MySQL计算指定日期范围内各酒店各房型每日有效售出房量

样本预订数据 #

-----------------------------------------------------------------------------------
hotel_id | roomtype_id | status   |  booking_timestamp  | checkin_date | checkout_date
-----------------------------------------------------------------------------------
1        | 1           | new      | 2023-02-02 00:15:30 | 2023-02-03   | 2023-02-05
1        | 1           | new      | 2023-02-04 09:10:15 | 2023-02-04   | 2023-02-04
1        | 1           | new      | 2023-02-07 03:00:10 | 2023-02-09   | 2023-02-10
1        | 1           | new      | 2023-02-08 04:10:10 | 2023-02-09   | 2023-02-10
1        | 1           | cancelled| 2023-02-08 10:15:30 | 2023-02-09   | 2023-02-10
2        | 2           | new      | 2023-02-03 06:40:14 | 2023-02-06   | 2023-02-07
2        | 2           | new      | 2023-02-05 08:10:14 | 2023-02-06   | 2023-02-08

需求说明 #

计算**指定日期范围（2023-02-01至2023-02-12）**内，每个酒店、每个房型的每日有效售出房量，需排除已取消的订单。每行数据对应一间客房的预订记录。

高效解决方案（SQL实现） #

这类时间维度的统计需求，用日历表关联+聚合的方式是最优解，以下是具体实现步骤：

1. 生成目标日期序列 #

先构建覆盖指定范围的日历表，确保每个日期都能被统计到：

WITH date_range AS (
    SELECT DATE '2023-02-01' + INTERVAL '1 day' * (n-1) AS date
    FROM generate_series(1, 12) AS n -- 12天对应2023-02-01至2023-02-12
),

2. 过滤有效订单 #

筛选出状态为new的有效订单，直接排除cancelled记录：

valid_bookings AS (
    SELECT hotel_id, roomtype_id, checkin_date, checkout_date
    FROM bookings
    WHERE status = 'new'
),

3. 关联日历表统计每日在住量 #

将日历表与有效订单关联，判断日期是否落在入住区间内（退房当天不计入在住），再按维度聚合统计：

daily_occupancy AS (
    SELECT
        dr.date,
        vb.hotel_id,
        vb.roomtype_id,
        COUNT(vb.hotel_id) AS room_sold
    FROM date_range dr
    LEFT JOIN valid_bookings vb
        ON dr.date >= vb.checkin_date
        AND dr.date < vb.checkout_date
    GROUP BY dr.date, vb.hotel_id, vb.roomtype_id
)

4. 补全缺失的维度组合 #

如果某些日期没有对应酒店房型的订单，补全记录并将room_sold设为0：

SELECT
    COALESCE(do.hotel_id, h.hotel_id) AS hotel_id,
    COALESCE(do.roomtype_id, rt.roomtype_id) AS roomtype_id,
    dr.date,
    COALESCE(do.room_sold, 0) AS room_sold
FROM date_range dr
CROSS JOIN (SELECT DISTINCT hotel_id FROM bookings) h
CROSS JOIN (SELECT DISTINCT roomtype_id FROM bookings) rt
LEFT JOIN daily_occupancy do
    ON dr.date = do.date
    AND h.hotel_id = do.hotel_id
    AND rt.roomtype_id = do.roomtype_id
ORDER BY hotel_id, roomtype_id, dr.date;

注意事项 #

• 不同数据库生成日期序列的语法有差异：MySQL用递归CTE，SQL Server用DATEADD+递归，PostgreSQL用generate_series
• 用dr.date < vb.checkout_date确保退房当天不计入在住量
• CROSS JOIN用来生成所有酒店-房型-日期的组合，避免遗漏无订单的日期记录

预期输出 #

--------------------------------------------------------------
hotel_id | roomtype_id | date       | room_sold
--------------------------------------------------------------
1        | 1           | 2023-02-01 | 0          No new check-in yet
1        | 1           | 2023-02-02 | 0          No new check-in yet
1        | 1           | 2023-02-03 | 1          one room got checked-in (1st row of data)
1        | 1           | 2023-02-04 | 2          another room got check-in (2nd row of data)
1        | 1           | 2023-02-05 | 0          all rooms of this room type got check-out
1        | 1           | 2023-02-06 | 0          No new check-in yet
1        | 1           | 2023-02-07 | 0          No new check-in yet
1        | 1           | 2023-02-09 | 1          2 checked-in but 1 cancelled (row 3 to 5)
1        | 1           | 2023-02-10 | 0          all rooms of this room type got check-out
2        | 2           | 2023-02-01 | 0          No new check-in yet
2        | 2           | 2023-02-02 | 0          No new check-in yet
2        | 2           | 2023-02-03 | 0          No new check-in yet
2        | 2           | 2023-02-04 | 0          No new check-in yet
2        | 2           | 2023-02-05 | 0          No new check-in yet
2        | 2           | 2023-02-06 | 2          2 rooms got checked-in (row 6 and 7)
2        | 2           | 2023-02-07 | 1          1 room checked-out (row 6)
2        | 2           | 2023-02-08 | 0          1 room checked-out (row 7)
2        | 2           | 2023-02-09 | 0          No new check-in yet
2        | 2           | 2023-02-10 | 0          No new check-in yet
2        | 2           | 2023-02-11 | 0          No new check-in yet
2        | 2           | 2023-02-12 | 0          No new check-in yet

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