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

Comptage efficace des événements par intervalles de temps

Énoncé du problème

Considérez une table d'événements contenant des événements horodatés. L'objectif est de signaler le nombre d'événements se produisant dans des intervalles de temps spécifiques, tels que des jours, des heures ou des intervalles définis par l'utilisateur. L'objectif est de déterminer l'approche la plus efficace pour générer dynamiquement ces informations via une seule requête SQL dans Postgres.

Solution

Utilisation de Date_bin() (Postgres 14 ou plus récent)

Postgres 14 introduit le 'date_bin()' fonction, fournissant une solution simple à ce problème :

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;

Génération d'un ensemble complet de lignes (Postgres 13 ou version antérieure)

Pour les versions antérieures de Postgres, le la requête suivante génère un ensemble complet de créneaux horaires et effectue un LEFT JOIN pour compter les événements dans chacun intervalle :

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;

Considérations

• Postgres 9.6 ou version ultérieure est requis pour 'generate_series()'.
• Pour les grands ensembles de données, la création d'un champ « Intervalle » distinct dans le tableau et le pré-stockage des valeurs d'intervalle de temps peuvent améliorer les performances, mais nécessitent un stockage supplémentaire espace.
• Lors de la spécification de l'intervalle de temps, utilisez la syntaxe Postgres appropriée, telle que « intervalle '17 min ».
• L'opérateur « ENTRE » peut être utilisé pour filtrer les événements dans un espace spécifique. plage de temps, mais assurez-vous de gérer correctement les limites supérieure et inférieure.
• Utilisez 'to_char()' pour formater la colonne start_time à des fins d'affichage sans affecter la Opération ORDER BY.

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!