PostgreSQL: exécution du nombre de lignes pour une requête 'par minute'

Seulement revenir quelques minutes avec l'activité

le plus court

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

• utilisation date_trunc() , il rend exactement ce dont vous avez besoin.

• Ne comprennent pas id dans la requête, puisque vous voulez GROUP BY minute tranches.

• count() est généralement utilisé comme simple fonction agrégée . Ajouter une clause OVER en fait une fonction de fenêtre . Omettez PARTITION BY dans la définition de la fenêtre - vous voulez un compte courant sur toutes les lignes . Par défaut, cela compte de la première rangée à la dernière paire de la rangée courante telle que définie par ORDER BY . je cite le manuel :

  l'option de cadrage par défaut est RANGE UNBOUNDED PRECEDING , qui est le identique à RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . Avec ORDER BY , ceci définit le cadre à toutes les lignes de la partition de démarrage à travers la dernière ligne ORDER BY .

  et qui se trouve être exactement ce dont vous avez besoin.

• utiliser count(*) plutôt que count(id) . Il s'adapte à votre question ("nombre de lignes"). Il est généralement légèrement plus rapide que count(id) . Et, alors que nous pourrions supposer que id est NOT NULL , il n'a pas été spécifié dans la question, donc count(id) est erroné , à strictement parler, parce que les valeurs nulles ne sont pas comptées avec count(id) .

• vous ne pouvez pas GROUP BY tranches minute au même niveau de requête. Les fonctions agrégées sont appliquées avant les fonctions de fenêtre, la fonction de fenêtre count(*) ne verrait qu'une rangée par minute de cette façon.
  
  Vous pouvez, cependant, SELECT DISTINCT , parce que DISTINCT est appliqué après fonctions de fenêtre.

• ORDER BY 1 est juste un raccourci pour ORDER BY date_trunc('minute', "when") ici.
  
  1 est une référence de position à la première expression de la liste SELECT .

• utiliser to_char() si vous devez formater le résultat. Comme:

SELECT DISTINCT
       to_char(date_trunc('minute', "when"), 'DD.MM.YYYY HH24:MI') AS minute
     , count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY date_trunc('minute', "when");

le plus rapide

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;

un peu comme ci-dessus, mais:

• j'utilise un sous-jeu pour agréger et compter les lignes par minute. De cette façon, nous obtenons 1 rangée par minute sans DISTINCT dans le SELECT extérieur .

• utilisez sum() comme fonction agrégée de fenêtre maintenant pour additionner les comptes de la sous-série.

j'ai trouvé que c'était beaucoup plus rapide avec beaucoup de rangées par minute.

Inclure minutes sans activité

le plus court

@GabiMe a demandé dans un commentaire comment obtenir eOne row pour every minute dans le temps, y compris ceux où aucun événement ne s'est produit (aucune ligne dans le tableau de base):

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   tbl
   ) m(minute)
LEFT   JOIN (SELECT date_trunc('minute', "when") FROM tbl) c(minute) USING (minute)
ORDER  BY 1;

• generate_series() - ici directement basé sur les valeurs agrégées de la sous-série.

• LEFT JOIN à tous les horodateurs tronqués à la minute et au compte. Les valeurs NULL (lorsqu'il n'y a pas de ligne) ne s'additionnent pas au compte courant.

le plus rapide

avec CTE:

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;

• encore une fois, additionnez et comptez les lignes par minute dans la première étape, il omet la nécessité de plus tard DISTINCT .

• Différent de count() , sum() peut renvoyer NULL . Par défaut 0 avec COALESCE .

avec de nombreuses lignes et un index sur "when" cette version avec un sous-query était le plus rapide parmi un couple de variantes que j'ai testé avec Postgres 9.1-9.4:

SELECT m.minute
     , COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM  (
   SELECT generate_series(date_trunc('minute', min("when"))
                        ,                      max("when")
                        , interval '1 min')
   FROM   tbl
   ) m(minute)
LEFT   JOIN (
   SELECT date_trunc('minute', "when") AS minute
        , count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) c USING (minute)
ORDER  BY 1;