Wie kann man vorhergehende Zeilen innerhalb eines Zeitbereichs in PostgreSQL effizient zählen?

Vorherige Zeilen innerhalb eines Bereichs zählen

Problemstellung:

Bestimmen Sie die Gesamtzahl der vorhergehenden Datensätze innerhalb einer definierten Zeit Bereich für jede Zeile in einer Tabelle.

Spezifisch Szenario:

Abfrage:

SELECT id, date
     , count(*) OVER (HAVING previous_rows.date >= (date - '1 hour'::interval))  -- ?
FROM test;

Tabelle:

CREATE TABLE test (
  id  bigint
, ts  timestamp
);

Postgres 11 oder neuer:

Mit Postgres 11 wurden verbesserte Rahmenoptionen für Fensterfunktionen eingeführt, die die Verwendung des RANGE-Modus mit PRECEDING und ermöglichen FOLGEN SIE, um Zeilen innerhalb eines angegebenen Offsets auszuwählen.

SELECT id, ts
     , count(*) OVER (ORDER BY ts RANGE '1 hour' PRECEDING EXCLUDE CURRENT ROW)
FROM   test
ORDER  BY ts;

Postgres 10 oder älter:

ROM (Romans Abfrage):

SELECT id, ts
     , (SELECT count(*)::int - 1
        FROM   unnest(dates) x
        WHERE  x >= sub.ts - interval '1h') AS ct
FROM (
   SELECT id, ts
        , array_agg(ts) OVER(ORDER BY ts) AS dates
   FROM   test
   ) sub;

ARR (Anzahlarray Elemente):

SELECT id, ts
     , (SELECT count(*)
        FROM   test t1
        WHERE  t1.ts >= t.ts - interval '1h'
        AND    t1.ts < t.ts) AS ct
FROM   test t
ORDER  BY ts;

COR (korrelierte Unterabfrage):

CREATE OR REPLACE FUNCTION running_window_ct(_intv interval = '1 hour')
  RETURNS TABLE (id bigint, ts timestamp, ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   cur   CURSOR FOR
         SELECT t.ts + _intv AS ts1
              , row_number() OVER (ORDER BY t.ts ROWS UNBOUNDED PRECEDING) AS rn
         FROM   test t
         ORDER  BY t.ts;
   rec   record;
   rn    int;
BEGIN
   OPEN cur;
   FETCH cur INTO rec;
   ct := -1;  -- init

   FOR id, ts, rn IN
      SELECT t.id, t.ts
           , row_number() OVER (ORDER BY t.ts ROWS UNBOUNDED PRECEDING)
      FROM   test t ORDER BY t.ts
   LOOP
      IF rec.ts1 >= ts THEN
         ct := ct + 1;
      ELSE
         LOOP
            FETCH cur INTO rec;
            EXIT WHEN rec.ts1 >= ts;
         END LOOP;
         ct := rn - rec.rn;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;

Funktion aufrufen:

SELECT * FROM running_window_ct();

Benchmark-Ergebnisse:

Ein Benchmark mit Eine Tabelle mit unterschiedlichen Zeilenzahlen zeigte, dass die FNC-Funktion in Bezug auf Leistung und Skalierbarkeit der klare Sieger ist.

Das obige ist der detaillierte Inhalt vonWie kann man vorhergehende Zeilen innerhalb eines Zeitbereichs in PostgreSQL effizient zählen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!