PostgreSQL で時間範囲内の先行行を効率的にカウントするにはどうすればよいですか?

範囲内の前の行のカウント

問題文:

定義された時間内の先行レコードの合計数を決定するテーブル内の各行の範囲。

特定シナリオ:

クエリ:

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

テーブル:

CREATE TABLE test (
  id  bigint
, ts  timestamp
);

Postgres 11 以降:

Postgres 11 では、改良されたウィンドウ関数フレーム オプションが導入され、指定されたオフセット内の行を選択するには、PRECEDING および FOLLOWING を指定した RANGE モードを使用します。

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

Postgres 10 以降:

ROM (Roman'sクエリ):

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 (配列要素数):

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 (相関サブクエリ):

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

に電話してください関数:

SELECT * FROM running_window_ct();

ベンチマーク結果:

行数が異なるテーブルを使用したベンチマークは、FNC 関数が次の点で明らかに勝者であることを示しました。パフォーマンスとスケーラビリティ。

以上がPostgreSQL で時間範囲内の先行行を効率的にカウントするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。