如何对有序日期区间进行重组分组？

核心思路 #

要解决这个问题，关键是生成连续关联行的分组ID——因为分析函数无法直接引用计算后的分组结果，所以改用「累积求和标记新组」的方式：

• 按my key分区、tech date排序，判断当前行与前一行是否存在start date或end date相等的情况
• 如果不存在相等（即需要开启新组），则标记为1，否则标记为0
• 对标记值做累积求和，得到唯一的组ID，连续关联的行会被分到同一个组

完整SQL实现 #

WITH grouped_data AS (
    SELECT 
        `my key`,
        `start date`,
        `end date`,
        -- 标记是否为新组：当前行与前一行的start/end都不相等时，标记为1
        CASE 
            WHEN LAG(`start date`) OVER (PARTITION BY `my key` ORDER BY `tech date`) = `start date`
                 OR LAG(`end date`) OVER (PARTITION BY `my key` ORDER BY `tech date`) = `end date`
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM mytable
),
group_ids AS (
    SELECT 
        `my key`,
        `start date`,
        `end date`,
        -- 累积求和生成组ID
        SUM(is_new_group) OVER (PARTITION BY `my key` ORDER BY `tech date`) AS group_id
    FROM grouped_data
)
-- 按组聚合，取最小start和最大end
SELECT 
    `my key`,
    MIN(`start date`) AS `start date`,
    MAX(`end date`) AS `end date`
FROM group_ids
GROUP BY `my key`, group_id
ORDER BY `my key`, `start date`;

验证示例数据 #

用你提供的示例数据测试：

1. 第一行没有前一行，is_new_group为1，group_id为1
2. 第二行的end date和第一行相等，is_new_group为0，group_id保持1
3. 第三行的start date和第二行相等，is_new_group为0，group_id保持1
4. 第四行的start date和end date都和第三行不相等，is_new_group为1，group_id为2

聚合后得到：

完全符合期望结果。

特殊情况处理（可选） #

如果需要优先排除9999-12-31（组内存在非该值的end date时，取最大的非该值），可以修改聚合逻辑：

COALESCE(
    MAX(CASE WHEN `end date` != '9999-12-31' THEN `end date` ELSE NULL END),
    MAX(`end date`)
) AS `end date`

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