PostgreSQL处理重叠日期范围 计算每周最小可用房间数

原有SQL统计错误的核心原因是日期展开逻辑不符合业务规则：业务上退房当日房间即可释放，因此单个预订实际占用的日期范围是入住日（含）至退房日前1天（含），原逻辑把退房日也计入了住客占用周期，导致退房日当天同时统计了离店住客和新入住住客的占用，计数偏高。
只需要调整generate_series的结束日期，将退房日减去1天即可，修正后的SQL如下：

WITH reservations_with_expanded_dates AS (
    SELECT
        reservation_id,
        generate_series(
            check_in_date,
            check_out_date - 1, -- 退房当日不计入原住客占用
            '1day'::interval
        )::DATE AS stay
    FROM reservations
)
SELECT
    EXTRACT(WEEK FROM stay)::INT AS week,
    MAX(number_of_rooms) AS min_required_rooms
FROM (
    SELECT
        stay,
        COUNT(*) AS number_of_rooms
    FROM reservations_with_expanded_dates
    GROUP BY stay
) AS reservations_per_day
GROUP BY week
ORDER BY week;

执行后返回结果：第1周3间、第2周2间，完全符合预期。

原按日展开的方案在预订量大、单预订入住周期长的场景下，会生成大量冗余的中间日期行，性能较差。可以使用事件增量累计法实现，不需要展开每日数据，性能提升非常明显：

• 把每个预订拆为两个事件：入住日房间占用+1，退房日房间占用-1
• 按日期聚合每日的占用增量
• 通过窗口函数按日期顺序累加增量，得到当日实际需要的房间数
• 最后按周取房间数的最大值即可
  该方案的中间数据量仅为预订记录数的2倍，不受入住时长影响，在大数据量场景下性能远高于日期展开方案。
  对应SQL如下：

WITH room_events AS (
    -- 拆分入住+1、退房-1两个事件
    SELECT check_in_date AS event_date, 1 AS delta FROM reservations
    UNION ALL
    SELECT check_out_date AS event_date, -1 AS delta FROM reservations
),
daily_room_usage AS (
    SELECT
        event_date,
        -- 按日期累加增量，得到当日实际占用房间数
        SUM(SUM(delta)) OVER (ORDER BY event_date) AS used_rooms
    FROM room_events
    GROUP BY event_date
)
SELECT
    EXTRACT(WEEK FROM event_date)::INT AS week,
    MAX(used_rooms) AS min_required_rooms
FROM daily_room_usage
GROUP BY week
ORDER BY week;

注意：如果需要处理跨周的日期边界问题，建议使用date_trunc('week', event_date)获取每周的起始日期作为分组维度，避免跨年时周数重复的问题。

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