MySQL窗口函数每5分钟计算一次平均值或最大值 [英] MySQL window function to calculate averages or maximums every 5 minutes
问题描述
我有一个表 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.
这篇关于MySQL窗口函数每5分钟计算一次平均值或最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
