I have some sensor data in a table, as follows:

I want to write a TSQL statement where I get the number of times each device reported a heartbeat in a rolling 24 hour window for that device. So, in the above case, the result would look like:

Device 1, started its heartbeats at 2022-11-20 10:25:33 and had 3 heartbeats in the next 24 hours window. Device 2 had 3 heartbeats in the 24 hours starting at 12:31:50, but had another heartbeat at 15:25:33 the next day (so it is counted in a new rolling window).

Is there anyway I can write a TSQL for this?

The following query works if I were to group the counts by Date, but my rolling window needs to start from the first instance a device reports its heartbeat. The next group starts at the first record after the 24 hour period following the first/previous record.

SELECT DeviceId, Count(*), CONVERT(date, HeartbeatTimestamp)
FROM Table
GROUP BY DeviceId, CONVERT(date, HeartbeatTimestamp)

Just came up with this. Unfortunately, this is returning each DeviceId n times when the count is n. So, if Device Id 2 has a count of 3 in the first group, I get 3 rows (each showing the count as 3).

SELECT t1.DeviceId, t1.HeartbeatTimestamp, COUNT(*) AS Count FROM Table1 t1 INNER JOIN Table1 t2 ON t1.DeviceId = t2.DeviceId AND DATEDIFF(hour,t1.HeartbeatTimestamp, t2.HeartbeatTimestamp)<=24 GROUP BY t1.DeviceId,t1.HeartbeatTimestamp

CodePudding user response：

You can use a recursive query to find the Heartbeat Window Start. Then a simple GROUP BY query at the end to gives you the required result.

with cte as
(
      select DeviceId, HeartbeatTimestamp, 
             rn = row_number() over (partition by DeviceId
                                         order by HeartbeatTimestamp)
      from   sensors
),
rcte as
(
      select DeviceId, 
             [Heartbeat Window Start] = HeartbeatTimestamp,
             rn
      from   cte
      where  rn = 1

      union all

      select r.DeviceId,
             [Heartbeat Window Start] = case when datediff(second, [Heartbeat Window Start], c.HeartbeatTimestamp) > 24 * 60 * 60
                                             then c.HeartbeatTimestamp
                                             else [Heartbeat Window Start]
                                             end,
             c.rn
      from   rcte r
             inner join cte c on  r.DeviceId = c.DeviceId
                              and r.rn        = c.rn - 1
)
select DeviceId, 
       [Heartbeat Window Start], 
       [Heartbeat Count] = count(*)
from   rcte
group by DeviceId, [Heartbeat Window Start];