SQL Server 2008及以上版本按小时统计事件类型数量

需要在指定起止日期范围内，按小时统计各事件类型的数量，要求每个事件类型在每小时的计数为0或大于0；若@AllEventTypes中的事件类型在@Events中无对应数据，该小时计数显示为0。当前查询仅支持按天统计，需修改为按小时统计。

示例数据表 #

declare @Events table(EventDateTime DateTime2,EventType int)  
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:08.0000000', 12)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:07:04.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:00.0000000', 42)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T12:59:27.0000000', 60)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:03.0000000', 11)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:03.0000000', 43)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T21:11:23.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:21.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:00.0000000', 12)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:13.0000000', 55)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:11:20.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:03.0000000', 53)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T13:00:21.0000000', 55)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T13:00:21.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T13:00:21.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:12:24.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:14:04.0000000', 60)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:15:22.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:08.0000000', 42)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:15:28.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:13.0000000', 43)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:12:56.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:43:48.0000000', 11)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:14:14.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T12:59:27.0000000', 60)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T04:59:08.0000000', 42)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:09:10.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T13:00:21.0000000', 43)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T04:59:12.0000000', 61)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:08:59.0000000', 60)

所有事件类型 #

declare @AllEventTypes table(EventType int)
insert into @AllEventTypes values (12),(20), (21),(22),(30),(31),
                                  (32),(40),(41),(42),(43),(44),
                                  (45),(46),(47),(50),(51),(52),
                                  (53),(54),(55),(56),(57),(58),
                                  (59),(60),(61),(70),(71),(72)

指定日期范围 #

DECLARE @StartDate DATETIME2 = '2022-12-20 00:00:01.0000000', 
        @EndDate DATETIME2 = '2022-12-21 23:59:59.0000000'  

生成所有小时时段的代码 #

declare @AllDates table(Dates DateTime2)
insert into @AllDates 

SELECT  TOP (DATEDIFF(Hour, @StartDate, @EndDate) + 1)
Date = DATEADD(Hour, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;
-- Select * from @AllDates

当前按天统计的查询代码 #

SELECT DATEADD(dd, DATEDIFF(dd, 0, EventDateTime), 0) as Date,
EventType ,
Count(EventType) as EventCount
from  @Events
where   EventDateTime>=@StartDate and EventDateTime<=@EndDate
GROUP BY  DATEADD(dd, DATEDIFF(dd, 0, EventDateTime), 0),EventType 

解决方案 #

要实现按小时统计且每个事件类型每小时都有计数（无数据则为0），需通过笛卡尔积生成所有小时-事件类型的组合，再左连接实际事件数据进行统计：

-- 先生成所有小时区间和事件类型的笛卡尔积
WITH HourlyEventTypes AS (
    SELECT 
        ad.Dates AS HourStart,
        aet.EventType
    FROM @AllDates ad
    CROSS JOIN @AllEventTypes aet
),
-- 统计每个小时每个事件类型的实际发生数
EventCounts AS (
    SELECT
        DATEADD(Hour, DATEDIFF(Hour, 0, e.EventDateTime), 0) AS HourStart,
        e.EventType,
        COUNT(*) AS EventCount
    FROM @Events e
    WHERE e.EventDateTime >= @StartDate AND e.EventDateTime <= @EndDate
    GROUP BY DATEADD(Hour, DATEDIFF(Hour, 0, e.EventDateTime), 0), e.EventType
)
-- 左连接得到所有组合的计数，无数据则显示0
SELECT
    het.HourStart,
    het.EventType,
    ISNULL(ec.EventCount, 0) AS EventCount
FROM HourlyEventTypes het
LEFT JOIN EventCounts ec ON het.HourStart = ec.HourStart AND het.EventType = ec.EventType
ORDER BY het.HourStart, het.EventType;

核心逻辑说明 #

1. HourlyEventTypes：通过@AllDates和@AllEventTypes的交叉连接，生成指定日期范围内每小时与所有事件类型的全量组合，确保每个小时每个事件类型都有一条记录。
2. EventCounts：对@Events按小时和事件类型分组，统计实际发生数。
3. 最终查询：将全量组合左连接实际统计结果，用ISNULL把无数据的计数替换为0，最后按小时和事件类型排序。

性能优化建议（针对数百万条数据） #

• 为@Events表建立联合索引：CREATE NONCLUSTERED INDEX IX_Events_EventDateTime_EventType ON Events(EventDateTime, EventType);，提升分组统计效率。
• 替换sys.all_objects生成小时区间的方式，改用更高效的循环生成：

declare @AllDates table(Dates DateTime2)
DECLARE @CurrentHour DATETIME2 = @StartDate
WHILE @CurrentHour <= @EndDate
BEGIN
    INSERT INTO @AllDates VALUES (@CurrentHour)
    SET @CurrentHour = DATEADD(Hour, 1, @CurrentHour)
END

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