Группировать по интервалам данных

Question

У меня есть одна таблица, в которой хранится информация о пропускной способности сети в течение определенного периода времени. Один столбец будет содержать дату и время (первичный ключ), а другой столбец будет записывать пропускную способность. Данные записываются каждую минуту. У нас будут другие столбцы, записывающие другие данные в этот момент времени.

Если пользователь запрашивает данные с 15-минутными интервалами (в течение 24-часового периода с учетом начальной и конечной даты), возможно ли с помощью одного запроса получить необходимые мне данные или мне нужно написать хранимую процедуру / курсор для этого? Затем пользователи могут запрашивать данные 5-минутных интервалов и т. Д.

Я, скорее всего, буду использовать Postgres, но есть ли другие варианты NOSQL, которые были бы лучше?

Есть идеи?

Answer 1

WITH t AS (
   SELECT ts, (random()*100)::int AS bandwidth
   FROM   generate_series('2012-09-01', '2012-09-04', '1 minute'::interval) ts
   )

SELECT date_trunc('hour', ts) AS hour_stump
      ,(extract(minute FROM ts)::int / 15) AS min15_slot
      ,count(*) AS rows_in_timeslice               -- optional
      ,sum(bandwidth) AS sum_bandwidth
FROM   t
WHERE  ts >= '2012-09-02 00:00:00+02'::timestamptz -- user's time range
AND    ts <  '2012-09-03 00:00:00+02'::timestamptz -- careful with borders 
GROUP  BY 1, 2
ORDER  BY 1, 2;

CTE t предоставляет данные, которые может содержать ваша таблица: одна временная метка ts в минуту с bandwidth число. (Вам не нужна эта часть, вместо этого вы работаете со своим столом.)

Вот очень похожее решение для очень похожего вопроса - с подробным объяснением, как работает эта конкретная агрегация:

• date_trunc 5-минутный интервал в PostgreSQL

Вот аналогичное решение для аналогичного вопроса, касающегося выполнения сумм - с подробным объяснением и ссылками для различных используемых функций:

• PostgreSQL: подсчет количества строк для запроса "по минутам"

Дополнительный вопрос в комментарии

WITH -- same as above ...

SELECT DISTINCT ON (1,2)
       date_trunc('hour', ts) AS hour_stump
      ,(extract(minute FROM ts)::int / 15) AS min15_slot
      ,bandwidth AS bandwith_sample_at_min15
FROM   t
WHERE  ts >= '2012-09-02 00:00:00+02'::timestamptz
AND    ts <  '2012-09-03 00:00:00+02'::timestamptz
ORDER  BY 1, 2, ts DESC;

Извлекает одну неагрегированную выборку за 15-минутный интервал - из последней доступной строки в окне. Это будет 15-ая минута, если строка не пропущена. Решающие части DISTINCT ON а также ORDER BY,
Больше информации об используемой технике здесь:

• Выберите первую строку в каждой группе GROUP BY?

Answer 2

select
    date_trunc('hour', d) + 
    (((extract(minute from d)::integer / 5 * 5)::text) || ' minute')::interval
    as "from",
    date_trunc('hour', d) + 
    ((((extract(minute from d)::integer / 5 + 1) * 5)::text) || ' minute')::interval
    - '1 second'::interval
    as "to",
    sum(random() * 1000) as bandwidth
from 
    generate_series('2012-01-01', '2012-01-31', '1 minute'::interval) s(d)
group by 1, 2
order by 1, 2
;

Это на 5 минут колеблется. В течение 15 минут разделите на 15.