如何在SQL中合并预约表相交重叠时间段，获取合并后的时段列表

实现逻辑 #

合并重叠连续时间段是经典的窗口函数应用场景，实现逻辑如下：

• 按诊所ID、预约开始时间对所有记录排序，用LAG窗口函数获取上一条预约的结束时间
• 标记当前预约是否为新时间段的起点：若当前预约开始时间大于上一条的结束时间，说明无重叠，标记为新分组
• 对分组标记做累加，相同累加值的记录属于同一个连续重叠的时间段组
• 按分组聚合，取每组最小的开始时间和最大的结束时间，即为合并后的连续时间段

可直接运行的SQL代码（兼容SQL Server 2012+） #

WITH MarkGroup AS (
    -- 内层查询标记新时间段起点
    SELECT 
        CLINIC_ID,
        A_START,
        A_END,
        CASE 
            WHEN A_START > LAG(A_END, 1, '1900-01-01') OVER(PARTITION BY CLINIC_ID ORDER BY A_START)
            THEN 1 
            ELSE 0 
        END AS is_new_group
    FROM APPOINTMENT
),
Grouped AS (
    -- 对新分组标记累加，得到连续时间段的分组ID
    SELECT 
        *,
        SUM(is_new_group) OVER(PARTITION BY CLINIC_ID ORDER BY A_START ROWS UNBOUNDED PRECEDING) AS group_id
    FROM MarkGroup
)
-- 按分组聚合得到合并后的时间段
SELECT 
    MIN(A_START) AS [Slat start time],
    MAX(A_END) AS [slat end time]
FROM Grouped
GROUP BY CLINIC_ID, group_id
ORDER BY [Slat start time]

原SQL问题说明 #

你编写的SQL存在两个核心问题：

• 逻辑条件不成立：A.A_START < B.A_START AND A.A_START > B.A_END 要求A的开始时间同时小于B的开始、大于B的结束，而正常预约的开始时间一定小于结束时间，该条件永远为假，无返回结果
• 笛卡尔关联的方式性能极差，数据量稍大就会出现性能问题，窗口函数的实现方式效率高很多

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