Wie zähle ich vorherige Zeilen innerhalb eines Zeitbereichs in PostgreSQL?

Vorherige Zeilen innerhalb eines Zeitbereichs zählen

In PostgreSQL können Sie für jeden die Gesamtzahl der vorhergehenden Datensätze innerhalb eines bestimmten Zeitbereichs ermitteln Zeile mit Fensterfunktionen.

Verwendung von RANGE in Fensterfunktionen (Postgres 11 oder neuer)

Für Postgres 11 oder höher können Sie im RANGE-Modus den Zeitbereich mithilfe der Optionen PRECEDING und EXCLUDE angeben:

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

Ältere PostgreSQL-Versionen

Für frühere Versionen von PostgreSQL gibt es andere Ansätze empfohlen:

Roman's Query (ROM)

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;

Array Counting (ARR)

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 (FNC)

CREATE OR REPLACE FUNCTION running_window_ct(_intv interval = '1 hour')
RETURNS TABLE (id bigint, ts timestamp, ct int) 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;

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 mit dem gewünschten Zeitintervall aufrufen:

SELECT * FROM running_window_ct('1 hour');

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