Aggrégation des données de séries temporelles: intervalles de 5 minutes dans sql

L'analyse des données de séries chronologiques nécessite souvent du regroupement des données à des intervalles spécifiques. Ce guide montre comment regrouper les données à des intervalles de 5 minutes à l'aide de SQL, en abordant un scénario où les données doivent être agrégées dans un délai défini. L'exemple utilise des données des tableaux «Time» et «ID», en comptant les occurrences du nom «John». Le défi consiste à passer du regroupement d'horodatage individuel à une agrégation d'intervalle de 5 minutes.

Solutions pour différents systèmes de base de données

L'approche optimale varie en fonction de votre système de base de données. Voici des solutions pour PostgreSQL et MySQL:

postgresql

PostgreSQL offre une approche flexible en utilisant extrait ('epoch') pour obtenir l'horodatage unix (secondes depuis epoch) et interval :

SELECT
    date_trunc('minute', timestamp)   INTERVAL '5 minutes' * (extract(minute from timestamp)::int / 5) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

Cette requête tronque d'abord l'horodatage à la minute en utilisant date_trunc . Ensuite, il calcule l'intervalle de 5 minutes en ajoutant des multiples de 5 minutes en fonction de la minute de l'horodatage d'origine.

mysql

mysql fournit une solution plus simple en utilisant unix_timestamp () et une division entière:

SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 300) * 300) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

Cette requête convertit l'horodatage en un horodat UNIX, effectue une division entière de 300 (secondes en 5 minutes), puis convertit le résultat en un horodatage en utilisant de_unixtime () .

Les deux requêtes regroupent les résultats par l'intervalle calculé de 5 minutes et le nom, fournissant la sortie agrégée souhaitée. N'oubliez pas de remplacer ... par votre clause spécifique Where . La clause Ordre par assure une présentation chronologique des résultats.