如何合并重叠排班时间段？求正确的SQL查询语句

原始数据 #

原SQL的问题 #

你之前的查询存在以下问题：

• 分组仅按date，未包含userid，多用户场景下会导致数据混淆
• 错误引用了不存在的字段enddate（实际字段为endtime）
• 仅计算了时间段的分钟差，未输出合并后的起止时间，无法满足需求

正确SQL实现 #

以下是基于窗口函数的解决方案，适用于SQL Server、PostgreSQL等支持窗口函数的数据库：

WITH ranked_shifts AS (
    SELECT 
        userid,
        date,
        starttime,
        endtime,
        -- 标记当前记录是否为新的连续时间段起点
        CASE 
            WHEN starttime > LAG(endtime) OVER (PARTITION BY userid, date ORDER BY starttime)
            THEN 1
            ELSE 0
        END AS is_new_group,
        -- 生成连续时间段的组ID
        SUM(CASE 
                WHEN starttime > LAG(endtime) OVER (PARTITION BY userid, date ORDER BY starttime)
                THEN 1
                ELSE 0
            END) OVER (PARTITION BY userid, date ORDER BY starttime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM shifts
)
SELECT 
    userid,
    date,
    MIN(starttime) AS starttime,
    MAX(endtime) AS endtime
FROM ranked_shifts
GROUP BY userid, date, group_id
ORDER BY userid, date, starttime;

逻辑说明 #

1. CTE阶段：
   • 使用LAG(endtime)获取同一用户、同一天内上一条排班记录的结束时间
   • 通过is_new_group判断当前排班是否与上一个时间段断开（当前开始时间晚于上一个结束时间）
   • 累加is_new_group值生成group_id，同一连续/重叠时间段的记录会被分到同一个组
2. 分组聚合：按userid、date和group_id分组，取每组的最早开始时间和最晚结束时间，得到合并后的结果

预期输出 #

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