SQL Server如何拆分时间区间为不相交区间并统计各区间人数

核心思路 #

我们采用事件点累加法实现，效率远高于区间匹配类方案：

• 将所有到访记录的开始时间记为「入场事件」，人数变化值为+1
• 将所有到访记录的结束时间记为「离场事件」，人数变化值为-1
• 所有事件按时间排序后，累加人数变化值即可得到每个时间点的当前在场人数
• 取相邻两个时间点拼接为互不重叠的统计区间，对应区间人数就是前一个时间点的累加值

实现代码 #

WITH all_events AS (
    -- 提取所有入场+离场事件
    SELECT start_time AS event_time, 1 AS delta FROM data
    UNION ALL
    SELECT end_time AS event_time, -1 AS delta FROM data
),
event_with_stats AS (
    SELECT 
        event_time,
        -- 累加得到当前时间点的在场人数
        SUM(delta) OVER(ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_count,
        -- 取当前事件的下一个事件时间，作为区间结束时间
        LEAD(event_time) OVER(ORDER BY event_time) AS next_time
    FROM all_events
)
-- 过滤掉最后一个没有结束时间的事件，得到最终统计结果
SELECT 
    event_time AS start_time,
    next_time AS end_time,
    current_count AS [count]
FROM event_with_stats
WHERE next_time IS NOT NULL
ORDER BY start_time

效率说明 #

该方案时间复杂度仅为O(n log n)，n为原始到访记录行数，仅需要对事件做一次排序即可完成计算，支持百万级以上数据量的高效计算，完全满足业务场景需求。

验证结果 #

针对你提供的测试数据，执行上述代码后输出结果和你期望的完全一致：

start_time              end_time                count
----------------------- ----------------------- -----------
2019-11-06 09:32:00.000 2019-11-06 09:35:00.000 1
2019-11-06 09:35:00.000 2019-11-06 09:41:00.000 2
2019-11-06 09:41:00.000 2019-11-06 09:43:00.000 1

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