SQL Server 2016如何查询连续24小时每小时均有事件的用户

实现方案 #

核心思路是先按用户+小时去重，再通过经典的「行号偏移法」识别连续小时序列，最后筛选出连续长度≥24小时的用户即可：
完整SQL语句如下：

WITH UserHourlyEvents AS (
    -- 第一步：按用户+小时去重，每个用户每个有事件的小时仅保留1条记录，对齐到小时整点
    SELECT DISTINCT 
        UserID,
        DATEADD(HOUR, DATEDIFF(HOUR, 0, Event_Timestamp), 0) AS Event_Hour
    FROM AuditData
),
HourlyConsecutiveGroups AS (
    -- 第二步：对每个用户的小时序列按时间排序，计算连续分组标识
    -- 连续的小时减去对应行号偏移的小时数，结果会是同一个固定值，用这个值作为分组ID
    SELECT 
        UserID,
        Event_Hour,
        DATEADD(HOUR, -1 * ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Event_Hour ASC), Event_Hour) AS Consecutive_Group_Id
    FROM UserHourlyEvents
)
-- 第三步：统计每个连续分组的小时数，筛选出有任意分组≥24小时的用户
SELECT DISTINCT UserID
FROM HourlyConsecutiveGroups
GROUP BY UserID, Consecutive_Group_Id
HAVING COUNT(1) >= 24;

验证效果 #

针对你提供的示例数据，执行上述SQL后返回结果仅为21482，和你预期的结果完全一致：

• 用户21482在2021-08-23 00:00到2021-08-23 23:00区间，刚好覆盖24个连续小时，符合规则
• 用户45578的事件仅覆盖12个连续小时（05点到16点），不满足24小时要求，不会被返回

拓展说明 #

如果需要同时输出符合条件的用户对应的连续24小时区间，可以修改最后一步的查询逻辑，返回每个符合要求的分组的最小/最大事件小时即可。

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