Группировка данных MySQL по интервалам в 15 минут

Быстрый ответ

Для проведения группировки данных по 15-минутным интервалам в MySQL можно использовать следующую конструкцию:

SELECT COUNT(*), 
       DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00') – 
       INTERVAL (MINUTE(created_at) % 15) MINUTE AS time_interval
FROM events
GROUP BY time_interval;

В данном случае для формирования 15-минутных интервалов используется функция DATE_FORMAT, округляющая минуты временной метки в меньшую сторону.

Использование UNIX_TIMESTAMP для чётких интервалов

Если работа с временными метками UNIX предпочтительнее, попробуйте воспользоваться этим методом:

SELECT COUNT(*) AS count,
       FROM_UNIXTIME((FLOOR(UNIX_TIMESTAMP(created_at) / 900) * 900)) AS time_interval
FROM events
GROUP BY time_interval;

Здесь мы преобразуем время в формат UNIX, округляем его до ближайшего 15-минутного интервала, а затем, используя функцию FROM_UNIXTIME, переводим обратно в удобочитаемый формат.

Создание собственных интервалов

Создание пользовательского временного интервала можно реализовать следующим образом:

SELECT ...
       SEC_TO_TIME(TIME_TO_SEC(created_at) – MOD(TIME_TO_SEC(created_at), 900)) AS time_interval,
       ...

Здесь для трансформации и округления времени до 15-минутных интервалов применяются функции SEC_TO_TIME и TIME_TO_SEC.

Повышение точности с использованием ROUND

Если требуется более точное округление, нежели предоставляет FLOOR, можно использовать функцию ROUND.

SELECT ...
       ROUND(UNIX_TIMESTAMP(created_at) / (15 * 60)) AS time_rounded,
       ...

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

Работа с типами данных

Для изгибательных манипуляций с типами данных используйте следующий подход:

SELECT ...
       CONVERT(time_interval, DATETIME) AS converted_interval,
       ...

Функция CONVERT позволяет преобразовать временной интервал в нужный тип данных, но нужно учесть возможное влияние на производительность при работе с большим объемом данных.

Точное извлечение с использованием SUBSTRING

Функция SUBSTRING позволяет точно извлечь требуемые части временной метки:

SELECT ...
       SUBSTRING(created_at, 1, 16) – 
       INTERVAL (MINUTE(created_at) % 15) MINUTE AS neat_interval,
       ...

Проверка результата до окончания работы

Важно не забывать проверять точность выполнения группировки:

SELECT ...
       /* ваш расчёт интервала */,
       ...
WHERE created_at BETWEEN '2023-01-01' AND '2023-01-02'

Проверка запросов на конкретном временном промежутке будет полезна для уверенности в правильности логики.

Визуализация

Отправления на графике: 🚂 00:00, 🚂 00:15, 🚂 00:30, 🚂 00:45 и так далее, через каждые 15 минут.

Запросы: 📊 00:05, 🗂 00:20, 📈 00:27, 📉 00:43

Группировка по интервалам имеет следующий вид:

Производительность и точность

Поддерживайте баланс между производительностью и точностью, особенно при работе с постоянно обновляющимися данными.

SELECT /* используйте оптимальное индексирование */,
       /* примените стратегии кэширования */,
       ...

Этот подход позволит поддерживать высокую производительность без потери точности.

Изучайте новое и адаптируйтесь

Обновляйте свои знания, изучая документацию MySQL и уделяя внимание обсуждениям в сообществе, чтобы применять наиболее эффективные методы и функции.

Курируйте свои данные

Относитесь к каждому 15-минутному интервалу как к кураторской компиляции данных, формулируя запросы, которые позволяют классифицировать информацию наиболее эффективным образом.

Полезные материалы

1. MySQL – Grouping into interval of 5 minutes within a time range – Stack Overflow
2. Aggregate Functions – MariaDB Knowledge Base
3. Difference Between GROUP BY and ORDER BY in SQL – GeeksforGeeks