PostgreSQL: Anzahl der Zeilen für eine Abfrage "pro Minute"

Nur zurück, Minuten mit Aktivität

Kürzeste

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

• Verwenden date_trunc()es gibt genau das, was Sie brauchen.

• Nicht id in der Abfrage, da will man GROUP BY - minute-Scheiben.

• count() wird normalerweise verwendet, als nur Aggregatfunktion. Anhängen einer OVER Klausel macht es einen Fensterfunktion. Weglassen PARTITION BY im Fenster definition - Sie wollen eine laufende Zählung über alle Zeilen. Standardmäßig zählt von der ersten Zeile an die Letzte peer von der aktuellen Zeile, definiert durch ORDER BY. Ich zitiere das Handbuch:

  Den Standard-framing option ist RANGE UNBOUNDED PRECEDINGdie die
  gleiche wie RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Mit ORDER BY,
  dieses setzt den Rahmen um alle Zeilen aus der partition starten
  durch die aktuelle Zeile die Letzte ORDER BY peer.

  - Und das passiert zur genauwas Sie brauchen.

• Verwenden count(*) eher als count(id). Besser passt deine Frage ("Anzahl der Zeilen"). Es ist im Allgemeinen etwas schneller als count(id). Und während wir annehmen, daß id ist NOT NULL es wurde nicht angegeben in Frage, und so count(id) ist falschstreng genommen, da NULL-Werte nicht gezählt, mit count(id).

• Können Sie nicht GROUP BY - minute-Scheiben an der gleichen query-Ebene. Aggregatfunktionen angewendet werden vor Fenster Funktionen der Fenster-Funktion count(*) nur 1 Zeile pro minute auf diese Weise.
  
  Sie können jedoch SELECT DISTINCT, weil DISTINCT angewendet wird nach Fenster-Funktionen.

• ORDER BY 1 ist nur ein Kürzel für ORDER BY date_trunc('minute', "when") hier.
  
  1 ist ein positions-Referenz an den 1. Ausdruck in der SELECT Liste.

• Verwenden to_char() wenn Sie brauchen, um format das Ergebnis. Wie:

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

Schnellste

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;

Ähnlich wie die oben genannten, aber:

• Ich eine Unterabfrage verwenden, zu aggregieren und zählen der Zeilen pro minute. Auf diese Weise erhalten wir 1 Zeile pro minute ohne DISTINCT im äußeren SELECT.

• Verwenden sum() als window-Aggregat-Funktion hinzufügen, bis die Grafen von der Unterabfrage.

Ich fand, dass dies wesentlich schneller mit vielen Zeilen pro minute.

Gehören Minuten ohne Aktivität

Kürzeste

@GabiMe fragte in einem Kommentar , wie man eone Zeile für jeder minute im Zeitrahmen, einschließlich derjenigen, in denen kein Ereignis stattgefunden hat (keine Zeile in der Basistabelle):

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;

• Erzeugen, die eine Zeile für jede minute in der Zeitspanne zwischen der ersten und der letzten Veranstaltung mit generate_series() - hier direkt auf Basis der aggregierten Werte aus der Unterabfrage.

• LEFT JOIN alle Zeitstempel abgeschnitten, um die Minuten und zählen. NULL Werte (wo es keine Zeile vorhanden ist) nicht hinzufügen, um die laufende Zählung.

Schnellste

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

• Wieder, zu aggregieren und zählen der Zeilen pro minute im ersten Schritt, es wird die Notwendigkeit für später DISTINCT.

• Anders aus count()sum() zurückkehren können NULL. Standardmäßig 0 mit COALESCE.

Mit vielen Zeilen und einer index auf "when" diese version mit einer Unterabfrage war Sie die Schnellste unter ein paar Varianten getestet habe ich mit 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;

InformationsquelleAutor der Antwort Erwin Brandstetter