Geek Answers Handbook

MySQL Time Aggregation

Basically I want to aggregate some values ​​in a table according to a time interval.

What I do, I take system snapshots every 15 minutes, and I want to be able to draw a graph for a long period. Since the charts really get confused if too many points are shown (except that it turns out very slowly for rendering), I want to reduce the number of points by aggregating several points into one point by averaging over them.

To do this, I would have to group by buckets, which can be determined by me (daily, weekly, monthly, yearly, ...), but so far all my experiments have not been lucky at all.

Is there any trick I can apply for this?

+7
mysql group-by aggregate
source share
2 answers

I had a similar question: collating-stats-into-time-chunks and it answered very well. Essentially, the answer was:

Perhaps you can use the DATE_FORMAT () function and group. Here is an example, I hope you can adapt to your specific needs.

SELECT DATE_FORMAT( time, "%H:%i" ), SUM( bytesIn ), SUM( bytesOut ) FROM stats WHERE time BETWEEN <start> AND <end> GROUP BY DATE_FORMAT( time, "%H:%i" )

If your time window spans more than one day, and you use an example format, data from different days will be aggregated into “time pores”. If the source data does not exactly hit the hour, you can smooth it using "% H: 00."

Thanks to Martin Clayton for the answer he provided me.

+10
source share

It's easy to trim the time to the last 15 minutes (for example) by doing something like:

 SELECT dateadd(minute, datediff(minute, '20000101', yourDateTimeField) / 15 * 15, '20000101') AS the15minuteBlock, COUNT(*) as Cnt FROM yourTable GROUP BY dateadd(minute, datediff(minute, '20000101', yourDateTimeField) / 15 * 15, '20000101');

Use similar truncation methods to group by hour, week, etc.

You can always wrap it in a CASE statement to process several methods using:

 GROUP BY CASE @option WHEN 'week' THEN dateadd(week, .....
+2
source share

More articles:


All Articles