How to Count Previous Rows Within a Time Range in PostgreSQL?

Count Previous Rows within a Time Range

In PostgreSQL, you can determine the total number of preceding records within a given time range for each row using window functions.

Using RANGE in window functions (Postgres 11 or newer)

For Postgres 11 or later, the RANGE mode allows you to specify the time range using PRECEDING and EXCLUDE options:

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

Older PostgreSQL Versions

For earlier versions of PostgreSQL, other approaches are recommended:

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

Call the function with the desired time interval:

SELECT * FROM running_window_ct('1 hour');

The above is the detailed content of How to Count Previous Rows Within a Time Range in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!