如何按id、id2分组并正确展示连续时间段的最小/最大日期

我需要对数据表按id、id2分组，合并连续时间段的记录，展示每组的最小start_date和最大end_date。但执行常规分组SQL后结果不符合预期：

尝试的SQL语句 #

SELECT
    id,
    id2,
    min(start_date),
    MAX(end_date)
FROM
    table
GROUP BY
    id,
    id2

这段SQL会把同一id+id2的所有记录合并成一条，但实际需求是将不连续的时间段拆分为独立分组。

样本表 #

当前执行结果 #

期望结果 #

解决方案 #

这是典型的连续区间合并问题，可通过窗口函数生成分组标识来实现：

WITH ranked_data AS (
    SELECT
        id,
        id2,
        start_date,
        end_date,
        -- 生成连续区间的分组ID：当前行与上一行不连续时，分组ID加1
        SUM(
            CASE 
                WHEN DATE_ADD(LAG(end_date) OVER (PARTITION BY id, id2 ORDER BY start_date), INTERVAL 1 DAY) = start_date 
                THEN 0 
                ELSE 1 
            END
        ) OVER (PARTITION BY id, id2 ORDER BY start_date) AS group_id
    FROM
        table
)
SELECT
    id,
    id2,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM
    ranked_data
GROUP BY
    id,
    id2,
    group_id
ORDER BY
    start_date;

逻辑说明 #

1. 使用LAG窗口函数，获取同一id+id2分组内上一条记录的end_date
2. 判断当前记录的start_date是否是上一条end_date的次日：如果是，说明属于同一连续区间；否则，标记为新区间的开始
3. 通过累加标记值生成group_id，同一连续区间的记录会拥有相同的group_id
4. 最后按id、id2、group_id分组，即可得到每个连续区间的最小开始日期和最大结束日期

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