Группировка данных и агрегатные функции

Оператор GROUP BY

Иногда требуется узнать информацию не о самих объектах, а об определенных группах, которые они образуют. Для этого используется оператор GROUP BY и агрегатные функции.

Общая структура запроса с GROUP BY

MySQL
SELECT [константы, агрегатные_функции, поля_группировки] 
FROM имя_таблицы 
GROUP BY поля_группировки;

Пример использования

Для демонстрации работы оператора GROUP BY выведем общую сумму потраченных средств, сгруппированных по членам семьи.

MySQL
SELECT family_member, SUM(unit_price * amount) FROM Payments 
GROUP BY family_member;

Ниже представлен набор данных, находящихся в таблице Payments:

family_memberunit_priceamount
120001
221001
3205
43501
41502
51001
21201
255001
52301
52301
52501
52501
322001
2660001
185
175
283
188
175
21503
31001
1103
13001
31508

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

Для того, чтобы лучше понять как происходит группировка по полю, преобразуем исходные данные в новый, сгрупированный вид:

family_memberunit_priceamount
120001
3001
103
75
88
75
85
221001
1201
55001
1503
83
660001
31508
1001
22001
205
41502
3501
52501
2501
2301
2301
1001

Как видно, образовались группы записей, объединённых одним family_member. После этого мы можем внутри каждой из этих групп применить формулу суммы, которая умножит количество товара на его стоимость, а потом просуммирует все получившиеся значения:

family_memberSUM(unit_price * amount)
12504
274194
33600
4650
51060
Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.

Агрегатные функции

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение.

Агрегатные функции применяются для значений, не равных NULL. Исключением является функция COUNT().
ФункцияОписание
SUM(поле_таблицы)Возвращает сумму значений
AVG(поле_таблицы)Возвращает среднее значение
COUNT(поле_таблицы)Возвращает количество записей
MIN(поле_таблицы)Возвращает минимальное значение
MAX(поле_таблицы)Возвращает максимальное значение

Оператор HAVING

Для фильтрации строк по значениям агрегатных функций используется оператор HAVING.

Общая структура запроса с оператором HAVING

MySQL
SELECT [константы, агрегатные_функции, поля_группировки] 
FROM имя_таблицы 
GROUP BY поля_группировки
HAVING условие_на_ограничение_строк_после_группировки

Отличие HAVING от WHERE

  • WHERE — сначала выбираются записи по условию, а затем могут быть сгруппированы, отсортированы и т.д.
  • HAVING — сначала группируются записи, а затем выбираются по условию, при этом, в отличие от WHERE, в нём можно использовать значения агрегатных функций

Пример использования

Выведем общую сумму, потраченную на покупки, для каждого члена семьи, где общая сумма покупки меньше, чем 5000 рублей:

MySQL
SELECT family_member, SUM(unit_price * amount) AS sum 
FROM Payments GROUP BY family_member
HAVING sum < 5000;

Задания для самопроверки

Сортировка данных, оператор ORDER BY
Многотабличные запросы, оператор JOIN