агрегирующие данные временных рядов: 5-минутные интервалы в SQL

анализ данных временных рядов часто требует группировки данных в определенные интервалы. Это руководство демонстрирует, как группировать данные в 5-минутные интервалы с использованием SQL, обращаясь к сценарию, в котором данные должны быть агрегированы в определенные сроки. В примере используются данные из таблиц «Время» и «Идентификатор», подсчитывая события имени «Джона». Задача заключается в переходе от индивидуальной группировки временной метки к 5-минутной интервальной агрегации.

решения для различных систем баз данных

Оптимальный подход варьируется в зависимости от вашей системы баз данных. Вот решения для PostgreSQL и MySQL:

postgresql

postgresql предлагает гибкий подход с использованием Extract ('epoch') , чтобы получить Unix TimeStamp (секунды с эпохи) и интервал :

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;

этот запрос первым усекает метку времени до минуты, используя date_trunc . Затем он вычисляет 5-минутный интервал, добавляя множество 5 минут на основе минуты исходной метки времени.

mysql

mysql предоставляет более простое решение с использованием unix_timestamp () и целочисленного деления:

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;

Этот запрос преобразует TimeStamp в UNIX TimeStamp, выполняет целочисленное разделение на 300 (секунды за 5 минут), а затем преобразует результат обратно в TimeStamp с использованием from_unixtime () .

оба запроса группируют результаты по расчетному 5-минутному интервалу и имени, обеспечивая желаемый агрегированный выход. Не забудьте заменить ... с вашим конкретным , где пункт. Порядок порядок обеспечивает хронологическое представление результатов.