MySQL窗口函数每5分钟计算一次平均值或最大值

2021-09-25 00:00:00 sql group-by window-functions mysql

我有一个表 data，其中包含 clock (unixtime) 和 value 列，其中记录每 50-70 秒出现一次.我需要绘制反映每 5 分钟时间的最大值(或平均值)的月度图表.为此，我需要进行一个查询，每 5 分钟对值进行一次分组和计数.但我就是做不到.

I have a table data with columns clock (unixtime) and value, where records appear every 50-70 seconds. I need to draw a monthly graph that reflects the maximum (or average) values for every 5 minutes of time. To do this, I need to make a query that would group and count the values for every 5 minutes. But I just can't do it.

SELECT clock, value FROM data WHERE clock BETWEEN 1622667600 AND 1625259600

(示例时钟)返回:

如何按 5 行或 300 秒对其进行分组，以获得如下结果(例如，MAX 为 5 行)?

How to group this by 5 rows or 300 seconds, to get a result like below (for example, MAX of 5 rows)?

  1. 1622667879 - 94691016
  2. 1622668182 - 43543688
  3. 1622668479 - 42904552
  4. 1622668781 - 41118136
  5. 1622669079 - 47856848

推荐答案

五分钟有 300 秒.所以你可以使用算术来聚合结果:

There are 300 seconds in five minutes. So you can use arithmetic to aggregate the results:

SELECT (FLOOR(clock / 300) * 300) as period_start,
       MIN(clock), MAX(clock), AVG(value)
FROM data 
WHERE clock BETWEEN 1622667600 AND 1625259600
GROUP BY FLOOR(clock / 300);

我不知道你是否需要 WHERE 子句，但我把它留在了.

I don't know if you want the WHERE clause, but I left it in.

相关文章