Query using group by with steps/range over large data

I have a table that stores a sensor temperature readings every few seconds

Sample data looks like this

nId nOperationId    strDeviceIp     nIfIndex    nValue      nTimestamp
97  2               192.168.99.252  1           26502328    1593828551
158 2               192.168.99.252  1           26501704    1593828667
256 2               192.168.99.252  1           26501860    1593828788
354 2               192.168.99.250  1           26501704    1593828908
452 2               192.168.99.250  1           26501692    1593829029

I want to have the average temperature per device so I ran the following query

select strDeviceIp, AVG(CAST(nValue as bigint)) as val1
from myTable
where nOperationId = 2 and nTimestamp >= 1593828600 and nTimestamp <= 1593838600
group by  strSwitchIp;

Where I can pass the time range I want.

My issue is that this gives me total average but I want steps or range I want to achieve that instead of one line I’ll get all the values in a range/step of say 5 minutes as a row.

P.S. I’m trying to show a graph.

Running the following query I get

strSwitchIp     average
192.168.99.252  26501731

But I would like to get

strSwitchIp     average     timestamp
192.168.99.252  26201731    1593828600
192.168.99.252  26532731    1593828900
192.168.99.252  24501721    1593829200
192.168.99.252  26506531    1593829500

In this example I would like to get a row every 300 seconds, per device.

Answer

Since your nTimestamp is number of seconds, you can simply add it to the GROUP BY. Division by 300 gives you 300 second (5 minute) intervals. In SQL Server / is integer division, which discards the fractional part.

select 
    strSwitchIp
    ,AVG(CAST(nValue as bigint)) as val1
    ,(nTimestamp / 300) * 300 AS Timestamp
from myTable
where 
    nOperationId = 2 and nTimestamp >= 1593828600 and nTimestamp <= 1593838600
group by
    strSwitchIp
    ,nTimestamp / 300
;

nTimestamp / 300 gives an integer, a number of 5-minute intervals since 1970. / discards here the fractional part.

When this number is multiplied back by 300, it becomes again the number of seconds since 1970, but “rounded” to the nearest 5-minute interval. Just as you showed in the question in the expected result.

For example:

1593828667 / 300 = 5312762.2233333333333333333333333
discard fractional part
1593828667 / 300 = 5312762
5312762 * 300 = 1593828600

So, all timestamps between 1593828600 and 1593828899 become 1593828600 and all values for these timestamps are grouped into one row and averaged.