MYSQL Get the average of a column each 10 minutes

user_15648345 0
Viewed 189

I have a table test containing data with 1 minute step, here is an extract of it:

DATE_TIME VALUE_G
2016-01-01 00:30:00 0.0
2016-01-01 00:31:00 0.0
2016-01-01 00:32:00 0.0
2016-01-01 00:33:00 0.0
2016-01-01 00:34:00 0.0
2016-01-01 00:35:00 0.0
2016-01-01 00:36:00 0.0
2016-01-01 00:37:00 0.0
2016-01-01 00:38:00 0.09
2016-01-01 00:39:00 0.8
2016-01-01 00:40:00 1.1
2016-01-01 00:41:00 1.1
2016-01-01 00:42:00 1.1
2016-01-01 00:43:00 0.77
2016-01-01 00:44:00 0.37
2016-01-01 00:45:00 0.37
2016-01-01 00:46:00 0.37
2016-01-01 00:47:00 0.52
2016-01-01 00:48:00 0.65
2016-01-01 00:49:00 0.4
2016-01-01 00:50:00 0.27

I want to get the average of VALUE_G every 10 minutes, but I want the average to be calculated like this:

DATE_TIME_AGG AVG(VALUE_G)
2016-01-01 00:30:00 0.0
2016-01-01 00:40:00 0.199
2016-01-01 00:50:00 0.592

In the above example, for the first row, the average is calculated for DATE_TIME between "2016-01-01 00:21:00" and "2016-01-01 00:30:00", in the second row : between "2016-01-01 00:31:00" and "2016-01-01 00:40:00" and in the third row between "2016-01-01 00:41:00" and "2016-01-01 00:50:00". How can I achieve this knowing that the table test contains a lot of data.

Following this answer https://stackoverflow.com/a/4073342/15648345 I can get part of the work done but, the average is not calculated as I want. Here is the code :

select from_unixtime(ROUND(unix_timestamp(DATE_TIME) / (60*10)) * 60 * 10) as DATE_TIME_AGG ,AVG(VALUE_G)
from test
group by DATE_TIME_AGG;
aggregate group-by datetime mysql average
2 Answers

Use CEIL() instead of ROUND().

Your from_unixtime(ROUND(unix_timestamp(DATE_TIME) / (60*10)) * 60 * 10) way of reducing your DATE_TIME values to ten minute buckets for aggregation is close. Because it contains ROUND() it maps

2016-01-01 00:30 to 2016-01-01 00:30  (minutes 25-34 map to 30)
2016-01-01 00:35 to 2016-01-01 00:30  (minutes 35-44 map to 40)

According to your question, you want this mapping.

2016-01-01 00:30 to 2016-01-01 00:30    (minutes 21-30 map to 30)
2016-01-01 00:31 to 2016-01-01 00:40    (minutes 31-40 map to 40)

So you need a function on DATE_TIME to do that particular mapping. This, I believe, does the trick for you, by using CEIL() in place of ROUND().

  from_unixtime(CEIL(unix_timestamp(DATE_TIME) / (60*10)) * 60 * 10)

Check it out here. https://www.db-fiddle.com/f/ufuCQ82vYc6oueBnkNbAJ5/0

user_2056080

You can try below query

SELECT date_format(date_time,'%Y-%m-%d %H:00')  +
            interval (minute(date_time) - case when minute(date_time) mod 10 = 0 then 0  else (-10+minute(date_time) mod 10)  end  ) minute as datestamp,
avg(value_g)
    FROM test
    group by 1
    order by 1
user_77724830
Related