Wie kann ich vorherige Zeilen innerhalb eines Zeitbereichs in PostgreSQL effizient zählen?

Vorherige Zeilen innerhalb des Bereichs zählen

Viele reale Anwendungen erfordern die Bestimmung der Anzahl vorhergehender Datensätze innerhalb eines bestimmten Zeitbereichs. In diesem Artikel werden verschiedene Ansätze untersucht, um dies in PostgreSQL zu erreichen, insbesondere beim Umgang mit großen Datensätzen und dynamischen Zeitbereichen.

Fensterfunktionsansatz (Postgres 11)

In Postgres 11 und höher ermöglicht die RANGE-Framing-Option der Fensterfunktionen eine einfache Lösung:

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

CTE, Array-Aggregation und Zählung (Postgres 10 und älter)

Trotz ihrer Leistungseinschränkungen bleibt die CTE-basierte Lösung von Roman bestehen eine Option:

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;

Korrelierte Unterabfrage Ansatz

Die korrelierte Unterabfragemethode bietet überlegene Leistung:

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;

PL/pgSQL-Funktion mit Cursor (Postgres 9.1 und neuer)

Für optimale Leistung, insbesondere in Szenarien mit dynamischen Zeitbereichen, ein PL/pgSQL Funktion in Kombination mit einem Cursor kann verwendet werden:

CREATE FUNCTION running_window_ct(_intv interval = '1 hour')
RETURNS TABLE (id bigint, ts timestamp, ct int)
LANGUAGE plpgsql AS
$func$
.....
$func$;

SELECT * FROM running_window_ct();

Benchmark-Ergebnisse

Das Benchmarking dieser Ansätze an einem Datensatz mit 100.000 Zeilen zeigt die Überlegenheit von die PL/pgSQL-Funktion für Skalierbarkeit und Leistung:

100 rows:
ROM: 27.656 ms
ARR: 7.834 ms
COR: 5.488 ms
FNC: 1.115 ms

1000 rows:
ROM: 2116.029 ms
ARR: 189.679 ms
COR: 65.802 ms
FNC: 8.466 ms

100000 rows:
ROM: DNF
ARR: DNF
COR: 6760 ms
FNC: 828 ms

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