Mysql按自定义周期汇总_在MySQL中按时间跨度汇总数据

Basically I want is to aggregate some values in a table according to a timespan.

What I do is, I take snapshots of a system every 15 minutes and I want to be able to draw some graph over a long period. Since the graphs get really confusing if too many points are shown (besides getting really slow to render) I want to reduce the number of points by aggregating multiple points into a single point by averaging over them.

For this I'd have to be able to group by buckets that can be defined by me (daily, weekly, monthly, yearly, ...) but so far all my experiments had no luck at all.

Is there some trick I can apply to do so?

解决方案

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

Perhaps you can use the DATE_FORMAT() function, and grouping. Here's an example, hopefully you can adapt to your precise needs.

SELECT

DATE_FORMAT( time, "%H:%i" ),

SUM( bytesIn ),

SUM( bytesOut )

FROM

stats

WHERE

time BETWEEN AND

GROUP BY

DATE_FORMAT( time, "%H:%i" )

If your time window covers more than one day and you use the example format, data from different days will be aggregated into 'hour-of-day' buckets. If the raw data doesn't fall exactly on the hour, you can smooth it out by using "%H:00."

Thanks be to martin clayton for the answer he provided me.