MySql - 按时间间隔和平均值对行进行分组

Question

我在 MySQL 数据库中有一个表，其中的行每分钟采集一次数据。我想按 15 分钟对这些行进行分组，取平均值

我将日期存储为 DATEFORMAT。

我有一个查询，将它们按 1 小时分组，但我无法在 15 分钟内完成这项工作

select machineId as machineId, 
       avg(value) as value,
       DATE_FORMAT(MAX(LASTUPDATE),'%Y-%m-%d %H:00:00') AS LASTUPDATE 
from Power 
WHERE machineId in (1,2) AND 
      str_to_date(LASTUPDATE, '%Y-%m-%d %H:%i:%s' ) > str_to_date('2018-11-15 09:00:00', '%Y-%m-%d %H:%i:%s') 
group by date_format( LASTUPDATE, '%Y%m%d%H'  ), machineId

有什么想法吗？

Answer 1

是的，您只需要用 15 分钟的时间片来表示时间即可。

 FLOOR(CAST(DATE_FORMAT(last_update, '%i') AS UNSIGNED)/15)

然后转换回分钟:

 15*FLOOR(CAST(DATE_FORMAT(last_update, '%i') AS UNSIGNED)/15)

然后将其放入您的查询中(简化):

 SELECT machine
 , CONCAT(DATE_FORMAT(last_update, '%Y-%m-%d %H')
    , LPAD(15*FLOOR(CAST(DATE_FORMAT(last_update, '%i') AS UNSIGNED)/15), 2, '0')
    ) AS timebin
 , AVG(value)
 FROM yourtable
 GROUP BY machine
 , DATE_FORMAT(last_update, '%Y-%m-%d %H')
 , 15*FLOOR(CAST(DATE_FORMAT(last_update, '%i')/15))