Comment compter efficacement les événements dans des intervalles de temps dynamiques dans PostgreSQL ?

Comment compter efficacement les événements par intervalles de temps

Introduction

Les applications nécessitent souvent d'extraire des informations à partir de données basées sur des événements, telles que le comptage d'événements dans des limites spécifiques intervalles de temps. Cette tâche pose un défi lorsque la taille de l'intervalle varie de manière dynamique.

Énoncé du problème

Une table de données avec des événements horodatés nécessite une requête SQL dynamique qui regroupe les événements par intervalles de temps arbitraires, notamment hebdomadaires, quotidiens, toutes les heures, voire toutes les 15 minutes.

Solution

Postgres 14 ou Plus récent

Pour les versions 14 et 3 de Postgres, la fonction date_bin() fournit une solution simple. Pour récupérer une ligne par plage horaire avec des données, utilisez la requête suivante :

SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time
     , count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1;

Pour obtenir toutes les plages horaires depuis une date spécifique, ajustez la requête comme suit :

SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1;

Postgres 13 ou plus ancien

Pour les anciennes versions de Postgres, la requête suivante peut être utilisée :

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

Cette requête fournit une ligne pour chaque créneau horaire, avec un décompte de 0 pour les créneaux sans événements.

Optimisation

Le pré-stockage des données d'intervalle dans la table des événements peut améliorer les performances. Cependant, cette approche double la taille de la table. Si le compromis est acceptable, il peut fournir une augmentation significative de la vitesse.

Considérations supplémentaires

• Gérez correctement les limites de temps supérieures et inférieures.
• Utilisez le to_char () pour formater les horodatages à des fins d'affichage.
• Utilisez la fonction generate_series() pour générer un ensemble complet d'heures machines à sous.

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!