Comment générer un nombre de lignes en cours dans PostgreSQL pour chaque minute, y compris les minutes inactives ?

PostgreSQL : Générer un nombre de lignes en cours par minute, y compris les périodes inactives

Cet article montre comment générer un nombre courant de lignes dans une table PostgreSQL pour chaque minute, même si certaines minutes n'ont aucune activité.

Méthode 1 : compte courant pour les minutes actives uniquement

Cette approche utilise des fonctions de fenêtre pour calculer un décompte pour chaque minute où une activité existe :

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

date_trunc('minute', "when") regroupe les lignes par minute. La fonction count(*) OVER (ORDER BY date_trunc('minute', "when")) fournit un total cumulé classé par minute.

Méthode 2 : inclure les minutes inactives

Pour inclure les minutes sans activité, nous utilisons generate_series pour créer une séquence de minutes complète et un LEFT JOIN pour incorporer les décomptes :

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

Un CTE (cte) regroupe les lignes par minute et les compte. generate_series crée une série de minutes couvrant toute la plage horaire. Le LEFT JOIN combine cette série avec les décomptes de cte, et COALESCE gère les minutes sans activité en attribuant un décompte courant de 0. Le sum() OVER (ORDER BY m.minute) calcule ensuite la somme cumulée.

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!