PostgreSQL 15.1：如何通过单查询获取可用时间区间

表结构 #

工作时间表（Working Hours） #

预约表（Bookings） #

需求 #

获取指定日期（2023-02-21）内未被预约的可用时间区间，预期输出如下：

单查询解决方案（PostgreSQL 15.1） #

以下是可实现需求的单条查询语句：

WITH target_date AS (
    SELECT '2023-02-21'::date AS date,
           EXTRACT(DOW FROM '2023-02-21'::date)::int AS day_of_week
),
work_hours AS (
    SELECT th.date,
           wh.start_time,
           wh.end_time
    FROM target_date th
    JOIN working_hours wh ON th.day_of_week = wh.day_of_week
),
time_points AS (
    SELECT date, start_time AS point_time, 'start' AS type
    FROM bookings
    WHERE date = (SELECT date FROM target_date)
    UNION ALL
    SELECT date, end_time AS point_time, 'end' AS type
    FROM bookings
    WHERE date = (SELECT date FROM target_date)
    UNION ALL
    SELECT date, start_time AS point_time, 'work_start' AS type
    FROM work_hours
    UNION ALL
    SELECT date, end_time AS point_time, 'work_end' AS type
    FROM work_hours
),
ordered_points AS (
    SELECT date,
           point_time,
           LAG(point_time) OVER (PARTITION BY date ORDER BY point_time) AS prev_point,
           LAG(type) OVER (PARTITION BY date ORDER BY point_time) AS prev_type
    FROM time_points
)
SELECT date,
       prev_point AS start_time,
       point_time AS end_time
FROM ordered_points
WHERE (prev_type IN ('work_start', 'end'))
  AND (type IN ('start', 'work_end'))
  AND prev_point < point_time
ORDER BY start_time;

语句逻辑说明 #

1. target_date：定义目标日期，并计算该日期对应的星期几（DOW返回0为周日，1为周一，与工作时间表的day_of_week字段对应）。
2. work_hours：关联目标日期和工作时间表，获取当日的工作起止时间。
3. time_points：收集所有关键时间点——工作开始/结束时间、所有预约的开始/结束时间。
4. ordered_points：对时间点按时间排序，用窗口函数LAG获取前一个时间点及其类型。
5. 最后筛选有效空闲区间：前一个点是工作开始或预约结束，后一个点是预约开始或工作结束，且前时间点早于后时间点，得到最终的空闲时段。

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