如何用SQL单表+CTE统计每日客房预订数量（含结账日）

完全可以用公共表表达式（CTE）实现这个需求，这是非常贴合场景的解决方案。

假设你的表名为hotel_bookings，time_series、start、end均为日期类型，直接看实现代码：

WITH daily_dates AS (
    -- 提取time_series中所有唯一日期，作为每日统计的基准维度
    SELECT DISTINCT time_series AS stat_date
    FROM hotel_bookings
)
SELECT
    dd.stat_date,
    COUNT(hb.start) AS daily_bookings_count
FROM daily_dates dd
LEFT JOIN hotel_bookings hb
    ON dd.stat_date BETWEEN hb.start AND hb.end
GROUP BY dd.stat_date
ORDER BY dd.stat_date;

代码说明： #

• daily_dates这个CTE负责从原表中剥离出所有需要统计的日期（去重处理），确保每个日期只作为一条统计基准。
• 左连接原表时，通过BETWEEN关键字让统计日期匹配所有处于start到end区间内的预订记录（包含两端，满足结账日期计入统计的要求）。
• COUNT(hb.start)用来统计有效预订数，避免左连接带来的NULL值被误计入统计结果。

如果你的业务需要统计连续日期范围（而非仅time_series中存在的日期），也可以用递归CTE生成连续日期序列，比如PostgreSQL或MySQL 8.0+的写法：

-- 示例：生成2024-01-01到2024-01-31的连续日期
WITH RECURSIVE daily_dates AS (
    SELECT '2024-01-01'::DATE AS stat_date
    UNION ALL
    SELECT stat_date + INTERVAL '1 day'
    FROM daily_dates
    WHERE stat_date < '2024-01-31'::DATE
)
SELECT
    dd.stat_date,
    COUNT(hb.start) AS daily_bookings_count
FROM daily_dates dd
LEFT JOIN hotel_bookings hb
    ON dd.stat_date BETWEEN hb.start AND hb.end
GROUP BY dd.stat_date
ORDER BY dd.stat_date;

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