如何使用SQL查询超出3场并发上限的重叠在线课程时段

现有存储计划内在线课程信息的数据表lessons，包含3个字段：

• id：主键，bigserial类型
• sch_at：带时区的时间戳类型，代表课程开始时间
• duration：interval类型，代表课程持续时长

当前服务器最多仅支持同时运行3场在线会议，需要查询出并发数超过服务器上限的冲突时段、以及对应的冲突课程id，方便提前调整排期。

场景示例 #

以下4场课程就会出现并发超限的问题：

• 15:00开始，时长45分钟
• 15:00开始，时长1小时
• 15:15开始，时长1小时15分钟
• 15:30开始，时长45分钟

上述课程在重叠时段同时运行的数量超过3，需要SQL能识别这类冲突。

参考建表及测试数据 #

-- 建表语句
create table lessons (
    id bigserial not null constraint str_pkey primary key, 
    duration interval, 
    sch_at timestamp with time zone
);

-- 测试数据插入
insert into lessons (id, sch_at, duration) values 
(1234, '2020-09-19 15:30:00.000000', '0 years 0 mons 0 days 1 hours 15 mins 0.00 secs'), 
(1235, '2020-09-19 15:45:00.000000', '0 years 0 mons 0 days 0 hours 45 mins 0.00 secs');

实现SQL #

WITH lesson_events AS (
    -- 拆分每个课程的开始、结束事件
    SELECT
        id AS lesson_id,
        sch_at AS event_time,
        1 AS event_type -- 开始事件，并发数+1
    FROM lessons
    UNION ALL
    SELECT
        id AS lesson_id,
        sch_at + duration AS event_time,
        -1 AS event_type -- 结束事件，并发数-1
    FROM lessons
),
concurrent_calculation AS (
    -- 按时间顺序计算累计并发数，同时记录当前时段的起止时间
    SELECT
        event_time AS period_start,
        LEAD(event_time) OVER (ORDER BY event_time) AS period_end,
        SUM(event_type) OVER (ORDER BY event_time, event_type) AS concurrent_count
    FROM lesson_events
),
overload_periods AS (
    -- 筛选出并发数超过3的冲突时段
    SELECT
        period_start,
        period_end
    FROM concurrent_calculation
    WHERE concurrent_count > 3
        AND period_end IS NOT NULL -- 排除最后一个无结束时间的无效时段
)
-- 关联得到所有在冲突时段内的课程id
SELECT DISTINCT
    op.period_start AS conflict_start_time,
    op.period_end AS conflict_end_time,
    l.id AS conflict_lesson_id
FROM overload_periods op
JOIN lessons l
    ON l.sch_at < op.period_end
    AND l.sch_at + l.duration > op.period_start
ORDER BY op.period_start, l.id;

使用说明 #

• 仅需要查询冲突时段的话，直接取overload_periods的查询结果即可
• 仅需要查询所有存在冲突的课程id的话，去掉查询结果中的时间字段后保留DISTINCT即可
• 需要调整并发上限的话，修改concurrent_count > 3中的数值即可适配不同的服务器限制

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