选择按5分钟周期分组的平均记录

Question

我有一个小问题。我有这样格式的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

Answer 1

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：“按分钟”运行查询的行数

另外：我不会使用它time作为标识符。它是标准SQL中的保留字，而在Postgres中是函数/类型名。