如何在Snowflake中基于连续日期合并同Name的SQL记录？

这是典型的**间隙与孤岛（Gaps and Islands）**问题，需要将同一ID下、名称（Name）相同且日期连续的记录合并。以下是适用于Snowflake的SQL实现：

完整SQL代码 #

WITH ranked_records AS (
    SELECT
        ID,
        Name,
        Start,
        End,
        -- 标记新分组的起点：首条记录/名称变更/日期不连续
        CASE
            WHEN LAG(Name) OVER (PARTITION BY ID ORDER BY Start) != Name
                 OR DATEADD(day, 1, LAG(End) OVER (PARTITION BY ID ORDER BY Start)) != Start
            THEN 1
            ELSE 0
        END AS is_new_group
    FROM your_table_name -- 替换为你的实际表名
),
grouped_records AS (
    SELECT
        *,
        -- 累计生成分组ID，连续的同名+日期连续记录会归为同一组
        SUM(is_new_group) OVER (PARTITION BY ID ORDER BY Start) AS group_id
    FROM ranked_records
)
SELECT
    ID,
    Name,
    MIN(Start) AS Start,
    MAX(End) AS End
FROM grouped_records
GROUP BY ID, Name, group_id
ORDER BY Start;

逻辑说明 #

1. 标记分组起点：

   • 使用LAG()窗口函数，获取同一ID下按Start排序的上一条记录的Name和End日期。
   • 如果当前记录的Name与上一条不同，或者当前Start日期不是上一条End日期的次日，则标记为新分组的起点（is_new_group=1）。

2. 生成分组ID：

   • 对is_new_group列做累计求和（SUM() OVER()），这样连续的符合条件的记录会被分配同一个group_id。

3. 聚合合并：

   • 按ID、Name、group_id分组，取每组的最小Start和最大End，得到合并后的结果。

适配你的数据 #

将代码中的your_table_name替换为你实际的数据表名称即可运行，该逻辑会自动处理名称重复出现但不连续的场景（如示例中2021年的Value1会单独成组）。

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