MYSQL 每10分钟获取一列的平均值

Question

我有一个表 test，其中包含步长为 1 分钟的数据，这是它的摘录:

<表类="s-表"><头><日>DATE_TIME VALUE_G<正文>2016-01-01 00:30:000.02016-01-01 00:31:000.02016-01-01 00:32:000.02016-01-01 00:33:000.02016-01-01 00:34:000.02016-01-01 00:35:000.02016-01-01 00:36:000.02016-01-01 00:37:000.02016-01-01 00:38:000.092016-01-01 00:39:000.82016-01-01 00:40:001.12016-01-01 00:41:001.12016-01-01 00:42:001.12016-01-01 00:43:000.772016-01-01 00:44:000.372016-01-01 00:45:000.372016-01-01 00:46:000.372016-01-01 00:47:000.522016-01-01 00:48:000.652016-01-01 00:49:000.42016-01-01 00:50:000.27

我想每 10 分钟获取一次 VALUE_G 的平均值，但我希望这样计算平均值:

<表类="s-表"><头>DATE_TIME_AGG平均(VALUE_G)<正文>2016-01-01 00:30:000.02016-01-01 00:40:000.1992016-01-01 00:50:000.592

在上面的示例中，对于第一行，计算第二行中“2016-01-01 00:21:00”和“2016-01-01 00:30:00”之间的 DATE_TIME 的平均值:在“2016-01-01 00:31:00”和“2016-01-01 00:40:00”之间，第三行在“2016-01-01 00:41:00”和“2016-01”之间-01 00:50:00”。知道表 test 包含大量数据，我该如何实现这一目标。

按照这个答案 https://stackoverflow.com/a/4073342/15648345我可以完成部分工作，但计算的平均值不是我想要的。这是代码:

select from_unixtime(ROUND(unix_timestamp(DATE_TIME) / (60*10)) * 60 * 10) as DATE_TIME_AGG ,AVG(VALUE_G)
from test
group by DATE_TIME_AGG;

Answer 1

使用 CEIL() 而不是 ROUND()。

您的 from_unixtime(ROUND(unix_timestamp(DATE_TIME)/(60*10)) * 60 * 10) 将您的 DATE_TIME 值减少到十分钟桶以进行聚合的方法很近。因为它包含 ROUND() 它映射

2016-01-01 00:30 to 2016-01-01 00:30  (minutes 25-34 map to 30)
2016-01-01 00:35 to 2016-01-01 00:30  (minutes 35-44 map to 40)

根据你的问题，你想要这个映射。

2016-01-01 00:30 to 2016-01-01 00:30    (minutes 21-30 map to 30)
2016-01-01 00:31 to 2016-01-01 00:40    (minutes 31-40 map to 40)

因此您需要在 DATE_TIME 上执行该特定映射的函数。我相信，通过使用 CEIL() 代替 ROUND()，这对您有用。

  from_unixtime(CEIL(unix_timestamp(DATE_TIME) / (60*10)) * 60 * 10) 

在这里查看。 https://www.db-fiddle.com/f/ufuCQ82vYc6oueBnkNbAJ5/0