多医生/患者场景下查询各时段各地点可用预约槽数的SQL需求

需求背景 #

需要统计特定时段内（示例为10:00 AM前）所有地点、所有日期的可用预约槽数，同时支持对比已预约数与可用数。核心规则：

• 医生排班为全年固定模式，按星期几设置（无具体日期），同一时段有N名医生在岗则可同时接待N名患者
• 预约默认按15分钟为一个槽位，需基于医生排班时段拆分出所有可用槽，再扣减已被患者预约占用的槽位，得到最终可用数

样本表结构 #

1. 医生时间表（doctor_schedule） #

2. 患者预约表（patient_appointments） #

SQL实现方案 #

以下以PostgreSQL为例，其他数据库可根据语法差异调整：

-- 步骤1：生成要查询的日期范围（示例为2021-10-01至2021-10-07）
WITH date_range AS (
    SELECT DATE '2021-10-01' + INTERVAL (n-1) DAY AS appt_date
    FROM generate_series(1,7) n -- 生成7天数据，可按需调整日期数量
),
-- 步骤2：生成医生排班对应的所有15分钟预约槽，统计每个时段的总可用槽数
doctor_slots AS (
    SELECT
        ds.Location,
        dr.appt_date,
        -- 生成每个15分钟槽的开始时间
        (dr.appt_date::TIMESTAMP + INTERVAL '15 minutes' * s.slot_num) AS slot_start,
        COUNT(ds.RESOURCE) AS total_available_per_slot
    FROM date_range dr
    JOIN doctor_schedule ds 
        ON TO_CHAR(dr.appt_date, 'FMDay') = ds.Day -- 匹配日期对应的星期几（注意语言/大小写一致）
    -- 生成覆盖排班时段的15分钟槽序号
    CROSS JOIN generate_series(
        0,
        EXTRACT(EPOCH FROM (ds.EndTime::TIME - ds.StartTime::TIME))::INT / 15 - 1
    ) s(slot_num)
    WHERE 
        -- 筛选特定时段：仅统计10:00 AM前的槽位
        (ds.StartTime::TIME + INTERVAL '15 minutes' * s.slot_num) < TIME '10:00:00'
    GROUP BY ds.Location, dr.appt_date, slot_start
),
-- 步骤3：统计已被患者预约占用的槽数
occupied_slots AS (
    SELECT
        pa.Location,
        pa.ApptDt AS appt_date,
        -- 把长预约拆分成15分钟的槽（比如30分钟预约占2个槽）
        (pa.ApptDt::TIMESTAMP + INTERVAL '15 minutes' * s.slot_offset) AS slot_start,
        COUNT(*) AS occupied_count
    FROM patient_appointments pa
    -- 根据预约时长拆分槽位
    CROSS JOIN generate_series(
        0,
        (pa.Duration / 15) - 1
    ) s(slot_offset)
    WHERE 
        -- 同样筛选特定时段
        (pa.StartTime::TIME + INTERVAL '15 minutes' * s.slot_offset) < TIME '10:00:00'
    GROUP BY pa.Location, pa.ApptDt, slot_start
)
-- 步骤4：汇总计算各地点每日的总可用、已占用、剩余可用槽数
SELECT
    COALESCE(ds.Location, os.Location) AS Location,
    COALESCE(ds.appt_date, os.appt_date) AS Date,
    COALESCE(SUM(ds.total_available_per_slot), 0) AS total_available,
    COALESCE(SUM(os.occupied_count), 0) AS total_occupied,
    COALESCE(SUM(ds.total_available_per_slot), 0) - COALESCE(SUM(os.occupied_count), 0) AS available_slots
FROM doctor_slots ds
FULL OUTER JOIN occupied_slots os 
    ON ds.Location = os.Location 
    AND ds.appt_date = os.appt_date 
    AND ds.slot_start = os.slot_start
GROUP BY COALESCE(ds.Location, os.Location), COALESCE(ds.appt_date, os.appt_date)
ORDER BY Location, Date;

适配与注意事项 #

1. 星期几匹配：TO_CHAR(dr.appt_date, 'FMDay')的输出格式需和doctor_schedule.Day的存储值一致，比如存储中文星期则需调整语言参数（如Oracle用TO_CHAR(..., 'FMDay', 'NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE')）。
2. 日期序列生成：不同数据库语法不同：
   • MySQL：用递归CTE生成日期：WITH RECURSIVE date_range AS (SELECT '2021-10-01' AS appt_date UNION ALL SELECT DATE_ADD(appt_date, INTERVAL 1 DAY) FROM date_range WHERE appt_date < '2021-10-07')
   • SQL Server：用DATEADD(DAY, n-1, '2021-10-01')结合数字表或master..spt_values
3. 时段调整：修改WHERE子句中的时间条件即可切换查询时段，比如要查全天则移除该条件。
4. 非15分钟预约兼容：如果存在非15分钟倍数的预约时长，可根据业务需求调整拆分逻辑（比如向上取整到15分钟）。

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