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

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

実際のアプリケーションの多くは、特定の時間範囲内の先行レコードの数を決定する必要があります。この記事では、特に大規模なデータセットと動的時間範囲を扱う場合に、PostgreSQL でこれを実現するためのさまざまなアプローチについて説明します。

ウィンドウ関数のアプローチ (Postgres 11 )

Postgres 11 以降では、ウィンドウ関数の RANGE フレーミング オプションにより、簡単なソリューション:

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

CTE、配列集約、およびカウント (Postgres 10 以降)

パフォーマンスの制限にもかかわらず、Roman の CTE ベースのソリューションはそのままですのオプション:

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;</h3>
<p><h3>相関サブクエリ手法</h3></p>
<p>相関サブクエリ手法は優れたパフォーマンスを提供します:</p>
<pre class="brush:php;toolbar:false">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 関数 (Postgres 9.1 およびNewer)

特に動的時間範囲を持つシナリオで最適なパフォーマンスを得るには、PL/pgSQL 関数とカーソルを組み合わせて使用​​できます。

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

ベンチマーク結果

ベンチマーク100,000 行のデータセットに対するこれらのアプローチは、スケーラビリティとパフォーマンスの面で PL/pgSQL 関数の優位性を示しています。

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

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