SQL如何按客户分组计算每笔交易距上次交易的间隔天数

ROW_NUMBER()窗口函数仅能按分组排序规则为每行生成连续序号，无法直接获取同分组内上一行的日期字段值，因此无法完成日期间隔计算。

使用LAG()窗口函数即可满足需求，该函数的作用是获取分组内排序后、当前行之前第N行的指定字段值，正好匹配“取同一客户上一笔交易日期”的诉求，核心逻辑如下：

• 按Client字段拆分客户分组，每个客户的交易记录独立计算
• 分组内按交易日期Date升序排列，保证时间先后顺序正确
• 用LAG()取当前行的上一行日期，即该客户的上一笔交易日期
• 计算当前交易日期与上一笔交易日期的差值，首笔交易因不存在上一行记录，会默认返回NULL，完全匹配需求规则

参考SQL代码 #

以MySQL为例的写法：

SELECT
    Date,
    Client,
    DATEDIFF(Date, LAG(Date) OVER (PARTITION BY Client ORDER BY Date ASC)) AS `Number of days`
FROM transaction_table
-- 如需匹配示例结果的展示顺序，新增下方排序规则即可
ORDER BY Client, Date DESC;

如果使用PostgreSQL等支持日期直接做差的引擎，可简化日期计算部分：

SELECT
    Date,
    Client,
    Date - LAG(Date) OVER (PARTITION BY Client ORDER BY Date ASC) AS "Number of days"
FROM transaction_table
ORDER BY Client, Date DESC;

执行后返回的结果符合需求规则：同一客户首笔交易的间隔字段为NULL，后续交易的间隔为和上一笔交易相差的自然日天数。

内容的提问来源于stack exchange，提问作者Chris