Comment compter efficacement les lignes par intervalles de temps en SQL ?

Comptage des lignes par intervalles de temps : dévoilement de la meilleure approche

Les applications traitent souvent des événements horodatés et nécessitent d'analyser les données en fonction d'une heure spécifique intervalles. Pour compter efficacement le nombre d'événements dans chaque intervalle, les développeurs peuvent réfléchir à différentes approches.

1. Requête SQL dynamique pour des intervalles arbitraires

L'utilisation d'une requête SQL pour regrouper des événements par intervalles de temps arbitraires est une méthode efficace et privilégiée. Cependant, Postgres ne prend pas en charge nativement cette fonctionnalité.

2. Méthode Brute Force avec tri temporel

Interroger tous les enregistrements dans une période de début/fin et les compter manuellement selon les intervalles souhaités est une approche simple mais lourde. Il nécessite de trier les résultats par horodatage et manque de personnalisation des intervalles dynamiques.

3. Pré-stockage des données d'intervalle

Le maintien de champs séparés pour chaque intervalle dans la table des événements peut éviter les calculs d'exécution et simplifier les rapports. Cependant, cette méthode double la taille de la table, ce qui n'est peut-être pas optimal.

Bonne pratique

La meilleure pratique dépend des exigences spécifiques de l'application et de la taille des données :

• Pour un nombre important d'intervalles ou une personnalisation d'intervalles arbitraires, une requête SQL dynamique convient. Bien que Postgres ne dispose pas d'une prise en charge intégrée, des solutions de contournement existent en utilisant des fonctions de fenêtre telles que lead().
• Pour un petit nombre d'intervalles fixes (par exemple, jours ou semaines), pré-stockage des données d'intervalle peut simplifier l'exécution des requêtes mais augmente la taille de la table.
• Pour une solution en temps réel ou lorsque la croissance de la table est un problème, la force brute La méthode avec tri temporel peut être plus appropriée.

Postgres 14 avec la fonction date_bin()

Pour Postgres 14 et plus récent, la fonction date_bin() permet d'effectuer directement regroupement des horodatages en intervalles. Cela simplifie les requêtes et fournit des résultats efficaces.

Exemple : Comptage des événements dans chaque intervalle de 15 minutes

Pour compter les événements dans chaque intervalle de 15 minutes dans une plage de temps donnée en utilisant date_bin() function :

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;

Remarque :

Pour Postgres 13 et versions antérieures, des approches alternatives utilisant generate_series() et des fonctions de fenêtre sont disponibles.

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!