Группировка записей по дням или часам

22.08.2012

В преддверии ММАС (Московского международного Автомобильного Салона) один из наших клиентов заказал рассылку на более чем 40 тысяч контаков, и по окончании которой, мне захотелось посмотреть скорость рассылки за всё время. Т.е. сколько писем уходило каждый час и представить это в виде графика.

Немного теории

Конечно представление данных в виде графика, это самая простая вещь, которая могла понадобиться. А вот группировку рассмотрим поподробнее.

Если представить всё более абстрактно, то у нас есть таблица `CommOut`, в которой есть поле с указанным временем отправки писем. Время конечно же 0храниться в формате datetime и имеет следующий вид: "гггг-мм-дд чч:мм:сс" (например 2012-08-22 21:58:46).

Теперь нам надо всего лишь отбросить минуты и секунды, сгруппировать данные по получившейся дате, и применить  необходимую агрегатную функцию (в моём случае это будет COUNT).

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

Сгруппируем это

Не буду рассусоливать, а сразу перейду к запросу:

SELECT COUNT(`id`), DATE_FORMAT(`send_date`, '%Y %m %d %H') as dat  FROM `CommOut` GROUP BY dat ORDER BY dat DESC
Как видите всё очень даже просто.

Здесь функция DATE_FORMAT конвертирует дату из поля `send_date` в следующий вид: гггг мм дд чч. Т.е. из 2012-08-22 21:58:46 мы получим 2012 08 22 21. А это, то что нам и надо. Далее обзываем получившееся поле именем dat, группируем по нему (GROUP BY dat) и сортируем в порядке убывания (ORDER BY dat DESC).

А вызовом COUNT(`id`) я получаю количество записей в группе, т.е. количество отправленных писем за данный час. А заменив параметр '%Y %m %d %H' на более простой '%H', мы сможем уже наблюдать статистику не за весь период, а конкретно по каждому часу, т.е. записи не будут разбиваться на дни/месяца/года, а только на часы.