SQL Server按小时滚动统计事件数量的查询实现方法

核心逻辑 #

• 先生成统计当日0点到23点共24个整点时段的完整序列，保证无事件发生的小时也会返回0值，不会缺项
• 事件与小时段的匹配规则完全对齐需求：事件在当前小时段结束前已启动，且在当前小时段开始时未结束，即计入该小时统计数，天然支持跨小时事件的滚动累计，不需要额外写累加逻辑
• 左连接事件表做聚合统计，性能优于逐行循环展开事件跨小时记录的方案

可直接运行的SQL代码 #

-- 修改变量值即可切换需要统计的目标日期
DECLARE @StatsDate DATE = '2022-07-03';

WITH HourSequence AS (
    -- 锚点：生成当日0点时段
    SELECT 
        0 AS HourIndex,
        CAST(@StatsDate AS DATETIME) AS HourStart,
        DATEADD(HOUR, 1, CAST(@StatsDate AS DATETIME)) AS HourEnd
    UNION ALL
    -- 递归生成1-23点的所有时段
    SELECT 
        HourIndex + 1,
        DATEADD(HOUR, HourIndex + 1, CAST(@StatsDate AS DATETIME)),
        DATEADD(HOUR, HourIndex + 2, CAST(@StatsDate AS DATETIME))
    FROM HourSequence
    WHERE HourIndex < 23
)
SELECT
    CONVERT(VARCHAR(8), HourStart, 108) AS Hour,
    COUNT(e.ID) AS NumEvents
FROM HourSequence hs
LEFT JOIN Events e
    ON e.StartDateTime < hs.HourEnd
    AND e.EndDateTime >= hs.HourStart
GROUP BY hs.HourIndex, hs.HourStart
ORDER BY hs.HourIndex;

示例数据验证结果 #

针对提供的6条测试数据，上述代码返回结果如下，完全匹配计数规则：

原有代码问题说明 #

之前写的统计逻辑存在两个核心问题：

• GROUP BY StartDateTime是按精确到秒的开始时间戳分组，没有按小时维度截断聚合，会导致同一小时启动的事件被拆分为多组
• 仅统计事件启动所在的小时，没有覆盖事件持续跨多个小时的场景，无法实现滚动累计计数

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