如何在SQL中逐行比较首行与后续行直至满足日期阈值条件

问题背景 #

我有一张表，结构如下：

需要实现的逻辑：按ID分组，每组从首行开始，依次和后续行比较日期差，直到某行与当前起始行的日期差超过30天；此时将该行设为新的起始行，继续和后续行重复上述判断。预期结果（阈值30天）如下：

我尝试了以下代码，但无法实现正确的逐行迭代判断：

select *, 
case when lagdiff > 30 then 1 else 0
end as lag_flg from
(SELECT *, datediff(Date,lag) AS lagdiff from
(select ID, Date, lag(Date) OVER (
                    PARTITION BY ID ORDER BY Date
                    ) AS lag
from table

解决方案：递归CTE实现动态起始点判断 #

普通的lag()窗口函数只能获取上一行数据，无法追踪当前组的动态起始行，因此需要用递归CTE来实现逐行迭代逻辑。以下是不同SQL方言的实现：

MySQL 8+版本 #

WITH RECURSIVE cte AS (
    -- 初始化：取每个ID的最早日期作为初始起始行，组号设为1
    SELECT 
        ID,
        `Date`,
        `Date` AS start_date,
        1 AS group_num
    FROM your_table
    WHERE `Date` = (SELECT MIN(`Date`) FROM your_table t WHERE t.ID = your_table.ID)
    
    UNION ALL
    
    -- 递归迭代：判断当前行与组起始行的日期差
    SELECT 
        t.ID,
        t.`Date`,
        -- 日期差超30天则更新起始行为当前行，否则沿用原起始行
        CASE WHEN DATEDIFF(t.`Date`, c.start_date) > 30 THEN t.`Date` ELSE c.start_date END,
        -- 日期差超30天则组号+1，否则沿用原组号
        CASE WHEN DATEDIFF(t.`Date`, c.start_date) > 30 THEN c.group_num + 1 ELSE c.group_num END
    FROM your_table t
    JOIN cte c ON t.ID = c.ID 
        AND t.`Date` > c.`Date`
    -- 避免同一行被重复处理
    WHERE NOT EXISTS (
        SELECT 1 FROM cte c2 WHERE c2.ID = t.ID AND c2.`Date` = t.`Date`
    )
)
-- 输出最终分组结果
SELECT ID, `Date`, group_num
FROM cte
ORDER BY ID, `Date`;

PostgreSQL版本 #

WITH RECURSIVE cte AS (
    SELECT 
        ID,
        "Date",
        "Date" AS start_date,
        1 AS group_num
    FROM your_table
    WHERE "Date" = (SELECT MIN("Date") FROM your_table t WHERE t.ID = your_table.ID)
    
    UNION ALL
    
    SELECT 
        t.ID,
        t."Date",
        CASE WHEN (t."Date" - c.start_date) > INTERVAL '30 days' THEN t."Date" ELSE c.start_date END,
        CASE WHEN (t."Date" - c.start_date) > INTERVAL '30 days' THEN c.group_num + 1 ELSE c.group_num END
    FROM your_table t
    JOIN cte c ON t.ID = c.ID 
        AND t."Date" > c."Date"
    WHERE NOT EXISTS (
        SELECT 1 FROM cte c2 WHERE c2.ID = t.ID AND c2."Date" = t."Date"
    )
)
SELECT ID, "Date", group_num
FROM cte
ORDER BY ID, "Date";

原代码问题说明 #

你用lag()窗口函数计算的是当前行与上一行的日期差，而不是与当前组的起始行的日期差。这种方式无法实现“从起始行累计判断日期差”的逻辑，比如中间连续多行与上一行的差都小于30天，但累计到某一行与起始行的差超过30天，lag()的方式会漏掉这个判断，因此无法得到正确的分组结果。

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