Лучший способ подсчета записей по произвольным временным интервалам в Rails+Postgres

Question

Мое приложение имеет Events таблица с отметками времени событий.

Мне нужно сообщить количество событий во время каждого из последних N временные интервалы. Для разных отчетов интервалом может быть "каждая неделя" или "каждый день" или "каждый час" или "каждый 15-минутный интервал".

Например, пользователь может отобразить, сколько заказов он получил каждую неделю, день, час или четверть часа.

1) Я предпочитаю динамически выполнять один SQL-запрос (я использую Postgres), который группируется с произвольным интервалом времени. Есть способ сделать это?

2) Простой, но уродливый метод грубой силы состоит в том, чтобы сделать один запрос для всех записей в пределах начального / конечного таймфрейма, отсортированных по метке времени, а затем создать метод подсчета вручную для любого интервала.

3) Другой подход - добавить отдельные поля в таблицу событий для каждого интервала и статически хранить the_weekthe_day, the_hour, а также the_quarter_hour поле, поэтому я беру "удар" в момент создания записи (один раз), а не каждый раз, когда я сообщаю об этом поле.

Какова лучшая практика здесь, учитывая, что я мог бы изменить модель и предварительно сохранить интервальные данные, если это необходимо (хотя и за скромный счет удвоения ширины таблицы)?

Answer 1

К счастью, вы используете PostgreSQL. Функция окна generate_series() твой друг.

Прецедент

Учитывая следующую таблицу испытаний (которую вы должны были предоставить):

CREATE TABLE event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series(timestamp '2018-05-01'
                     , timestamp '2018-05-08'
                     , interval '7 min') + random() * interval '7 min';

Одно событие на каждые 7 минут (плюс от 0 до 7 минут, случайно).

Основное решение

Этот запрос подсчитывает события за любой произвольный интервал времени. 17 минут в примере:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

• Запрос извлекает минимум и максимум ts из базовой таблицы, чтобы охватить весь диапазон времени. Вместо этого вы можете использовать произвольный диапазон времени.

• Укажите любой временной интервал по мере необходимости.

• Создает одну строку для каждого временного интервала. Если в течение этого интервала не произошло никаких событий, счетчик 0,

• Обязательно обрабатывайте верхнюю и нижнюю границу правильно:

  • Неожиданные результаты запроса SQL с метками времени BETWEEN

• Функция окна lead() имеет часто пропускаемую особенность: он может обеспечить значение по умолчанию, когда нет ведущей строки. обеспечение 'infinity' в примере. В противном случае последний интервал будет обрезан верхней границей. NULL,

Минимальный эквивалент

Приведенный выше запрос использует CTE и lead() и подробный синтаксис. Элегантно и, возможно, легче понять, но немного дороже. Вот более короткая, быстрая, минимальная версия:

SELECT start_time, count(e.ts) AS events
FROM  (SELECT generate_series(min(ts), max(ts), interval '17 min') FROM event) g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '17 min'
GROUP  BY 1
ORDER  BY 1;

Пример "каждые 15 минут на прошлой неделе"

И форматирование с to_char(),

SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), count(e.ts) AS events
FROM   generate_series(date_trunc('day', localtimestamp - interval '7 days')
                     , localtimestamp
                     , interval '15 min') g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '15 min'
GROUP  BY start_time
ORDER  BY start_time;

Еще ORDER BY а также GROUP BY на базовое значение метки времени, а не на отформатированную строку. Это быстрее и надежнее.

дБ <> скрипка здесь

Связанный ответ, производящий подсчет времени за период времени:

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