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

時間範囲内の前の行をカウントする

PostgreSQL では、指定された時間範囲内の各行の前のレコードの合計数を決定できます。ウィンドウ関数を使用した行。

ウィンドウ関数での RANGE の使用 (Postgres) 11 以降)

Postgres 11 以降の場合、RANGE モードでは PRECEDING および EXCLUDE オプションを使用して時間範囲を指定できます。

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

古い PostgreSQL バージョン

PostgreSQL の以前のバージョンの場合、その他推奨されるアプローチは次のとおりです。

Roman's Query (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;

Array Counting (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;

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

希望の時間間隔で関数を呼び出します:

SELECT * FROM running_window_ct('1 hour');

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