如何在SQL中按小时查询指定时段内空闲厅室的可用性

需求说明 #

需要编写SQL查询实现以下目标：

• 展示指定时段内空闲的厅室
• 若没有单个费率时段能完全覆盖用户请求的整个时间范围，需将衔接的适配时段合并为一个完整时段

现有表结构 #

主厅表（Halls） #

内部厅室表（Halls_Interior） #

费率时段表（Halls_Price） #

查询场景 #

当用户查询9:00-11:00空闲的厅室时，期望将Halls_Price中ID3（9-10）和ID5（10-12）的时段合并，得到如下结果：

修正后的SQL查询 #

WITH filtered_rates AS (
    -- 筛选与目标时段[9,11]重叠的费率时段，截断结束时间至11
    SELECT 
        hp.IdHallsInterior,
        hp.FromTime,
        LEAST(hp.EndTime, 11) AS EndTime
    FROM Halls_Price hp
    WHERE 
        hp.FromTime < 11 
        AND hp.EndTime > 9
),
grouped_intervals AS (
    -- 用窗口函数标记可合并的时段组
    SELECT 
        fri.IdHallsInterior,
        fri.FromTime,
        fri.EndTime,
        SUM(CASE WHEN LAG(fri.EndTime) OVER (PARTITION BY fri.IdHallsInterior ORDER BY fri.FromTime) = fri.FromTime THEN 0 ELSE 1 END) 
        OVER (PARTITION BY fri.IdHallsInterior ORDER BY fri.FromTime) AS interval_group
    FROM filtered_rates fri
),
merged_intervals AS (
    -- 合并同组时段，确保覆盖完整查询时段
    SELECT 
        gi.IdHallsInterior,
        MIN(gi.FromTime) AS FromTime,
        MAX(gi.EndTime) AS EndTime
    FROM grouped_intervals gi
    GROUP BY gi.IdHallsInterior, gi.interval_group
    HAVING MIN(gi.FromTime) <= 9 AND MAX(gi.EndTime) >= 11
)
-- 关联表获取完整厅室信息
SELECT 
    hi.IdHalls,
    mi.IdHallsInterior,
    mi.FromTime,
    mi.EndTime
FROM merged_intervals mi
JOIN Halls_Interior hi ON mi.IdHallsInterior = hi.id
JOIN Halls h ON hi.IdHalls = h.id;

逻辑说明 #

1. filtered_rates：先筛选出和用户查询时段重叠的费率时段，同时将结束时间截断到用户需求的11点，避免超出范围。
2. grouped_intervals：通过LAG窗口函数比较当前时段的开始时间与上一个时段的结束时间，为衔接的时段分配同一组ID，标记可合并的时段。
3. merged_intervals：对同组时段进行合并，取组内最早开始时间和最晚结束时间，同时过滤掉无法覆盖完整查询时段的结果。
4. 最后关联内部厅室表和主厅表，输出包含主厅ID的完整结果。

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