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

상관 하위 쿼리 접근 방식

상관 하위 쿼리 방법은 뛰어난 성능을 제공합니다.

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 및 최신)

특히 동적 시간 범위가 있는 시나리오에서 최적의 성능을 위해 커서와 결합된 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!