我有一个小问题。我有这样格式的PostgreSQL表
time (datetime) | players (int) | servers (int)
---------------------------------------------------
2013-12-06 13:40:01 | 80 | 20
2013-12-06 13:41:13 | 78 | 21
etc.
我想按5分钟的时间对它们进行分组,并将该组的平均值作为一个单一值,因此将有20%的记录,每个记录平均包含〜5个数字,时间设置为中的第一个时间值群组。我不知道如何在PgSQL中执行此操作。因此结果将是:
2013-12-06 13:40:01 | avg of players on :40, :41, :42, :43, :44 | same with servers
2013-12-06 13:45:05 | avg of players on :45, :46, :47, :48, :49 | same with servers
2013-12-06 13:50:09 | avg of players on :50, :51, :52, :53, :54 | same with servers
2013-12-06 13:55:12 | avg of players on :55, :56, :57, :58, :59 | same with servers
SELECT grid.t5
,min(t."time") AS min_time
-- ,array_agg(extract(min FROM t."time")) AS 'players_on' -- optional
,avg(t.players) AS avg_players
,avg(t.servers) AS avg_servers
FROM (
SELECT generate_series(min("time")
,max("time"), interval '5 min') AS t5
FROM tbl
) grid
LEFT JOIN tbl t ON t."time" >= grid.t5
AND t."time" < grid.t5 + interval '5 min'
GROUP BY grid.t5
ORDER BY grid.t5;
子查询grid
每5分钟从表中的最小值“到最大值”产生一行time"
。
以5分钟为间隔将LEFT JOIN返回表切片数据。仔细包括下边框,并排除上边框。
要放弃5分钟的时间而什么也没发生,请使用JOIN
代替LEFT JOIN
。
要使您的发车时间从0:00、5:00等开始,请向下舍min("time")
入generate_series()
。
这些相关答案中的更多解释:
按数据间隔分组
PostgreSQL:“按分钟”运行查询的行数
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句