如何在MySQL中生成一系列每小时平均值? [英] How do I generate a series of hourly averages in MySQL?
问题描述
我的表中每隔十分钟就有一次数据:
I've got data in ten minutes intervals in my table:
2009-01-26 00:00:00 12
2009-01-26 00:10:00 1.1
2009-01-26 00:20:00 11
2009-01-26 00:30:00 0
2009-01-26 00:40:00 5
2009-01-26 00:50:00 3.4
2009-01-26 01:00:00 7
2009-01-26 01:10:00 7
2009-01-26 01:20:00 7.2
2009-01-26 01:30:00 3
2009-01-26 01:40:00 25
2009-01-26 01:50:00 4
2009-01-26 02:00:00 3
2009-01-26 02:10:00 4
etc.
是否可以为MySQL制定一个SQL查询,每小时返回一系列平均值?
Is it possible to formulate a single SQL-query for MySQL which will return a series of averages over each hour?
在这种情况下,它应该返回:
In this case it should return:
5.42
8.87
etc.
推荐答案
目前尚不清楚是否要在几天内汇总平均值.
It's unclear whether you want the average to be aggregated over days or not.
如果您希望26号午夜与27号午夜的平均值不同,则可以这样修改Mabwi的查询:
If you want a different average for midnight on the 26th vs midnight on the 27th, then modify Mabwi's query thus:
SELECT AVG( value ) , thetime
FROM hourly_averages
GROUP BY DATE( thetime ), HOUR( thetime )
请注意GROUP BY
子句中的其他DATE()
.没有这个,查询将把00:00
到00:59
的所有数据平均在一起,而不考虑发生的日期.
Note the additional DATE()
in the GROUP BY
clause. Without this, the query would average together all of the data from 00:00
to 00:59
without regard to the date on which it happened.
这篇关于如何在MySQL中生成一系列每小时平均值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!