Выбор среднего числа записей, сгруппированных по 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-минутными интервалами. Тщательно включите нижнюю границу и исключите верхнюю границу.

• Чтобы сбросить 5-минутные слоты, где ничего не произошло, используйте JOIN на месте LEFT JOIN,

• Чтобы ваше время сетки началось в 0:00, 5:00 и т. Д., Округлите min("time") в generate_series(),

Больше объяснения в этих связанных ответах:
Группировать по интервалам данных
PostgreSQL: подсчет количества строк для запроса "по минутам"

В сторону: я бы не использовал time в качестве идентификатора. Это зарезервированное слово в стандартном SQL и имя функции / типа в Postgres.

Answer 2

Попробуйте это, он должен группировать минуты 0-4, 5-9, 10-14 и так далее...

SELECT MIN(time), AVG(Players), AVG(Servers)
FROM MyTable t
GROUP BY date_trunc('hour', time),
    FLOOR(datepart('minute', time)/12)

РЕДАКТИРОВАТЬ: изменил группировку сначала на час, а затем на Floor минут. Я думаю, что это должно работать.

Answer 3

Как насчет этого?

select datepart('year', time) as StartYear, datepart('month', time) as StartMonth,
    datepart('day', time) as StartDay, datepart('hour', time) as StartHour,
    floor(datepart('minute', time)/5)*5 as StartMinute,
    avg(case when datepart('minute', time) = floor(datepart('minute', time)/5)*5 then players else null end) as Zero,
    avg(case when datepart('minute', time) = floor(datepart('minute', time)/5)*5+1 then players else null end) as One,
    avg(case when datepart('minute', time) = floor(datepart('minute', time)/5)*5+2 then players else null end) as Two,
    avg(case when datepart('minute', time) = floor(datepart('minute', time)/5)*5+3 then players else null end) as Three,
    avg(case when datepart('minute', time) = floor(datepart('minute', time)/5)*5+4 then players else null end) as Four,
from MyTable
group by datepart('year', time), datepart('month', time),
    datepart('day', time), datepart('hour', time),
    floor(datepart('minute', time)/5)*5