Ответ 1
К счастью для вас, вы используете PostgreSQL. generate_series() (зависит от Postgres), функция окна lead() и LEFT JOIN являются вашими друзьями:
Тестовый кейс
Учитывая следующую тестовую таблицу (которую вы должны были предоставить):
CREATE TEMP TABLE event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series('2015-04-17 0:0'::timestamp
,'2015-04-20 0:0'::timestamp
,interval '7 min') + random() * interval '7 min';
Одно событие за каждые 7 минут (плюс от 0 до 7 минут, случайным образом).
Базовое решение
Этот запрос подсчитывает события для любого произвольного интервала времени. 17 минут в примере:
WITH grid AS (
SELECT start_time
, lead(start_time) OVER (ORDER BY start_time) AS end_time
FROM (
SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
FROM event
) x
)
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 1
ORDER BY 1;
-
Запрос собирает минимальный и максимальный
timestampиз базовой таблицы автоматически, чтобы покрыть полный временной диапазон таблицы. Вместо этого вы можете использовать произвольный временной диапазон. -
Введите свой интервал выбора один раз. Может быть любой временной интервал, о котором вы можете подумать.
-
Одна строка для каждого временного интервала в результате. Если никакого события не произошло вообще, вы получите счетчик
0. -
Не забудьте правильно обработать верхнюю и нижнюю границу:
Ошибка PostgreSQL в отношении Heroku с" BETWEEN " -
Для текущего подсчета за период времени обратитесь к соответствующему ответу:
PostgreSQL: выполняется подсчет строк для запроса "минута"
Пример для "каждые 15 минут на прошлой неделе" `
Основное форматирование с to_char().
WITH grid AS (
SELECT start_time
, lead(start_time) OVER (ORDER BY start_time) AS end_time
FROM generate_series(now()::date - 7 -- truncates to start of day
, now()
, interval '15 min') AS start_time
)
SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), 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;Последняя строка угла
Чтобы округлить его, вы можете использовать:
lead(start_time, 1, now()) OVER (ORDER BY start_time) AS end_time
.. где now() - ваша верхняя граница диапазона времени из примера. Иначе последний интервал будет отключен, так как верхняя граница NULL.
lead() имеет часто упущенную функцию предоставления значения по умолчанию, если не существует ведущей строки.
Общее решение, охватывающее угловой корпус
WITH vals AS (
SELECT now()::date - 7 AS frame_start -- enter values once
, now() AS frame_end
, interval '15 min' AS t_interval
)
, grid AS (
SELECT start_time
, lead(start_time, 1, frame_end) OVER (ORDER BY start_time) AS end_time
FROM (
SELECT generate_series(frame_start, frame_end, t_interval) AS start_time
, frame_end
FROM vals
) x
)
SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), 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;
SQL Fiddle, отображающий все.