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

Ответ 1

Только минуты с активностью

Кратчайший

Не будет намного проще, чем это:

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");

Fastest

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 значения (там, где ни одна строка не существует) не добавляют к счету выполнения.

Fastest

С помощью 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;

• Это самый быстрый из нескольких вариантов, которые я тестировал с помощью Postgres 9.1 - 9.4.