MySQL查询：如何合并员工的重叠与连续可用时间槽

问题 #

需要查询特定日期的员工可用时间，同一员工在同一天的多条重叠或连续的时间记录需要合并。

数据表结构 #

表名： availabilitySlots

期望结果 #

合并同一员工同一天内重叠/连续的时间区间，最终返回：

注：staffId=1和4的多条重叠/连续记录需合并为单个区间，staffId=3的两条记录不连续，保留分开的区间

MySQL查询方案 #

使用窗口函数LAG()识别时间区间的连续性，再通过分组合并重叠/连续区间：

WITH ranked_slots AS (
    SELECT 
        staffId,
        startTime,
        endTime,
        dayOfWeek,
        date,
        -- 标记当前区间是否与上一个区间连续或重叠
        CASE 
            WHEN LAG(endTime) OVER (PARTITION BY staffId, date ORDER BY startTime) >= startTime 
            THEN 0 
            ELSE 1 
        END AS is_new_group
    FROM availabilitySlots
    WHERE date = '2023-10-01' -- 指定查询日期
),
grouped_slots AS (
    SELECT 
        staffId,
        startTime,
        endTime,
        dayOfWeek,
        date,
        -- 累计求和生成分组ID
        SUM(is_new_group) OVER (PARTITION BY staffId, date ORDER BY startTime) AS group_id
    FROM ranked_slots
)
SELECT 
    staffId,
    MIN(startTime) AS startTime,
    MAX(endTime) AS endTime,
    MAX(dayOfWeek) AS dayOfWeek,
    MAX(date) AS date
FROM grouped_slots
GROUP BY staffId, date, group_id
ORDER BY staffId, startTime;

逻辑说明 #

1. ranked_slots CTE：按员工和日期分组，按开始时间排序，用LAG()获取上一个区间的结束时间，判断当前区间是否需要开启新分组（如果上一个区间的结束时间 >= 当前区间的开始时间，说明重叠或连续，属于同一组）。
2. grouped_slots CTE：对is_new_group累计求和，生成唯一的分组ID，同一组的区间会得到相同的group_id。
3. 最终查询：按员工、日期、分组ID聚合，取每组的最小开始时间和最大结束时间，得到合并后的区间。

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