如何在 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;</p>
<p></p>
<h3>PL带游标的 /pgSQL 函数（Postgres 9.1 和较新)</h3></h3>
<p>为了获得最佳性能，特别是在动态时间范围的场景中，可以使用 PL/pgSQL 函数结合游标：</p>
<pre class="brush:php;toolbar:false">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中文网其他相关文章！