Мне нужно запросить каждую минуту общее количество строк до этой минуты.
Лучшее, что я смог достичь, пока не делает этого трюка. Он возвращает количество в минуту, а не общий счетчик до каждой минуты:
SELECT COUNT(id) AS count
, EXTRACT(hour from "when") AS hour
, EXTRACT(minute from "when") AS minute
FROM mytable
GROUP BY hour, minute
Не будет намного проще, чем это:
SELECT DISTINCT
date_trunc('minute', "when") AS minute
, count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM mytable
ORDER BY 1;
Используйте date_trunc(). Это дает вам именно то, что вам нужно. Помните, что при запуске с timestamptz начало "дня" определяется текущим часовым поясом.
Не включайте id в запрос, так как вы хотите GROUP BY минутные фрагменты.
count() в основном используется как простая функция aggregate. Добавление предложения OVER делает его функцией окна. Опустите PARTITION BY в определении окна - вы хотите, чтобы счетчик работал по всем строкам. По умолчанию это считается от первой строки до последнего однорангового узла текущей строки, как определено ORDER BY. Я цитирую руководство:
Параметр обрамления по умолчанию
RANGE UNBOUNDED PRECEDING, который является такой же, какRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;, он устанавливает кадр для всех строк из раздела, запускаемого через текущий row в очередиORDER BY.
И это именно то, что вам нужно.
Используйте count(*), а не count(id). Это лучше подходит вашему вопросу ( "количество строк" ). Он обычно немного быстрее, чем count(id). И, хотя мы можем предположить, что id есть NOT NULL, он не был задан в вопросе, поэтому count(id) является неправильным, строго говоря.
Вы не можете использовать GROUP BY минутные фрагменты на одном уровне запросов. Агрегатные функции применяются перед функциями окна, функция окна count(*) будет видеть только 1 строку в минуту таким образом.
Вы можете, однако, SELECT DISTINCT, потому что DISTINCT применяется после оконных функций.
ORDER BY 1 является просто сокращением для ORDER BY date_trunc('minute', "when") здесь. 1 служит в качестве позиционного параметра, ссылающегося на 1-ое выражение в предложении SELECT.
Используйте to_char(), если вам нужно украсить результат. Вот так:
SELECT DISTINCT
to_char(date_trunc('minute', "when"), 'DD.MM.YYYY HH24:MI') AS minute
, count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM mytable
ORDER BY date_trunc('minute', "when");
SELECT minute, sum(minute_ct) OVER (ORDER BY minute) AS running_ct
FROM (
SELECT date_trunc('minute', "when") AS minute
, count(*) AS minute_ct
FROM tbl
GROUP BY 1
) sub
ORDER BY 1;
Как и выше, но:
Я использую подзапрос, чтобы сбрасывать и подсчитывать строки в минуту.
Таким образом мы получаем разные строки в минуту во внешнем запросе, а шаг DISTINCT не нужен.
Используйте sum() как функцию агрегации окна, чтобы добавить подсчеты из подзапроса.
Я обнаружил, что это значительно быстрее со многими рядами в минуту.
@GabiMe спросил в комментарии, как получить одну строку для каждого minute в период времени, включая те, в которых не происходит никакого события (нет строки в базовой таблице):
SELECT DISTINCT
minute, count(c.minute) OVER (ORDER BY minute) AS running_ct
FROM (
SELECT generate_series(date_trunc('minute', min("when"))
, max("when")
, '1 min')
FROM tbl
) m(minute)
LEFT JOIN (SELECT date_trunc('minute', "when") FROM tbl) c(minute) USING (minute)
ORDER BY 1;
Создайте строку для каждой минуты в промежутке между первым и последним событиями с generate_series(). Объедините generate_series() с агрегатными функциями в одном подзапросе.
LEFT JOIN для всех временных меток, усеченных до минуты и подсчета. NULL значения (там, где ни одна строка не существует) не добавляют к счету выполнения.
С помощью CTE:
WITH cte AS (
SELECT date_trunc('minute', "when") AS minute, count(*) AS minute_ct
FROM tbl
GROUP BY 1
)
SELECT m.minute
, COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM (SELECT generate_series(date_trunc('minute', min("when"))
,max(minute), '1 min') AS minute FROM cte) m
LEFT JOIN cte c USING (minute)
ORDER BY 1;
Как и выше, но:
Снова сбросьте и подсчитайте строки в минуту на первом шаге, опустив необходимость в дальнейшем DISTINCT.
Чем меньше count(), sum() может возвращать NULL. Поэтому я завернул его в COALESCE, чтобы получить 0. Вместо этого.
С большим количеством строк и нескольких строк в минуту и с индексом на "when" эта версия с подзапросом должна быть еще быстрее:
SELECT m.minute
, COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM (SELECT generate_series(date_trunc('minute', min("when"))
, max("when"), '1 min') AS minute FROM tbl) m
LEFT JOIN (
SELECT date_trunc('minute', "when") AS minute
, count(*) AS minute_ct
FROM tbl
GROUP BY 1
) c USING (minute)
ORDER BY 1;