問題描述
我有一個(gè)表 data，其中包含 clock (unixtime) 和 value 列，其中記錄每 50-70 秒出現(xiàn)一次.我需要繪制反映每 5 分鐘時(shí)間的最大值(或平均值)的月度圖表.為此，我需要進(jìn)行一個(gè)查詢，每 5 分鐘對值進(jìn)行一次分組和計(jì)數(shù).但我就是做不到.
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
(示例時(shí)鐘)返回:
如何按 5 行或 300 秒對其進(jìn)行分組，以獲得如下結(jié)果(例如，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 秒.所以你可以使用算術(shù)來聚合結(jié)果:
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.
這篇關(guān)于MySQL窗口函數(shù)每5分鐘計(jì)算一次平均值或最大值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！