如何编写TSQL按设备滚动24小时窗口统计心跳上报次数？

针对你需要按设备首次心跳为起点、滚动24小时划分窗口统计心跳次数的需求，可以通过**递归CTE（公共表表达式）**实现，以下是完整的TSQL解决方案：

完整代码 #

WITH OrderedHeartbeats AS (
    -- 按设备分组，给心跳记录按时间排序并生成行号
    SELECT 
        DeviceId,
        HeartbeatTimestamp,
        ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY HeartbeatTimestamp) AS RowNum
    FROM YourSensorTable -- 替换为你的实际表名
),
RecursiveWindows AS (
    -- 锚点：每个设备的第一条心跳作为第一个窗口的起始
    SELECT 
        DeviceId,
        HeartbeatTimestamp AS WindowStart,
        DATEADD(HOUR, 24, HeartbeatTimestamp) AS WindowEnd,
        RowNum AS LastRowInWindow
    FROM OrderedHeartbeats
    WHERE RowNum = 1

    UNION ALL

    -- 递归生成后续窗口：仅当心跳时间超过上一窗口结束时间时，作为新窗口起点
    SELECT 
        oh.DeviceId,
        oh.HeartbeatTimestamp AS WindowStart,
        DATEADD(HOUR, 24, oh.HeartbeatTimestamp) AS WindowEnd,
        oh.RowNum AS LastRowInWindow
    FROM OrderedHeartbeats oh
    JOIN RecursiveWindows rw ON oh.DeviceId = rw.DeviceId
    WHERE oh.RowNum = rw.LastRowInWindow + 1
      AND oh.HeartbeatTimestamp > rw.WindowEnd
)
-- 统计每个窗口内的心跳次数
SELECT 
    rw.DeviceId AS [设备ID（Device Id）],
    rw.WindowStart AS [心跳窗口起始时间（Heartbeat Window Start）],
    COUNT(oh.HeartbeatTimestamp) AS [心跳次数（Heartbeat Count）]
FROM RecursiveWindows rw
JOIN OrderedHeartbeats oh 
    ON oh.DeviceId = rw.DeviceId
    AND oh.HeartbeatTimestamp >= rw.WindowStart
    AND oh.HeartbeatTimestamp <= rw.WindowEnd
GROUP BY rw.DeviceId, rw.WindowStart
ORDER BY rw.DeviceId, rw.WindowStart;

代码说明 #

1. OrderedHeartbeats CTE
   对每个设备的心跳记录按时间戳升序排列，生成行号RowNum，用于后续递归时精准定位下一条心跳记录。

2. RecursiveWindows CTE

   • 锚点查询：选取每个设备的第一条心跳作为首个窗口的起始时间，同时计算窗口结束时间（起始时间+24小时），记录该窗口最后一条记录的行号。
   • 递归查询：从上一窗口的下一条心跳开始，判断其时间是否超过上一窗口的结束时间，若是则将其作为新窗口的起始，重复计算窗口范围。

3. 最终统计
   将递归生成的窗口与原始心跳记录关联，统计每个窗口内的心跳数量，最终按设备ID和窗口起始时间排序输出。

为什么之前的方法不符合需求？ #

• 按日期分组的方式是固定自然日窗口，无法匹配以设备首次心跳为起点的滚动24小时规则；
• 自连接的方式会把每条心跳都当作窗口起始，导致重复生成窗口（比如设备2的前3条心跳都会触发窗口），不符合“后续窗口从前一窗口24小时后第一条心跳开始”的要求。

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