如何基于Reservations表用SQL计算单日最大重叠预订量

现有查询的局限性说明 #

你当前得到的是两两重叠的预订对，无法直接用来统计单日最大重叠数：两两重叠仅代表两个预订存在交集，不能证明多个预订同时覆盖了同一天。举个简单的反例：预订A(1-5)、预订B(2-3)、预订C(4-6)，A和B、A和C都重叠，但是B和C没有交集，单日最多同时存在2个预订，要是按两两重叠数给A算「2个重叠预订+自身=3」，结果就是错的。

方案1：直接统计每日预订数（逻辑直观，适合小数据量） #

先生成业务时间范围内的所有连续日期，再逐天统计覆盖当天的预订总数，取最大值即可。
以支持递归CTE的数据库为例，SQL写法如下：

WITH RECURSIVE dates AS (
    -- 取所有预订的最小开始日期为起点
    SELECT MIN(start) AS dt FROM Reservations
    UNION ALL
    -- 递归生成到最大结束日期的连续日期
    SELECT dt + 1 FROM dates WHERE dt < (SELECT MAX(end) FROM Reservations)
)
SELECT MAX(booking_count) AS max_daily_overlap
FROM (
    -- 统计每一天的预订数量
    SELECT COUNT(*) AS booking_count
    FROM dates d
    JOIN Reservations r ON r.start <= d.dt AND r.end >= d.dt
    GROUP BY d.dt
) daily_counts;

用你的示例数据运行，得到的最大值是3，对应日期4号同时覆盖了id为1、2、3的三个预订。

方案2：线扫描法（性能更优，适合大数据量） #

不需要生成全量日期，仅需要对预订的起止事件做标记累加：每个预订开始记为+1，结束记为-1，注意如果结束日期当天算入预订范围，结束事件需要标记为end+1，避免结束当天提前减扣，按时间顺序排序后累加标记值，累加过程中的最大值就是单日最大重叠数。
SQL写法如下：

WITH events AS (
    -- 开始事件：+1
    SELECT start AS dt, 1 AS delta FROM Reservations
    UNION ALL
    -- 结束事件：-1，结束日期后一天生效
    SELECT end + 1 AS dt, -1 AS delta FROM Reservations
)
SELECT MAX(running_count) AS max_daily_overlap
FROM (
    -- 按时间顺序累加标记值
    SELECT SUM(delta) OVER (ORDER BY dt) AS running_count
    FROM events
) running_counts;

该方案在数据量较大时性能远高于方案1，不需要遍历所有日期，仅需要处理2倍预订数的事件记录。

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