如何在 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，其他方法是推荐：

罗马查询（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;

数组计数（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中文网其他相关文章！