识别重叠预约：每个重叠段保留一个非超订实例

场景 #

预约具有开始和结束时间。允许预约重叠，但出于报告需求，我们需要区分重叠预约（称为overbooking）和非重叠时段。两个或多个预约可能相互重叠，但每次超订中总有一个预约的对应时段不被标记为overbooked。

规则 #

• 预约的重叠部分（segments）视为OVERBOOKED，但其中一个重叠实例被标记为NOT OVERBOOKED。
• 选择哪个重叠实例作为非超订不做限制，可以是该组重叠中的任意一个。

示例数据 #

期望结果 #

结果一 #

结果二（其他合规结果） #

已尝试的SQL代码 #

DROP TABLE IF EXISTS #appts
CREATE TABLE #appts 
(
    appt_id INT 
    ,st TIME
    ,et TIME
)

INSERT INTO #appts (appt_id,st,et) VALUES (1,'2:00','5:00')
INSERT INTO #appts (appt_id,st,et) VALUES (2,'3:00','6:00')
INSERT INTO #appts (appt_id,st,et) VALUES (3,'2:00','6:00')
INSERT INTO #appts (appt_id,st,et) VALUES (4,'6:00','7:00')

;With a1 AS
(
    SELECT * FROM #appts
)
,a2 AS 
(
    SELECT * FROM a1
)

SELECT DISTINCT
    a1.appt_id
    ,a2.appt_id
    ,ost = CASE WHEN a2.st > a1.st THEN a2.st ELSE a2.st END
    ,oet = CASE WHEN a2.et < a1.et THEN a2.et ELSE a1.et END
FROM 
    a1
    INNER JOIN a2 
        ON (a1.st > a2.st
        AND a1.st < a2.et
        OR a1.et > a2.st
        AND a1.et < a2.et)
WHERE
    a1.appt_id < a2.appt_id

问题 #

上述代码仅能输出可能的超订时段信息，无法生成包含NOT OVERBOOKED时段的合规结果集，需实现每个重叠段保留一个非超订实例的逻辑。

解决方案 #

以下SQL代码可实现需求，核心思路是先拆分所有有效时段区间，统计每个区间的重叠预约数，再为每个区间分配一个NOT OVERBOOKED标记：

DROP TABLE IF EXISTS #appts
CREATE TABLE #appts 
(
    appt_id INT 
    ,st TIME
    ,et TIME
)

INSERT INTO #appts (appt_id,st,et) VALUES (1,'2:00','5:00')
INSERT INTO #appts (appt_id,st,et) VALUES (2,'3:00','6:00')
INSERT INTO #appts (appt_id,st,et) VALUES (3,'2:00','6:00')
INSERT INTO #appts (appt_id,st,et) VALUES (4,'6:00','7:00')

;WITH AllTimePoints AS (
    -- 提取所有预约的起止时间作为时段分割点
    SELECT st AS time_point FROM #appts
    UNION
    SELECT et AS time_point FROM #appts
),
TimeSegments AS (
    -- 生成有预约覆盖的连续时段区间
    SELECT 
        tp1.time_point AS seg_start,
        tp2.time_point AS seg_end
    FROM AllTimePoints tp1
    JOIN AllTimePoints tp2 ON tp1.time_point < tp2.time_point
    WHERE EXISTS (
        SELECT 1 FROM #appts a
        WHERE a.st < tp2.time_point AND a.et > tp1.time_point
    )
),
ApptSegments AS (
    -- 关联预约与对应时段，统计重叠数并排序
    SELECT 
        a.appt_id,
        ts.seg_start,
        ts.seg_end,
        COUNT(*) OVER (PARTITION BY ts.seg_start, ts.seg_end) AS overlap_count,
        ROW_NUMBER() OVER (PARTITION BY ts.seg_start, ts.seg_end ORDER BY a.appt_id) AS rn
    FROM #appts a
    JOIN TimeSegments ts 
        ON a.st < ts.seg_end AND a.et > ts.seg_start
)
SELECT 
    appt_id,
    CASE 
        WHEN overlap_count = 1 THEN 'NOT OVERBOOKED'
        WHEN rn = 1 THEN 'NOT OVERBOOKED' -- 可调整ORDER BY规则选择不同预约作为非超订实例
        ELSE 'OVERBOOKED'
    END AS Classification,
    seg_start AS Segment_Start,
    seg_end AS Segment_End
FROM ApptSegments
ORDER BY appt_id, seg_start;

逻辑说明 #

1. AllTimePoints：提取所有预约的起止时间，作为分割时段的关键节点。
2. TimeSegments：利用分割点生成所有有预约覆盖的连续时段，避免无效空时段。
3. ApptSegments：将每个预约拆分为对应的时段片段，统计每个时段的重叠预约数，并给时段内的预约排序。
4. 最终查询：无重叠的时段直接标记为NOT OVERBOOKED；有重叠的时段，选择排序第一的预约标记为NOT OVERBOOKED，其余标记为OVERBOOKED。

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