Comment compter les lignes précédentes dans une plage de temps dans PostgreSQL ?

Compter les lignes précédentes dans une plage de temps

Dans PostgreSQL, vous pouvez déterminer le nombre total d'enregistrements précédents dans une plage de temps donnée pour chaque ligne à l'aide des fonctions de fenêtre.

Utilisation de RANGE dans les fonctions de fenêtre (Postgres 11 ou plus récent)

Pour Postgres 11 ou version ultérieure, le mode RANGE vous permet de spécifier la plage horaire à l'aide des options PRECEDING et EXCLUDE :

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

Anciennes versions de PostgreSQL

Pour les versions antérieures de PostgreSQL, d'autres approches sont recommandé :

Requête de Roman (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;

Comptage de tableaux (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;

Fonction 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$;

Appelez la fonction avec l'intervalle de temps souhaité :

SELECT * FROM running_window_ct('1 hour');

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!