在MySQL中按时间跨度汇总数据 [英] Aggregating data by timespan in MySQL

问题描述

基本上我想要的是根据时间跨度汇总表中的某些值。

我所做的是，我每隔15分钟拍摄一次系统快照，而我希望能够长时间画出一些图。由于如果显示的点数太多（除了渲染速度非常慢），图形会变得非常混乱，我希望通过对点进行平均将多个点聚合为一个点来减少点数。

为此，我必须能够通过我可以定义的桶（每天，每周，每月，每年......）进行分组，但到目前为止，我的所有实验都没有运气。

是否有一些技巧可以应用于此？

解决方案

我有一个类似的问题：整理统计到时间块和它回答得非常好。本质上，答案是：

也许你可以使用DATE_FORMAT（）函数和分组。这里有一个例子，希望你能适应你的确切需求。

pre $ SELECT
DATE_FORMAT（time，％H： ％i），
SUM（bytesIn），
SUM（bytesOut）
FROM
stats
WHERE
time BETWEEN< start> AND< end>
GROUP BY
DATE_FORMAT（时间，％H：％i）


如果您的时间窗口覆盖超过一天，并使用示例格式，则来自不同日期的数据将汇总到每日时间的时段中。如果原始数据不完全符合小时，您可以使用％H：00将其平滑化。

感谢martin clayton提供的答案他提供了我。

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

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

这篇关于在MySQL中按时间跨度汇总数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！