agregando datos de series de tiempo: intervalos de 5 minutos en sql

Analizar datos de series de tiempo a menudo requiere agrupar datos en intervalos específicos. Esta guía demuestra cómo agrupar los datos en intervalos de 5 minutos utilizando SQL, abordando un escenario en el que los datos deben agregarse dentro de un plazo definido. El ejemplo usa datos de las tablas 'Time' e 'ID', contando ocurrencias del nombre 'John'. El desafío radica en pasar de la agrupación de la marca de tiempo individual a la agregación de intervalos de 5 minutos.

soluciones para diferentes sistemas de base de datos

El enfoque óptimo varía según el sistema de su base de datos. Aquí hay soluciones para PostgreSQL y mySQL:

postgresql

PostgreSQL ofrece un enfoque flexible usando extracto ('Epoch') para obtener la marca de tiempo UNIX (segundos desde la época) y 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;

Esta consulta primero trunca la marca de tiempo al minuto usando date_trunc . Luego, calcula el intervalo de 5 minutos agregando múltiplos de 5 minutos en función del minuto de la marca de tiempo original.

mysql

mysql proporciona una solución más simple usando unix_timestamp () y la división Integer:

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;

Esta consulta convierte la marca de tiempo en una marca de tiempo UNIX, realiza la división de enteros por 300 (segundos en 5 minutos) y luego convierte el resultado en una marca de tiempo usando from_unixtime () .

ambas consultas agrupan los resultados mediante el intervalo calculado de 5 minutos y el nombre, proporcionando la salida agregada deseada. Recuerde reemplazar ... con su cláusula específica donde . La cláusula por garantiza la presentación cronológica de los resultados.