Dados da série de tempo agregados: intervalos de 5 minutos em SQL

Analisando dados de séries temporais geralmente requer agrupamento de dados em intervalos específicos. Este guia demonstra como agrupar dados em intervalos de 5 minutos usando o SQL, abordando um cenário em que os dados precisam ser agregados dentro de um prazo definido. O exemplo usa dados das tabelas 'Time' e 'ID', contando ocorrências do nome 'John'. O desafio está em passar do agrupamento individual de registro de data e hora para a agregação de intervalo de 5 minutos.

soluções para diferentes sistemas de banco de dados

A abordagem ideal varia dependendo do seu sistema de banco de dados. Aqui estão as soluções para PostgreSQL e MySQL:

postGresql

PostGresql oferece uma abordagem flexível usando extraCT ('epoch') para obter o timestamp UNIX (segundos desde a época) e 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 primeiro trunca o registro de data e hora do minuto usando date_trunc . Em seguida, ele calcula o intervalo de 5 minutos adicionando múltiplos de 5 minutos com base no minuto do registro de data e hora original.

mysql

MySQL fornece uma solução mais simples usando unix_timestamp () e divisão inteira:

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 converte o registro de data e hora em um registro de data e hora do Unix, executa a divisão inteira por 300 (segundos em 5 minutos) e depois converte o resultado de volta em um registro de data e hora usando de_unixtime () .

Ambas as consultas agrupam os resultados pelo intervalo de 5 minutos calculado e pelo nome, fornecendo a saída agregada desejada. Lembre -se de substituir ... com seu específico onde cláusula. A cláusula por garante a apresentação cronológica dos resultados.