MySQL window function to calculate averages or maximums every 5 minutes

I have a table data with columns clock (unixtime) and value, where records appear every 50-70 seconds. I need to draw a monthly graph that reflects the maximum (or average) values for every 5 minutes of time. To do this, I need to make a query that would group and count the values for every 5 minutes. But I just can’t do it.

SELECT clock, value FROM data WHERE clock BETWEEN 1622667600 AND 1625259600

(example clocks) return this:

MySQL window function to calculate averages or maximums every 5 minutes

How to group this by 5 rows or 300 seconds, to get a result like below (for example, MAX of 5 rows)?

  1. 1622667879 – 94691016
  2. 1622668182 – 43543688
  3. 1622668479 – 42904552
  4. 1622668781 – 41118136
  5. 1622669079 – 47856848

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

There are 300 seconds in five minutes. So you can use arithmetic to aggregate the results:

SELECT (FLOOR(clock / 300) * 300) as period_start,
       MIN(clock), MAX(clock), AVG(value)
FROM data 
WHERE clock BETWEEN 1622667600 AND 1625259600
GROUP BY FLOOR(clock / 300);

I don’t know if you want the WHERE clause, but I left it in.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x