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

Comptage des lignes précédentes dans une plage

Énoncé du problème :

Déterminer le nombre total d'enregistrements précédents dans un délai défini plage pour chaque ligne d'un tableau.

Spécifique Scénario :

Requête :

SELECT id, date
     , count(*) OVER (HAVING previous_rows.date >= (date - '1 hour'::interval))  -- ?
FROM test;

Tableau :

CREATE TABLE test (
  id  bigint
, ts  timestamp
);

Postgres 11 ou version ultérieure :

Postgres 11 a introduit des options améliorées de cadrage des fonctions de fenêtre, permettant l'utilisation du mode RANGE avec PRECEDING et SUIVANT pour sélectionner les lignes dans un décalage spécifié.

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

Postgres 10 ou version antérieure :

ROM (requête de Roman) :

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 (tableau de comptage éléments):

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;

COR (sous-requête corrélée):

CREATE OR REPLACE FUNCTION running_window_ct(_intv interval = '1 hour')
  RETURNS TABLE (id bigint, ts timestamp, ct int)
  LANGUAGE plpgsql 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;  -- init

   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:

SELECT * FROM running_window_ct();

Résultats du benchmark :

Un benchmark utilisant un Un tableau avec un nombre variable de lignes a montré que la fonction FNC est clairement la gagnante en termes de performances et d'évolutivité.

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!