Comment obtenir un nombre de lignes en cours par minute dans PostgreSQL ?

PostgreSQL : Calculer un nombre de lignes en cours par minute

Cet article montre comment obtenir un nombre de lignes en cours pour une requête, regroupées par minute dans PostgreSQL. Le défi est de compter avec précision les lignes pour chaque minute, même celles sans activité.

Exploiter les fonctions de la fenêtre

La solution la plus efficace utilise les puissantes fonctions de fenêtre de PostgreSQL. Ces fonctions fonctionnent sur des ensembles de lignes, permettant des calculs sur plusieurs lignes. Ici, on utilise la fonction COUNT au sein d'une fenêtre partitionnée par minute.

Requête efficace utilisant SELECT DISTINCT et ORDER BY

Cette requête utilise SELECT DISTINCT pour garantir des entrées de minutes uniques et COUNT avec une fonction de fenêtre pour générer le décompte en cours. ORDER BY garantit la somme cumulée correcte pour chaque intervalle d'une minute.

SELECT DISTINCT
       date_trunc('minute', "when") AS minute,
       count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;

Comprendre la syntaxe des fonctions de fenêtre

Décomposons la fonction de fenêtre :

• COUNT(*) : compte les lignes dans chaque partition d'une minute.
• OVER (ORDER BY date_trunc('minute', "when")) : Définit la fenêtre comme une séquence de lignes, classées par minute, accumulant le décompte depuis le début.

Gérer les minutes avec zéro activité

Pour inclure les minutes sans activité (zéro ligne), nous utilisons generate_series pour créer une série de minutes, puis effectuons un LEFT JOIN pour inclure ces minutes même si elles manquent d'entrées correspondantes dans le tableau :

SELECT DISTINCT
       minute, count(c.minute) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT generate_series(date_trunc('minute', min("when")), max("when"), interval '1 min')
   FROM   mytable
   ) m(minute)
LEFT   JOIN (SELECT date_trunc('minute', "when") AS minute FROM mytable) c USING (minute)
ORDER  BY 1;

Considérations relatives aux performances

Alors que les fonctions de fenêtre sont généralement optimales pour les grands ensembles de données, pour les ensembles de données plus petits, une alternative utilisant SUM() pourrait offrir de meilleures performances. Cette approche regroupe et compte d'abord les lignes par minute, puis utilise SUM() pour calculer le total cumulé :

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;

La meilleure approche dépend de la taille de votre ensemble de données et de vos exigences de performances. Il est recommandé de tester les deux méthodes pour déterminer la solution la plus efficace pour votre cas d'utilisation spécifique.

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!