MySQL窗口函数每5分钟计算一次平均值或最大值
我有一个表 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)?
- 1622667879 - 94691016
- 1622668182 - 43543688
- 1622668479 - 42904552
- 1622668781 - 41118136
- 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.
相关文章