Query using group by with steps/range over large data

user_1832932 0
Viewed 64

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.

sql-server sql
2 Answers

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.

user_41160170

you ca use partition like this:

select strDeviceIp, AVG(CAST(nValue as bigint)) as val1,
ROW_NUMBER() over(partition by nTimestamp order by nTimestamp desc) as ROW_NO from AmyTable) Q  where q.ROW_NO%5=0
....
user_72738290
Related