按同日同ID连续时间值合并多行 尝试LAG/LEAD实现未果求助

这是典型的*间隙与岛屿（Gaps and Islands）*问题，核心通过窗口函数标记连续时间片段，再按片段聚合即可实现，不需要复杂的递归逻辑。

核心实现逻辑 #

• 先按「同一日期、同一ID」作为分区维度，把所有记录按开始时间升序排序，保证时间线是顺向排列的。
• 用LAG()函数取同分区内上一行的结束时间，和当前行的开始时间做对比：
  • 如果当前行开始时间和上一行结束时间完全相等，说明两段时间没有间隙，属于同一个连续片段
  • 如果两者不相等，说明从当前行开始是一个新的独立片段
• 对每个新片段的标记值做累计求和，得到的数值就是每个连续片段的唯一分组ID——同一个连续片段内的所有行，分组ID完全一致；出现时间间隙的位置，分组ID会自动跳变。
• 最后按日期、ID、分组ID做聚合，取分组内最早的开始时间作为合并后的起始时间，最晚的结束时间作为合并后的终止时间即可。

可直接复用的SQL实现 #

WITH base_source AS (
    -- 替换为实际业务的源表查询，示例数据仅做演示
    SELECT * FROM (
        VALUES 
        ('2017-12-01'::DATE, '09:00:00'::TIME, '11:00:00'::TIME, 12345),
        ('2017-12-01'::DATE, '11:00:00'::TIME, '13:00:00'::TIME, 12345),
        ('2018-09-08'::DATE, '09:00:00'::TIME, '10:00:00'::TIME, 78465),
        ('2018-09-08'::DATE, '10:00:00'::TIME, '12:00:00'::TIME, 78465),
        ('2018-09-08'::DATE, '15:00:00'::TIME, '16:00:00'::TIME, 78465),
        ('2018-09-08'::DATE, '16:00:00'::TIME, '18:00:00'::TIME, 78465)
    ) AS real_table(Date, StartTime, EndTime, ID)
),
adjacent_flag AS (
    SELECT
        Date,
        StartTime,
        EndTime,
        ID,
        -- 标记是否为新片段的起点：无间隙则打0，有间隙则打1
        CASE
            WHEN StartTime = LAG(EndTime) OVER (PARTITION BY Date, ID ORDER BY StartTime) THEN 0
            ELSE 1
        END AS fragment_start_flag
    FROM base_source
),
fragment_group AS (
    SELECT
        Date,
        StartTime,
        EndTime,
        ID,
        -- 累计标记值生成统一的片段分组ID
        SUM(fragment_start_flag) OVER (
            PARTITION BY Date, ID 
            ORDER BY StartTime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS fragment_id
    FROM adjacent_flag
)
SELECT
    Date,
    MIN(StartTime) AS StartTime,
    MAX(EndTime) AS EndTime,
    ID
FROM fragment_group
GROUP BY Date, ID, fragment_id
ORDER BY Date, ID, StartTime;

常见问题说明 #

• 之前使用LAG/LEAD未得到正确结果，通常是两个原因：一是生成分组值后没有把它作为真正的聚合维度，导致跨片段的时间被错误合并；二是窗口函数的分区范围漏了日期或ID，造成跨日期、跨ID的时间线被误衔接。
• 如果业务规则调整，需要允许一定范围内的短间隙也算连续，只需修改adjacent_flag步骤中的判断条件即可，比如允许15分钟内的间隙可合并：WHEN StartTime <= LAG(EndTime) OVER (PARTITION BY Date, ID ORDER BY StartTime) + INTERVAL '15 minute' THEN 0。
• 该逻辑同时兼容时间重叠的异常数据，只要时间存在交叉衔接，就会被划分到同一个片段聚合，无需额外写异常判断。

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