Comment calculer efficacement le nombre de lignes en cours par minute dans PostgreSQL ?

Méthode de calcul efficace du nombre de lignes par minute dans PostgreSQL

Obtenir le nombre de lignes

<code class="language-sql">SELECT COUNT(id) AS count,
       EXTRACT(hour FROM "when") AS hour,
       EXTRACT(minute FROM "when") AS minute
FROM mytable
GROUP BY hour, minute;</code>

Cette requête compte les lignes par minute mais ne fournit pas de total cumulé.

Obtenez le décompte

Méthode 1 : renvoyer uniquement les minutes avec des enregistrements d'activité

<code class="language-sql">SELECT DISTINCT
       date_trunc('minute', "when") AS minute,
       count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;</code>

Cette requête utilise la fonction date_trunc() pour renvoyer le nombre de lignes pour chaque minute active. Il calcule le nombre total d'exécutions à l'aide d'une fonction de fenêtre avec une clause ORDER BY.

Contient des minutes sans activité

Méthode 2 : Utiliser une sous-requête avec jointure

<code class="language-sql">SELECT minute, sum(minute_ct) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT date_trunc('minute', "when") AS minute,
          count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) sub
ORDER  BY 1;</code>

Cette méthode regroupe le nombre de lignes par minute dans une sous-requête. La requête principale la rejoint ensuite pour accumuler le décompte et inclure les minutes sans activité.

Méthode 3 : Utiliser le CTE (le plus rapide)

<code class="language-sql">WITH cte AS (
   SELECT date_trunc('minute', "when") AS minute, count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) 
SELECT m.minute,
       COALESCE(sum(cte.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM  (
   SELECT generate_series(min(minute), max(minute), interval '1 min')
   FROM   cte
   ) m(minute)
LEFT   JOIN cte USING (minute)
ORDER  BY 1;</code>

Cette méthode combine CTE, sous-requête et jointure gauche. Il s'agit d'une approche efficace pour les grands ensembles de données avec des index « quand ».

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!