如何通过Oracle SQL计算酒店系统表的月度空闲天数？

需求说明 #

现有一张类酒店业务表，包含Name（姓名）、Check_In（入住日期）、Check_Out（退房日期）字段，需通过Oracle SQL统计每个月的空闲天数。

输入表数据 #

预期输出结果 #

实现方案 #

核心逻辑：先计算目标月份的总天数，再减去该月内所有订单的占用天数总和，得到当月空闲天数。以下SQL兼容跨月入住的场景（示例中未涉及，但代码已覆盖）：

WITH month_list AS (
    -- 生成所有存在入住/退房记录的月份，获取每月首尾日期
    SELECT DISTINCT TRUNC(Check_In, 'MM') AS month_start, LAST_DAY(Check_In) AS month_end
    FROM hotel_bookings
    UNION
    SELECT DISTINCT TRUNC(Check_Out, 'MM') AS month_start, LAST_DAY(Check_Out) AS month_end
    FROM hotel_bookings
),
occupied_days AS (
    -- 计算每个月的总占用天数
    SELECT
        ml.month_start,
        SUM(
            GREATEST(0, LEAST(ml.month_end, hb.Check_Out) - GREATEST(ml.month_start, hb.Check_In))
        ) AS total_occupied
    FROM month_list ml
    LEFT JOIN hotel_bookings hb
        ON hb.Check_In <= ml.month_end AND hb.Check_Out >= ml.month_start
    GROUP BY ml.month_start, ml.month_end
)
-- 计算空闲天数：当月总天数 - 占用天数
SELECT
    TO_CHAR(month_start, 'MM/YYYY') AS "月份",
    (month_end - month_start + 1) - NVL(total_occupied, 0) AS "空闲天数"
FROM occupied_days
ORDER BY month_start;

代码解析 #

1. month_list 公共表表达式：提取所有有业务记录的月份，确保不会遗漏任何需要统计的月份。
2. occupied_days 公共表表达式：通过GREATEST和LEAST函数精准截取订单在当前月内的占用时段，避免跨月数据统计错误，最后汇总当月总占用天数。
3. 最终查询：用当月总天数（首尾日期差+1）减去占用天数，得到空闲天数，并将月份格式化为MM/YYYY样式。

注意事项 #

如果表中日期字段是字符串类型，需先通过TO_DATE函数转换为日期格式，例如：TO_DATE(Check_In, 'DD/MM/YYYY')。

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