在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.

我所做的是,我每15分钟拍摄一个系统的快照,我希望能够在很长一段时间内绘制一些图表。如果显示太多点(除了渲染速度非常慢)之外,图形会变得非常混乱,我想通过对多个点进行平均来将多个点聚合到一个点来减少点数。

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?

有什么技巧可以申请吗?

2 个解决方案

#1

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.

也许你可以使用DATE_FORMAT()函数和分组。这是一个例子,希望您能够适应您的确切需求。

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 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."

如果您的时间窗口超过一天并且您使用示例格式,则不同日期的数据将汇总到“每小时”桶中。如果原始数据不是完全按小时计算,则可以使用“%H:00”将其平滑。

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

感谢马丁克莱顿为他提供的答案。

#2

It's easy to truncate times to the last 15 minutes (for example), by doing something like:

通过执行以下操作,可以很容易地将时间截断到最后15分钟(例如):

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, whatever.

使用类似的截断方法按小时,周,等等分组。

You could always wrap it up in a CASE statement to handle multiple methods, using:

您可以始终将其包装在CASE语句中以处理多个方法,使用:

GROUP BY CASE @option WHEN 'week' THEN dateadd(week, .....

#1