Wie kann ich die Anzahl laufender Zeilen pro Minute in PostgreSQL effizient berechnen?

Effiziente Berechnungsmethode der Zeilenanzahl pro Minute in PostgreSQL

Zeilenanzahl abrufen

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

Diese Abfrage zählt Zeilen pro Minute, liefert jedoch keine laufende Summe.

Laufende Zählung durchführen

Methode 1: Nur Minuten mit Aktivitätsaufzeichnungen zurückgeben

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

Diese Abfrage verwendet die Funktion date_trunc(), um die Zeilenanzahl für jede aktive Minute zurückzugeben. Es berechnet die Gesamtzahl der Läufe mithilfe einer Fensterfunktion mit einer ORDER BY-Klausel.

Enthält Minuten ohne Aktivität

Methode 2: Unterabfrage mit Join verwenden

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;

Diese Methode aggregiert die Zeilenanzahl pro Minute in einer Unterabfrage. Anschließend wird die Hauptabfrage damit verknüpft, um die Anzahl zu akkumulieren und Minuten ohne Aktivität einzubeziehen.

Methode 3: Verwenden Sie CTE (am schnellsten)

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;

Diese Methode kombiniert CTE, Unterabfrage und Left Join. Dies ist ein effizienter Ansatz für große Datensätze mit „Wann“-Indizes.

Das obige ist der detaillierte Inhalt vonWie kann ich die Anzahl laufender Zeilen pro Minute in PostgreSQL effizient berechnen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!