Bagaimana untuk Mengira Baris Sebelumnya Dengan Cekap Dalam Julat Masa dalam PostgreSQL?

Mengira Baris Sebelumnya dalam Julat

Pernyataan Masalah:

Tentukan jumlah kiraan rekod sebelumnya dalam masa yang ditetapkan julat untuk setiap baris dalam jadual.

Khusus Senario:

Pertanyaan:

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

Jadual:

CREATE TABLE test (
  id  bigint
, ts  timestamp
);

Postgres 11 atau Lebih Baru:

Postgres 11 memperkenalkan pilihan pembingkaian fungsi tetingkap yang dipertingkatkan, membenarkan penggunaan mod RANGE dengan MEndahului dan MENGIKUTI untuk memilih baris dalam ofset yang ditentukan.

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

Postgres 10 atau Lebih:

ROM (pertanyaan 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;

ARR (tatasusunan kiraan elemen):

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 (subkueri berkorelasi):

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$;

Panggil fungsi:

SELECT * FROM running_window_ct();

Keputusan Penanda Aras:

Tanda aras menggunakan jadual dengan kiraan baris yang berbeza-beza menunjukkan bahawa fungsi FNC adalah pemenang yang jelas dari segi prestasi dan kebolehskalaan.

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