请求实现按Name合并重叠时间区间的数据行

问题描述 #

按Name分组，合并每组内所有重叠的时间区间：合并后行的Start Time取区间最小值，End Time取区间最大值；无重叠区间保留原行。

原始数据表 #

期望结果表 #

解决方案（标准SQL） #

使用窗口函数标记分组内的重叠区间，再聚合合并：

WITH ranked_intervals AS (
    SELECT 
        Name,
        Start_Time,
        End_Time,
        -- 标记当前区间是否与前一个区间重叠，生成分组ID
        SUM(CASE WHEN Start_Time <= LAG(End_Time) OVER (PARTITION BY Name ORDER BY Start_Time) THEN 0 ELSE 1 END) 
        OVER (PARTITION BY Name ORDER BY Start_Time) AS group_id
    FROM your_table
),
merged_intervals AS (
    SELECT 
        Name,
        MIN(Start_Time) AS Start_Time,
        MAX(End_Time) AS End_Time
    FROM ranked_intervals
    GROUP BY Name, group_id
)
SELECT * FROM merged_intervals ORDER BY Name, Start_Time;

步骤说明 #

• 排序并标记分组ID：按Name分组，每组内按Start Time排序。用LAG函数获取前一个区间的End Time，如果当前区间的Start Time小于等于前一个的End Time，说明重叠，分组ID不变；否则生成新的分组ID。
• 聚合合并区间：按Name和group_id分组，取每组的最小Start Time和最大End Time，得到合并后的区间。
• 排序输出：按Name和Start Time排序，输出结果与期望一致。

结果验证 #

执行上述SQL后将得到符合期望的输出：

• Name A的三个1月区间重叠，合并为一个；3月区间独立保留。
• Name C的两个1月区间重叠，合并为一个；4月区间独立保留。
• Name D的两个区间重叠，合并为一个。
• Name B仅一个区间，直接保留。

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