如何在 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;

相關子查詢方法

相關子查詢方法提供卓越的效能：

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中文網其他相關文章！