选择按5分钟周期分组的平均记录 [英] Selecting an average of records grouped by 5 minute periods

问题描述

我有一个小问题。我有这样格式的PostgreSQL表

I'm having a slight issue. I have a PostgreSQL table with such format

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中执行此操作。因此结果将是：

I would like to group them by 5 minute periods and get an average of the group as a single value, so there will be 20% of the records, each containing an average of ~5 numbers, with time set to the first time value in the group. I have no idea how to do this in PgSQL. So the result would be:

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

  Explain

  • The subquery grid produces one row for every 5 minutes from the minimum to the maximum of "time" in your table.

    LEFT JOIN以5分钟为间隔将数据切片到表中。小心地 include 下边界和 exclude 上边界。

    LEFT JOIN back to the table slicing data in 5-min intervals. Carefully include lower border and exclude upper border.

    要丢弃5分钟的时隙发生，请使用 JOIN 代替 LEFT JOIN 。

    To drop 5-min-slots where nothing happened, use JOIN in place of LEFT JOIN.

    要让网格时间开始于0：00、5：00等，请对中的 min（ time）取整generate_series（）。

    To have your grid-times start at 0:00, 5:00 etc, round down the min("time") in generate_series().

    这些相关答案中的更多解释：
    
    按数据间隔分组
    
    PostgreSQL：运行查询的行数'by分钟

    More explanation in these related answers:
    Group by data intervals
    PostgreSQL: running count of rows for a query 'by minute'

    在旁边：我不会使用 time 作为标识符。它是标准SQL中的保留字和一个函数/在Postgres中输入名称。

    Aside: I wouldn't use time as identifier. It's a reserved word in standard SQL and a function / type name in Postgres.

    这篇关于选择按5分钟周期分组的平均记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！