如何用SQL计算指定日期下特定产品的最大重叠预订量

核心思路 #

采用事件扫描法计算重叠数，逻辑如下：

• 将每条预订记录拆分为两个事件：预订开始时计数+1，预订结束时计数-1
• 按时间顺序遍历所有事件，累加计数的过程中出现的最大值就是最大重叠预订数
• 该方案天然兼容单天/跨天场景，不需要针对单天做特殊适配

实现代码（兼容MySQL 8.0+/PostgreSQL/通用支持窗口函数的数据库） #

WITH event_list AS (
    -- 筛选目标产品、目标日期的记录，拆分+1/-1事件
    SELECT 
        reserved_from AS event_time,
        1 AS event_value
    FROM reservation_table
    WHERE 
        product_id = 2
        AND DATE(reserved_from) = '2021-10-28'
    UNION ALL
    SELECT 
        reserved_till AS event_time,
        -1 AS event_value
    FROM reservation_table
    WHERE 
        product_id = 2
        AND DATE(reserved_from) = '2021-10-28'
),
sorted_events AS (
    -- 事件排序：同时间点先处理开始事件，满足「结束时间点算占用」的业务规则，如需调整可交换排序顺序
    SELECT 
        event_value,
        SUM(event_value) OVER(ORDER BY event_time, event_value DESC) AS current_overlap
    FROM event_list
)
-- 取最大重叠数
SELECT MAX(current_overlap) AS max_reserved_count FROM sorted_events;

注意：如果表数据量较大，建议将DATE(reserved_from) = '2021-10-28'替换为reserved_from >= '2021-10-28 00:00:00' AND reserved_from < '2021-10-29 00:00:00'，可以用到reserved_from上的索引，查询效率更高。

规则说明 #

• 如果你的业务规则中，结束时间点不算占用（即A订单12:00结束，B订单12:00开始不算重叠），只需要把排序逻辑修改为ORDER BY event_time, event_value ASC即可
• 针对你提供的示例数据，执行上述SQL会返回结果3，和你的预期结果一致

无窗口函数版本（兼容MySQL 5.x等低版本数据库） #

如果你的数据库不支持窗口函数，可以用变量累加的方式实现，以MySQL为例：

SELECT MAX(current_overlap) AS max_reserved_count
FROM (
    SELECT 
        event_value,
        @current := @current + event_value AS current_overlap
    FROM (
        SELECT 
            reserved_from AS event_time,
            1 AS event_value
        FROM reservation_table
        WHERE 
            product_id = 2
            AND DATE(reserved_from) = '2021-10-28'
        UNION ALL
        SELECT 
            reserved_till AS event_time,
            -1 AS event_value
        FROM reservation_table
        WHERE 
            product_id = 2
            AND DATE(reserved_from) = '2021-10-28'
        ORDER BY event_time, event_value DESC
    ) AS t, (SELECT @current := 0) AS init
) AS t2;

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