Bagaimanakah Saya Boleh Mengira Baris Sebelumnya Dengan Cekap Dalam Julat Masa dalam PostgreSQL?

Kira Baris Sebelumnya Dalam Julat

Banyak aplikasi dunia nyata memerlukan penentuan kiraan rekod sebelumnya dalam julat masa tertentu. Artikel ini meneroka pendekatan yang berbeza untuk mencapai perkara ini dalam PostgreSQL, terutamanya apabila berurusan dengan set data yang besar dan julat masa dinamik.

Pendekatan Fungsi Tetingkap (Postgres 11 )

Dalam Postgres 11 dan lebih baru, pilihan pembingkaian RANGE bagi fungsi tetingkap membolehkan a penyelesaian mudah:

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

CTE, Agregasi Tatasusunan dan Pengiraan (Postgres 10 dan Lebih Lama)

Walaupun had prestasinya, penyelesaian berasaskan CTE Roman kekal sebagai pilihan:

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;

Pendekatan Subkueri Berkaitan

Kaedah subkueri berkorelasi menawarkan prestasi unggul:

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;

/pgSQL Fungsi dengan Kursor (Postgres 9.1 dan Lebih baharu)

Untuk prestasi optimum, terutamanya dalam senario dengan julat masa dinamik, fungsi PL/pgSQL yang digabungkan dengan kursor boleh digunakan:

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

Tanda aras Keputusan

Menanda aras pendekatan ini pada set data sebanyak 100,000 baris menunjukkan keunggulan fungsi PL/pgSQL untuk kebolehskalaan dan prestasi:

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

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