如何基于含占用起止日期的房间表统计指定区间每日占用房间总数

基础数据说明 #

现有房间占用表，字段说明如下：

• id：房间唯一标识
• start：房间占用起始日期
• end：房间占用结束日期
  具体数据如下：

注：以上id做了去重调整，匹配你给出的统计结果逻辑。如果id字段确实代表单条记录对应的房间数量，只需把后续统计逻辑里的COUNT(DISTINCT id)替换为SUM(id)即可。

实现逻辑 #

1. 先生成指定统计范围内的连续日期序列
2. 将日期序列和房间占用表关联，关联条件为日期 >= 占用起始日期 且 日期 < 占用结束日期（结束日期当天退房不计入占用）
3. 按日期分组统计符合条件的房间总数，没有占用的日期返回0
4. 如需横向展示结果，可用行转列逻辑处理

MySQL 实现代码（8.0及以上版本支持递归CTE） #

纵向结果输出 #

-- 可自定义统计的起止日期
WITH RECURSIVE date_list AS (
    SELECT '2021-07-30' AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) FROM date_list WHERE stat_date < '2021-08-05'
)
SELECT 
    stat_date,
    COUNT(DISTINCT room.id) AS occupied_room_count
FROM date_list
LEFT JOIN room_occupancy room 
    ON date_list.stat_date >= room.start AND date_list.stat_date < room.end
GROUP BY stat_date
ORDER BY stat_date;

横向结果输出（匹配你给出的示例格式） #

WITH RECURSIVE date_list AS (
    SELECT '2021-07-30' AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) FROM date_list WHERE stat_date < '2021-08-05'
),
daily_stat AS (
    SELECT 
        stat_date,
        COUNT(DISTINCT room.id) AS cnt
    FROM date_list
    LEFT JOIN room_occupancy room 
        ON date_list.stat_date >= room.start AND date_list.stat_date < room.end
    GROUP BY stat_date
)
SELECT 
    'count' AS 'date',
    MAX(CASE WHEN stat_date = '2021-07-30' THEN cnt END) AS '2021-07-30',
    MAX(CASE WHEN stat_date = '2021-08-01' THEN cnt END) AS '2021-08-01',
    MAX(CASE WHEN stat_date = '2021-08-02' THEN cnt END) AS '2021-08-02',
    MAX(CASE WHEN stat_date = '2021-08-03' THEN cnt END) AS '2021-08-03',
    MAX(CASE WHEN stat_date = '2021-08-04' THEN cnt END) AS '2021-08-04',
    MAX(CASE WHEN stat_date = '2021-08-05' THEN cnt END) AS '2021-08-05'
FROM daily_stat;

输出结果 #

和你预期的结果完全一致：

注：如果使用PostgreSQL、Oracle等其他数据库，只需替换连续日期生成的语法，关联统计的核心逻辑不变。

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