PostgreSQL: uruchamianie liczby wierszy dla zapytania 'na minutę'

Zwraca tylko minuty z aktywnością

Najkrótsza

SELECT DISTINCT
       date_trunc('minute', "when") AS minute
     , count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;

• Użycie date_trunc(), zwraca dokładnie to, czego potrzebujesz.

• Nie włączaj id do zapytania, ponieważ chcesz GROUP BY minute plasterki.

• count() jest zwykle używany jako zwykła funkcja agregująca . Dodanie klauzuli OVER czyni ją funkcją okna . Pomiń PARTITION BY w definicji okna - chcesz, aby liczył wszystkie wiersze. Domyślnie liczy się od pierwszego wiersza do ostatniego równika bieżącego wiersza zgodnie z definicją ORDER BY. cytuję instrukcję :

  Domyślną opcją kadrowania jest RANGE UNBOUNDED PRECEDING, czyli tak samo jak RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Z ORDER BY, to ustawia ramkę jako wszystkie wiersze od uruchomienia partycji przez ostatni ORDER BY peer bieżącego wiersza.

  I to jest dokładnie to, czego potrzebujesz.

• Użyj count(*) zamiast count(id). Lepiej pasuje twoje pytanie ("liczba wierszy"). Na ogół jest nieco szybszy niż count(id). I chociaż możemy założyć, że id jest NOT NULL, nie zostało to określone w pytaniu, więc count(id) jest błędne , ściśle mówiąc, ponieważ wartości NULL nie są liczone z count(id).

• Nie możesz GROUP BY minut na tym samym poziomie zapytania. Funkcje zbiorcze są stosowane przed funkcje okna, funkcja okna count(*) będzie widzieć tylko 1 wiersz na minutę to sposób.
  Można jednak SELECT DISTINCT, ponieważ {[27] } jest stosowane po funkcje okna.

• ORDER BY 1 to tylko skrót od ORDER BY date_trunc('minute', "when") tutaj.
  1 jest odniesieniem pozycyjnym do pierwszego wyrażenia z listy SELECT.

• Użycie to_char() jeśli chcesz sformatować wynik. Lubię:

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

Najszybszy

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;

Podobnie jak wyżej, ale:

• Używam subquery do agreguj i licz wiersze na minutę. W ten sposób otrzymujemy 1 wiersz na minutę bez DISTINCT w zewnętrznym SELECT.

• Użyj sum() jako funkcji agregującej okno, aby dodać liczniki z zapytania podrzędnego.

Okazało się, że jest to znacznie szybsze z wieloma rzędami na minutę.

Dołącz minuty bez aktywności

Najkrótsza

@GabiMe zapytała w komentarzu jak zdobyć eOne row dla co minute W ramach czasowych, łącznie z tymi, w których nie wystąpiło zdarzenie (brak wiersza w tabeli bazowej):

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;

• Generuje wiersz dla każdej minuty w przedziale czasowym między pierwszym i ostatnim wydarzeniem z generate_series() - tutaj bezpośrednio na podstawie zagregowanych wartości z zapytania podrzędnego.

• LEFT JOIN do wszystkich znaczników czasu obciętych do minuty i liczą. NULL wartości (gdzie nie ma wiersza) nie dodają się do bieżącej liczby.

Najszybszy

Z 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;

• Ponownie, agreguj i licz wiersze na minutę w pierwszym kroku, pomija potrzebę późniejszego DISTINCT.

• Różni się od count(), sum() can return NULL. Domyślnie 0 Z COALESCE.

Z wieloma wierszami i indeksem na "when" Ta wersja z subquery była najszybsza wśród kilku wariantów, które testowałem z 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;