Aggregation over 5 minute periods

[sambrierley]

Hi, im unsure of the best way to achieve this so any help is greatly appreaciated.
I have several pieces of equiptment that register data every minute, is there a way to do like a 5 minute average i.e 00:00 - 00:04, 05:00 - 09:00
NOT 00:00- 05:00, 01:00- 06:00 and so on.

thanks for any help

 

[MarkK]

1) Round the date/time value to the nearest 5 minutes.
2) Write a GROUP BY query that groups by that newly rounded date/time
3) Use the Avg() function

Query 1:
SELECT MyRoundToFunction(DateTimeValue, 5) As RoundedDateTime, SensorValue
FROM MyTable

Query 2:
SELECT RoundedDateTime, Avg(SensorValue) As AvgSensor
FROM Query1
GROUP BY RoundedDateTime

 

[sambrierley]

Thank you very much

 

[gemma-the-husky]

try looking at the access partition function. It's little known, but does that sort of thing.

(now I see this is an old post, but no matter)