如何将Snowflake表中多条学生入学记录合并为连续时间段记录？

针对你的需求，我们可以通过窗口函数+分组聚合的方式实现连续时段的合并，以下是具体的SQL方案：

步骤说明 #

1. 按studentid分组，将每个学生的时段按startdate升序排序，获取前一个时段的结束日期。
2. 判断当前时段是否与前一个时段连续/重叠：若当前startdate ≤ 前一个时段的enddate + 1天（包含重叠、相邻的情况），则归为同一组；否则开启新组。
3. 按学生ID和分组ID聚合，取每组的最早开始日期和最晚结束日期，得到合并后的结果。

完整SQL代码 #

WITH sorted_periods AS (
    SELECT 
        studentid,
        startdate,
        enddate,
        -- 获取同一学生上一个时段的结束日期，按startdate排序
        LAG(enddate) OVER (PARTITION BY studentid ORDER BY startdate) AS prev_enddate
    FROM your_table_name -- 替换成你的实际表名
),
grouped_periods AS (
    SELECT 
        studentid,
        startdate,
        enddate,
        -- 生成分组ID：当前时段与前一个不连续时，分组ID+1
        SUM(CASE WHEN startdate <= DATEADD(day, 1, prev_enddate) OR prev_enddate IS NULL THEN 0 ELSE 1 END) 
            OVER (PARTITION BY studentid ORDER BY startdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM sorted_periods
)
SELECT 
    studentid,
    MIN(startdate) AS merged_startdate,
    MAX(enddate) AS merged_enddate
FROM grouped_periods
GROUP BY studentid, group_id
ORDER BY studentid, merged_startdate;

针对你的测试数据的执行结果 #

运行上述SQL后，你的测试数据会合并为一行：

关键逻辑解释 #

• LAG(enddate)：获取同一学生的上一个时段结束日期，用于判断时段连续性。
• DATEADD(day, 1, prev_enddate)：处理相邻时段（比如前一个时段结束于2022-10-31，下一个开始于2022-11-01，属于连续）。如果仅需合并重叠时段，去掉DATEADD直接用startdate <= prev_enddate即可。
• 累计求和生成group_id：通过判断当前时段是否与之前的时段连续，将连续时段归为同一分组ID，最终按分组聚合得到合并后的区间。

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