如何统计含耦合班级的时间表中单日超3个连续间隙？

问题核心 #

需要统计单班级（如"A"）单日是否存在3个以上连续空时段，同时要考虑该班级所属的耦合班级（如"A,B"）的占用时段——即耦合班级的时段也视为该单班级的占用时段，避免误判间隙。

解决方案思路 #

1. 先整合每个单班级的所有关联时段：包括自身的时段，以及所有包含该单班级的耦合班级的时段。
2. 基于整合后的时段，计算相邻时段的间隙，筛选出间隙超过3的记录。

修正表结构 #

首先修正classes_coupling表的字段类型缺失问题：

CREATE TABLE classes_coupling (
  id INT AUTO_INCREMENT KEY,
  class_id INT,
  class_coupling_id INT
);

完整查询语句 #

WITH class_all_timeslots AS (
    -- 单班级自身的所有占用时段
    SELECT 
        c.id AS target_class_id,
        t.day_id,
        t.timeslot_id
    FROM classes c
    JOIN timetable t ON c.id = t.class_id
    -- 合并包含该单班级的耦合班级的占用时段
    UNION ALL
    SELECT 
        cc.class_coupling_id AS target_class_id,
        t.day_id,
        t.timeslot_id
    FROM classes_coupling cc
    JOIN timetable t ON cc.class_id = t.class_id
),
ordered_timeslots AS (
    SELECT 
        target_class_id,
        day_id,
        timeslot_id,
        -- 获取当前时段的下一个时段
        LEAD(timeslot_id) OVER (PARTITION BY target_class_id, day_id ORDER BY timeslot_id) AS next_timeslot
    FROM class_all_timeslots
    -- 去重重复时段（同一时段可能被单班级和耦合班级同时占用）
    GROUP BY target_class_id, day_id, timeslot_id
)
-- 筛选出间隙超过3的记录，去重后返回
SELECT DISTINCT
    day_id,
    target_class_id AS class_id
FROM ordered_timeslots
WHERE 
    -- 间隙=下一个时段-当前时段，大于3表示中间有3个以上连续空时段
    next_timeslot - timeslot_id > 3
ORDER BY day_id, class_id;

逻辑说明 #

1. class_all_timeslots：将单班级自身的时段与耦合班级的时段合并，确保每个单班级的所有占用时段都被统计。
2. ordered_timeslots：对每个单班级每天的时段去重，并用LEAD窗口函数获取相邻的下一个时段，为计算间隙做准备。
3. 最终筛选：判断相邻时段的间隙是否超过3，去重后得到符合条件的日期和班级ID，与期望输出一致。

验证示例数据 #

对于班级1（A）：

• 第1天的合并时段为1,2,6，6-2=4>3，符合条件，被选中。
• 第3天的合并时段为1,2,3,4,7,8,9，所有相邻时段的间隙均≤3，不会被选中。

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