Итоги по датам

Задача
Вы хотите выводить итоговую информацию для значений даты или времени.

Решение
Используйте инструкцию GROUP BY для разбиения значений времени на диапазоны нужного размера. Часто для извлечения значащих составляющих даты или времени требуется использовать выражения.

Обсуждение
Чтобы разместить записи во временном порядке, используйте инструкцию ORDER BY для сортировки столбца временного типа. Если вместо этого вы хотитевыводить итоговую информацию для записей, группируя их по интервалам времени, необходимо определить, как сопоставить запись интервалу, и использовать для группирования записей инструкцию GROUP BY.

Если временные значения разбиваются на категории естественным образом, можно работать с ними напрямую. Часто так обрабатываются таблицы, в которых составляющие даты или времени представлены в отдельных столбцах. Например, таблица лучших игроков baseball1.com приводит даты рождения, используя разные столбцы для года, месяца и дня. Чтобы узнать, сколько игроков родилось в каждый день года, подсчитайте записи по ка-
лендарной дате, используя значения месяца и дня, но игнорируя год:

mysql> SELECT birthmonth, birthday, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL AND birthday IS NOT NULL
-> GROUP BY birthmonth, birthday;

+-------------+-----------+------------+
| birthmonth | birthday | COUNT(*) |
+-------------+-----------+------------+
| 1 | 1 | 47 |
| 1 | 2 | 40 |
| 1 | 3 | 50 |
| 1 | 4 | 38 |
...
| 12 | 28 | 33 |
| 12 | 29 | 32 |
| 12 | 30 | 32 |
| 12 | 31 | 27 |
+-------------+-----------+------------+

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

mysql> SELECT birthmonth, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL
-> GROUP BY birthmonth;

+-------------+-------------+
| birthmonth | COUNT(*) |
+-------------+-------------+
| 1 | 1311 |
| 2 | 1144 |
| 3 | 1243 |
| 4 | 1179 |
| 5 | 1118 |
| 6 | 1105 |
| 7 | 1244 |
| 8 | 1438 |
| 9 | 1314 |
| 10 | 1438 |
| 11 | 1314 |
| 12 | 1269 |
+-------------+-------------+

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


Чтобы определить, сколько водителей было в дороге каждый день и сколько миль проехал каждый из них, сгруппируйте записи driver_log по дате:

mysql> SELECT trav_date,
-> COUNT(*) AS 'number of drivers', SUM(miles) As 'miles logged'
-> FROM driver_log GROUP BY trav_date;

+--------------+----------------------+---------------+
| trav_date | number of drivers | miles logged |
+--------------+----------------------+---------------+
| 2001-11-26 | 1 | 115 |
| 2001-11-27 | 1 | 96 |
| 2001-11-29 | 3 | 822 |
| 2001-11-30 | 2 | 355 |
| 2001-12-01 | 1 | 197 |
| 2001-12-02 | 2 | 581 |
+--------------+----------------------+----------------+

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

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


Например, чтобы вывести итоги по времени дня для таблицы mail, сделайте следующее:

mysql> SELECT HOUR(t) AS hour,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY hour;

+------+-------------------------+--------------------------+
| hour | number of messages | number of bytes sent |
+------+------------------------+---------------------------+
| 7 | 1 | 3824 |
| 8 | 1 | 978 |
| 9 | 2 | 2904 |
| 10 | 2 | 1056806 |
| 11 | 1 | 5781 |
| 12 | 2 | 195798 |
| 13 | 1 | 271 |
| 14 | 1 | 98151 |
| 15 | 1 | 1048 |
| 17 | 2 | 2398338 |
| 22 | 1 | 23992 |
| 23 | 1 | 10294 |
+------+------------------------+---------------------------+

Чтобы вывести итоги по дню недели, используйте функцию DAYOFWEEK():

mysql> SELECT DAYOFWEEK(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY weekday;

+-----------+-------------------------+--------------------------+
| weekday | number of messages | number of bytes sent |
+-----------+-------------------------+--------------------------+
| 1 | 1 | 271 |
| 2 | 4 | 2500705 |
| 3 | 4 | 1007190 |
| 4 | 2 | 10907 |
| 5 | 1 | 873 |
| 6 | 1 | 58274 |
| 7 | 3 | 219965 |
+----------+---------------------------+--------------------------+

Чтобы сделать результат более понятным, можно использовать функцию DAYNAME() для вывода не номеров дней недели, а их названий.


Однако названия дней недели сортируются в лексическом порядке (например, «Tuesday» будет стоять после «Friday»), так что применяйте функцию DAYNAME() только для отображения результатов. Продолжайте группировать по числовым значениям дней, чтобы строки вывода сортировались в таком порядке:

mysql> SELECT DAYNAME(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY DAYOFWEEK(t);

+--------------+-------------------------+--------------------------+
| weekday | number of messages | number of bytes sent |
+--------------+-------------------------+---------------------------+
| Sunday | 1 | 271 |
| Monday | 4 | 2500705 |
| Tuesday | 4 | 1007190 |
| Wednesday | 2 | 10907 |
| Thursday | 1 | 873 |
| Friday | 1 | 58274 |
| Saturday | 3 | 219965 |
+--------------+--------------------------+--------------------------+

Аналогично можно суммировать значения по месяцам года, сортируя по числовому значению месяца и выводя его название.Есть множество возможностей ввода категорий для временных значений:

• Столбцы DATETIME и TIMESTAMP обычно содержат много уникальных значений. Для получения итогов за день отбросьте составляющую времени дня, чтобы привести все значения одного дня к единому значению. Любая из предложенных инструкций GROUP BY выполнит такую операцию, но последняя будет наиболее медленной:

GROUP BY FROM_DAYS(TO_DAYS(имя_столбца))
GROUP BY YEAR(имя_столбца), MONTH(имя_столбца), DAYOFMONTH(имя_столбца)
GROUP BY DATE_FORMAT(имя_столбца,'%Y-%m-%e')

• Чтобы вывести месячный или квартальный отчет о продажах, группируйте по MONTH(имя_столбца) или QUARTER(имя_столбца) для помещения дат в соответствующую часть года.

• Чтобы вывести итоговые данные об активности веб-сервера, поместите журналы сервера в MySQL и запускайте запросы, которые будут распределять записи по категориям. В главе 18 рассказано о том, как сделать это для Apache.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Выбор групп только с определенными характеристиками
Группирование по результатам выражения
Использование ключевого слова DISTINCT для удаления дубликатов
Итоги и значения NULL
Классификация некатегориальных данных

Вернуться в раздел: MySQL / 7. Формирование итогов