Comment puis-je compter efficacement les lignes précédentes dans une plage de temps dans PostgreSQL ?

Compter les lignes précédentes dans la plage

De nombreuses applications du monde réel nécessitent de déterminer le nombre d'enregistrements précédents dans une plage de temps spécifique. Cet article explore différentes approches pour y parvenir dans PostgreSQL, en particulier lorsqu'il s'agit de grands ensembles de données et de plages de temps dynamiques.

Approche des fonctions de fenêtre (Postgres 11)

Dans Postgres 11 et versions ultérieures, l'option de cadrage RANGE des fonctions de fenêtre permet un simple solution :

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

CTE, agrégation de tableaux et comptage (Postgres 10 et versions antérieures)

Malgré ses limitations en termes de performances, la solution basée sur CTE de Roman reste un option :

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;

Approche de sous-requête corrélée

La méthode de sous-requête corrélée offre des performances supérieures :

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 Fonction avec Curseur (Postgres 9.1 et Plus récent)

Pour des performances optimales, notamment dans les scénarios avec des plages de temps dynamiques, une fonction PL/pgSQL combinée à un curseur peut être utilisée :

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();

Résultats de référence

Analyser ces approches sur une base un ensemble de données de 100 000 lignes démontre la supériorité de la fonction PL/pgSQL en termes d'évolutivité et de performances :

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

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!