Comment regrouper des données de séries chronologiques en intervalles de 5 minutes à l'aide de SQL ?

Agrégation de données de séries chronologiques : intervalles de 5 minutes en SQL

L'analyse des données de séries chronologiques nécessite souvent de regrouper les données dans des intervalles spécifiques. Ce guide montre comment regrouper les données en intervalles de 5 minutes à l'aide de SQL, abordant un scénario dans lequel les données doivent être agrégées dans un délai défini. L'exemple utilise les données des tables « time » et « id », en comptant les occurrences du nom « John ». Le défi réside dans le passage d'un regroupement d'horodatages individuels à une agrégation par intervalles de 5 minutes.

Solutions pour différents systèmes de bases 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 propose une approche flexible utilisant extract('epoch') pour obtenir l'horodatage Unix (secondes depuis l'époque) et INTERVAL :

<code class="language-sql">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;</code>

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 la division entière :

<code class="language-sql">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;</code>

Cette requête convertit l'horodatage en horodatage Unix, effectue une division entière par 300 (secondes en 5 minutes), puis reconvertit le résultat en horodatage à l'aide de FROM_UNIXTIME().

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

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!