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

Question

Мне нужно запросить за каждую минуту общее количество строк до этой минуты.

Лучшее, чего я мог достичь, пока не помогает. Он возвращает количество в минуту, а не общее количество до каждой минуты:

SELECT COUNT(id) AS count
     , EXTRACT(hour from "when") AS hour
     , EXTRACT(minute from "when") AS minute
  FROM mytable
 GROUP BY hour, minute

Answer 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(), он возвращает именно то, что вам нужно.

• Не включать id в запросе, так как вы хотите GROUP BY мелкие кусочки.

• count() обычно используется как простая агрегатная функция. Добавление OVER предложение делает его оконной функцией. не указывать PARTITION BY в определении окна - вы хотите счетчик по всем строкам. По умолчанию это считается от первой строки до последней равноправной текущей строки, как определено ORDER BY, Я цитирую руководство:

  Опция кадрирования по умолчанию RANGE UNBOUNDED PRECEDING, который так же, как RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, С ORDER BY, это устанавливает фрейм, чтобы все строки от раздела запускались до последней строки текущего ORDER BY всматриваться.

  И это именно то , что вам нужно.

• использование count(*) скорее, чем count(id), Это лучше соответствует вашему вопросу ("количество строк"). Как правило, немного быстрее, чем count(id), И, хотя мы могли бы предположить, что id является NOT NULL, это не было указано в вопросе, поэтому count(id) строго говоря, неверно, потому что значения 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;

Как и выше, но:

• Я использую подзапрос для агрегирования и подсчета строк в минуту. Таким образом, мы получаем 1 ряд в минуту без DISTINCT во внешнем SELECT,

• использование 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")
                        , interval '1 min')
   FROM   tbl
   ) m(minute)
LEFT   JOIN (SELECT date_trunc('minute', "when") FROM tbl) c(minute) USING (minute)
ORDER  BY 1;

• Создайте строку для каждой минуты в промежутке времени между первым и последним событием с 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(cte.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM  (
   SELECT generate_series(min(minute), max(minute), interval '1 min')
   FROM   cte
   ) m(minute)
LEFT   JOIN cte USING (minute)
ORDER  BY 1;

• Опять же, агрегируйте и подсчитывайте количество строк в минуту на первом шаге, что исключает необходимость в последующих DISTINCT,

• Отличный от count(), sum() может вернуться NULL, По умолчанию для 0 с COALESCE,

Со многими строками и индексом на "when" эта версия с подзапросом была самой быстрой среди пары вариантов, которые я тестировал с Postgres 9.1 - 9.4:

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")
                        , interval '1 min')
   FROM   tbl
   ) m(minute)
LEFT   JOIN (
   SELECT date_trunc('minute', "when") AS minute
        , count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) c USING (minute)
ORDER  BY 1;