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

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

Erwin Brands.. 7

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".

LEFT JOIN返回表格,以5分钟的间隔切片数据.小心地包括下边框并排除上边框.

要在没有发生任何事情的情况下丢弃5分钟的插槽,请使用JOIN替代LEFT JOIN.

要让您的网格时间从0:00,5:00开始,向下舍min("time")入generate_series().

在这些相关答案中有更多解释:
按数据间隔分组
PostgreSQL:查询"按分钟"运行的行数

旁白:我不会time用作标识符.它是标准SQL中的保留字和Postgres中的函数/类型名称.