Bagaimana Mengira Baris Sebelumnya Dalam Julat Masa dalam PostgreSQL?

Kira Baris Sebelumnya dalam Julat Masa

Dalam PostgreSQL, anda boleh menentukan jumlah bilangan rekod sebelumnya dalam julat masa tertentu untuk setiap baris menggunakan fungsi tetingkap.

Menggunakan RANGE dalam fungsi tetingkap (Postgres 11 atau lebih baharu)

Untuk Postgres 11 atau lebih baru, mod RANGE membolehkan anda menentukan julat masa menggunakan pilihan SEBELUM DAN KECUALI:

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

Versi PostgreSQL Lama

Untuk versi PostgreSQL yang lebih awal, pendekatan lain ialah disyorkan:

Pertanyaan Rom (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;

Pengiraan Tatasusunan (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;

Fungsi PL/pgSQL (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$;

Panggil fungsi dengan selang masa yang dikehendaki:

SELECT * FROM running_window_ct('1 hour');

Atas ialah kandungan terperinci Bagaimana Mengira Baris Sebelumnya Dalam Julat Masa dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!