Агрегатные функции, GROUP BY и HAVING в SQL
В MySQL до версии 5.7.5 запрос SELECT shop, product, SUM(kg) FROM sales GROUP BY shop; выполнялся и возвращал для каждого магазина продукт из какой-то одной строки группы — какой именно, сервер не обещал. В MySQL 5.7.5 и новее тот же запрос падает с ERROR 1055: в группе «АТБ» три продукта, и выбирать один из них наугад сервер больше не станет.
Агрегатная функция (COUNT, SUM, AVG, MIN, MAX) вычисляет одно значение по набору строк. GROUP BY разбивает строки на группы с одинаковыми значениями указанных столбцов, и агрегатные функции считаются отдельно для каждой группы. HAVING отбирает группы по результату агрегата — так же, как WHERE отбирает отдельные строки.
1. Учебная таблица sales
Все примеры урока выполняются на таблице sales из урока про оператор SELECT: восемь продаж фруктов в трёх магазинах, у двух позиций цена не внесена (NULL). Если таблицы ещё нет, создайте её этим скриптом. Синтаксис — MySQL 8.
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT,
shop VARCHAR(50) NOT NULL,
product VARCHAR(50) NOT NULL,
kg INT NOT NULL,
price DECIMAL(6,2) NULL,
PRIMARY KEY (id)
);
INSERT INTO sales (shop, product, kg, price) VALUES
('АТБ', 'яблоки', 10, 45.00),
('АТБ', 'груши', 14, 80.00),
('АТБ', 'виноград', 20, NULL),
('Дигма', 'яблоки', 20, 50.00),
('Дигма', 'виноград', 34, 150.00),
('Дигма', 'лимоны', 34, NULL),
('Брусничка', 'лимоны', 14, 120.00),
('Брусничка', 'яблоки', 54, 40.00); Для самопроверки: всего продано 200 кг, по магазинам — АТБ 44, Дигма 88, Брусничка 68; цена известна у шести строк из восьми.
2. Агрегатные функции
Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение. Без GROUP BY весь результат WHERE считается одним набором, и запрос возвращает ровно одну строку.
| Функция | Что вычисляет | NULL в столбце | На пустой выборке |
|---|---|---|---|
COUNT(*) | Производит подсчет строк, удовлетворяющих условию запроса | Строки с NULL считаются | 0 |
COUNT(столбец) | Считает значения столбца, не равные NULL | Пропускаются | 0 |
SUM | Вычисляет арифметическую сумму всех значений колонки | Пропускаются | NULL |
AVG | Вычисляет среднее арифметическое всех значений | Пропускаются и не входят в делитель | NULL |
MAX | Определяет наибольшее из всех выбранных значений | Пропускаются | NULL |
MIN | Определяет наименьшее из всех выбранных значений | Пропускаются | NULL |
Агрегатные функции, за исключением COUNT(*), не учитывают значения NULL. COUNT(столбец) тоже пропускает NULL — подробнее в разделе про COUNT.
Использование агрегатных функций (users — таблица пользователей учебной базы forum, скрипт есть в уроке про ключи):
SELECT count(DISTINCT username) FROM users; -- количество неповторяющихся значений из столбца username
SELECT MAX(id) FROM users;
SELECT MIN(id) FROM users; Все функции сразу на таблице sales:
SELECT COUNT(*) AS all_rows,
COUNT(price) AS priced,
COUNT(DISTINCT product) AS products,
SUM(kg) AS total_kg,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM sales; +----------+--------+----------+----------+-----------+-----------+-----------+
| all_rows | priced | products | total_kg | avg_price | min_price | max_price |
+----------+--------+----------+----------+-----------+-----------+-----------+
| 8 | 6 | 4 | 200 | 80.833333 | 40.00 | 150.00 |
+----------+--------+----------+----------+-----------+-----------+-----------+ avg_price — это сумма цен 485, делённая на 6 строк с ценой, а не на все 8 (было бы 60.625). Если отсутствующая цена должна считаться нулём, пишут AVG(COALESCE(price, 0)).
А что вернут функции, если под условие не подошла ни одна строка?
SELECT COUNT(*), SUM(kg) FROM sales WHERE shop = 'Пятёрочка'; +----------+---------+
| COUNT(*) | SUM(kg) |
+----------+---------+
| 0 | NULL |
+----------+---------+ SUM вернул NULL, а не 0. В Java rs.getInt(2) для такого значения вернёт 0, и отличить «продаж нет» от «продали 0 кг» поможет только rs.wasNull() или COALESCE(SUM(kg), 0) в самом запросе.
3. COUNT(*), COUNT(столбец) и COUNT(DISTINCT)
У COUNT три формы: COUNT(*) считает строки, COUNT(столбец) — значения этого столбца, не равные NULL, а COUNT(DISTINCT столбец) — различные значения без NULL. Для столбца price таблицы sales это 8, 6 и 6: все шесть известных цен разные.
SELECT COUNT(DISTINCT shop) AS shops,
COUNT(DISTINCT product) AS products,
COUNT(DISTINCT shop, product) AS pairs
FROM sales; +-------+----------+-------+
| shops | products | pairs |
+-------+----------+-------+
| 3 | 4 | 8 |
+-------+----------+-------+ COUNT(DISTINCT shop, product) считает различные комбинации значений. Несколько столбцов внутри COUNT(DISTINCT ...) — расширение MySQL, и строки, где хотя бы один из них равен NULL, в подсчёт не попадают. В PostgreSQL то же самое пишут как COUNT(DISTINCT (shop, product)).
Что выведет этот запрос: COUNT с условием
Нужно посчитать позиции дороже 50. Первая мысль — передать условие в COUNT:
SELECT COUNT(price > 50) AS attempt,
SUM(price > 50) AS expensive,
COUNT(CASE WHEN price > 50 THEN 1 END) AS expensive_std
FROM sales; +---------+-----------+---------------+
| attempt | expensive | expensive_std |
+---------+-----------+---------------+
| 6 | 3 | 3 |
+---------+-----------+---------------+ Дороже 50 только груши АТБ, виноград «Дигмы» и лимоны «Бруснички», но COUNT(price > 50) вернул 6. Сравнение в MySQL даёт 1 или 0 (и NULL, если цены нет), а COUNT считает всё, что не NULL, — в том числе нули. SUM(price > 50) складывает единицы и работает, но только в MySQL. Переносимый вариант — COUNT(CASE WHEN условие THEN 1 END): при ложном условии CASE без ELSE возвращает NULL, и строка не считается.
COUNT в Java — это long, а не int
COUNT в MySQL возвращает BIGINT, поэтому результат читают через rs.getLong(1). Драйвер Connector/J отдаёт его из getObject() как Long, и приведение (Integer) rs.getObject(1) бросит ClassCastException. SUM и AVG по целому столбцу возвращают DECIMAL — в Java это BigDecimal.
4. Оператор GROUP BY
Оператор GROUP BY используется для объединения результатов выборки по одному или нескольким столбцам. Он имеет следующий синтаксис: GROUP BY column_name. Агрегатные функции в таком запросе считаются отдельно для каждой группы, и результат содержит одну строку на группу.
Предположим, есть таблица, содержащая информацию о том, сколько кг каждого вида фруктов продал магазин (это таблица sales из раздела 1 без столбца price):
| id | shop | product | kg |
|---|---|---|---|
| 1 | АТБ | яблоки | 10 |
| 2 | АТБ | груши | 14 |
| 3 | АТБ | виноград | 20 |
| 4 | Дигма | яблоки | 20 |
| 5 | Дигма | виноград | 34 |
| 6 | Дигма | лимоны | 34 |
| 7 | Брусничка | лимоны | 14 |
| 8 | Брусничка | яблоки | 54 |
Используя оператор GROUP BY, найти количество проданных кг для каждого магазина:
SELECT shop, SUM(kg) AS total_kg
FROM sales GROUP BY shop; | shop | total_kg |
|---|---|
| АТБ | 44 |
| Дигма | 88 |
| Брусничка | 68 |
Проверка по таблице: АТБ 10 + 14 + 20 = 44, Дигма 20 + 34 + 34 = 88, Брусничка 14 + 54 = 68, всего 200 — столько же, сколько SUM(kg) по всей таблице. Группировать можно и по нескольким столбцам (GROUP BY shop, product); строки с NULL в столбце группировки образуют одну общую группу.
Порядок групп в этом результате не гарантирован. MySQL 5.7 сам сортировал результат по столбцам GROUP BY, а начиная с MySQL 8.0 неявной сортировки нет: группы обычно идут в порядке появления, как выше, но код, который рассчитывал на старое поведение, после обновления может выводить их иначе. Нужен порядок — добавьте ORDER BY shop.
GROUP BY по нескольким столбцам и выражениям
При группировке по нескольким столбцам группа — это уникальная комбинация их значений. В sales каждая пара «магазин — продукт» встречается один раз, поэтому GROUP BY shop, product вернёт 8 групп по одной строке. Полезнее второй уровень, который делит магазин на части. Например, сколько килограммов каждый магазин продал с известной ценой и без неё:
SELECT shop,
price IS NULL AS no_price,
COUNT(*) AS positions,
SUM(kg) AS total_kg
FROM sales
GROUP BY shop, price IS NULL
ORDER BY shop, no_price; +-----------+----------+-----------+----------+
| shop | no_price | positions | total_kg |
+-----------+----------+-----------+----------+
| АТБ | 0 | 2 | 24 |
| АТБ | 1 | 1 | 20 |
| Брусничка | 0 | 2 | 68 |
| Дигма | 0 | 2 | 54 |
| Дигма | 1 | 1 | 34 |
+-----------+----------+-----------+----------+ В GROUP BY можно писать не только столбцы, но и выражения: price IS NULL равно 1 для строк без цены и 0 для остальных. У «Бруснички» все цены известны, поэтому для неё группа одна. Сумма по пяти группам — те же 200 кг.
NULL в столбце группировки
Для GROUP BY все NULL равны между собой и попадают в одну группу — в отличие от WHERE, где NULL = NULL не истинно:
SELECT price, COUNT(*) AS positions, SUM(kg) AS total_kg
FROM sales
WHERE price IS NULL OR price < 50
GROUP BY price
ORDER BY price; +-------+-----------+----------+
| price | positions | total_kg |
+-------+-----------+----------+
| NULL | 2 | 54 |
| 40.00 | 1 | 54 |
| 45.00 | 1 | 10 |
+-------+-----------+----------+ Строки 3 и 6 (виноград АТБ и лимоны «Дигмы», 20 + 34 кг) собрались в группу с price = NULL. При сортировке по возрастанию MySQL ставит её первой.
Итоговая строка: WITH ROLLUP
Модификатор WITH ROLLUP добавляет после групп строку с общим итогом; в столбце группировки у неё стоит NULL. Функция GROUPING(shop) (MySQL 8.0.1+) возвращает 1 именно для итоговой строки, и её можно подписать:
SELECT IF(GROUPING(shop), 'Итого', shop) AS shop_name,
SUM(kg) AS total_kg
FROM sales
GROUP BY shop WITH ROLLUP; +-----------+----------+
| shop_name | total_kg |
+-----------+----------+
| АТБ | 44 |
| Брусничка | 68 |
| Дигма | 88 |
| Итого | 200 |
+-----------+----------+ COALESCE(shop, 'Итого') вместо GROUPING() подходит, только если в самом столбце нет NULL: иначе обычная NULL-группа тоже получит подпись «Итого». До MySQL 8.0.12 WITH ROLLUP нельзя было сочетать с ORDER BY. Запись WITH ROLLUP есть только в MySQL и MariaDB; PostgreSQL, Oracle и SQL Server используют стандартную форму GROUP BY ROLLUP(shop).
5. Какие столбцы можно выбрать с GROUP BY: ONLY_FULL_GROUP_BY и ошибка 1055
В списке SELECT запроса с GROUP BY могут стоять только столбцы из GROUP BY и агрегатные функции. Что будет, если добавить туда product:
SELECT shop, product, SUM(kg) FROM sales GROUP BY shop; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'forum.sales.product' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by В группе «АТБ» три разных продукта, и непонятно, какой из них показать. Режим ONLY_FULL_GROUP_BY включён по умолчанию с MySQL 5.7.5; в старых версиях такой запрос выполнялся и возвращал продукт из произвольной строки группы. Исправляют тремя способами: добавить столбец в GROUP BY, обернуть его в агрегатную функцию (MAX(product), GROUP_CONCAT(product)) или явно согласиться на любое значение через ANY_VALUE(product).
Если нужны все продукты группы, подходит GROUP_CONCAT — агрегатная функция MySQL, которая склеивает значения группы в одну строку:
SELECT shop,
GROUP_CONCAT(product ORDER BY product SEPARATOR ', ') AS products,
SUM(kg) AS total_kg
FROM sales
GROUP BY shop
ORDER BY shop; +-----------+--------------------------+----------+
| shop | products | total_kg |
+-----------+--------------------------+----------+
| АТБ | виноград, груши, яблоки | 44 |
| Брусничка | лимоны, яблоки | 68 |
| Дигма | виноград, лимоны, яблоки | 88 |
+-----------+--------------------------+----------+ Результат GROUP_CONCAT обрезается до group_concat_max_len байт (по умолчанию 1024) с предупреждением, а не с ошибкой; для длинных списков значение переменной увеличивают. ERROR 1055 не возникает и тогда, когда столбец однозначно определяется столбцами группировки: при GROUP BY id по первичному ключу в SELECT можно перечислить любые столбцы этой таблицы — MySQL распознаёт функциональную зависимость.
6. Оператор SQL HAVING
Оператор SQL HAVING задаёт условие для групп, полученных после GROUP BY: в нём проверяют результат агрегатных функций. WHERE отбирает строки до группировки, HAVING — группы после неё, поэтому условие вроде «сумма больше 60» можно написать только в HAVING.
Используя оператор SQL HAVING, вывести названия магазинов, которые продали более 60 кг:
SELECT shop, SUM(kg) AS total_kg
FROM sales GROUP BY shop
HAVING SUM(kg) > 60; | shop | total_kg |
|---|---|
| Дигма | 88 |
| Брусничка | 68 |
MySQL разрешает написать в HAVING и псевдоним: HAVING total_kg > 60. Это расширение MySQL; по стандарту SQL и в PostgreSQL условие повторяют через функцию, как в примере выше.
Агрегат в HAVING не обязан стоять в списке SELECT: SELECT shop FROM sales GROUP BY shop HAVING COUNT(*) = 3; вернёт АТБ и Дигму — у этих магазинов по три позиции.
7. WHERE и HAVING в одном запросе
WHERE и HAVING отличаются моментом фильтрации: WHERE отбрасывает строки до того, как они попадут в группы, HAVING — готовые группы по значению агрегата. Поэтому условие на обычный столбец пишут в WHERE, а условие на SUM, COUNT и другие агрегаты — только в HAVING.
WHERE и HAVING часто нужны в одном запросе. Сколько килограммов продал каждый магазин без учёта яблок — и только те магазины, где таких продаж больше 30 кг:
SELECT shop, SUM(kg) AS total_kg
FROM sales
WHERE product <> 'яблоки'
GROUP BY shop
HAVING SUM(kg) > 30
ORDER BY shop; +-------+----------+
| shop | total_kg |
+-------+----------+
| АТБ | 34 |
| Дигма | 68 |
+-------+----------+ WHERE убрал три строки с яблоками, GROUP BY посчитал суммы (АТБ 34, Дигма 68, Брусничка 14), и HAVING отбросил Брусничку.
| Признак | WHERE | HAVING |
|---|---|---|
| Что фильтрует | Отдельные строки таблицы | Группы, полученные после GROUP BY |
| Когда выполняется | До группировки | После группировки |
| Агрегатные функции в условии | Нельзя: ERROR 1111 (HY000): Invalid use of group function | Можно |
Псевдонимы из SELECT | Нельзя: ERROR 1054 | В MySQL можно, по стандарту — нет |
| Производительность | Условия на обычные столбцы пишут здесь: в группировку попадёт меньше строк, может сработать индекс | Только условия на агрегаты |
8. GROUP BY и HAVING в порядке выполнения запроса
Логически запрос выполняется так: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Группы появляются только на третьем шаге, поэтому агрегатная функция в WHERE недопустима, а в ORDER BY, который идёт после SELECT, можно сортировать и по агрегату, и по его псевдониму.
SELECT shop, SUM(kg) FROM sales WHERE SUM(kg) > 60 GROUP BY shop; ERROR 1111 (HY000): Invalid use of group function Сортировка групп по агрегату — самые продаваемые продукты:
SELECT product, COUNT(*) AS shops, SUM(kg) AS total_kg
FROM sales
GROUP BY product
ORDER BY total_kg DESC; +----------+-------+----------+
| product | shops | total_kg |
+----------+-------+----------+
| яблоки | 3 | 84 |
| виноград | 2 | 54 |
| лимоны | 2 | 48 |
| груши | 1 | 14 |
+----------+-------+----------+ С LIMIT 1 тот же запрос вернёт лидера — яблоки, 84 кг. Полная таблица шагов с тем, что доступно на каждом из них, и разбор ошибки с псевдонимом в WHERE — в уроке про оператор SELECT. Группировку по данным из нескольких таблиц (например, число сообщений у каждого пользователя форума) строят поверх JOIN — см. урок SQL: все виды джойнов.
9. На чём чаще всего ошибаются с GROUP BY
Ошибки в запросах с агрегатами бывают двух видов: одни MySQL отклоняет с кодом ошибки, другие выполняются и дают правдоподобное, но неверное число. Вторые опаснее — в таблице они выделены.
| Ситуация | Что происходит | Как правильно |
|---|---|---|
Агрегатная функция в WHERE | ERROR 1111: Invalid use of group function | Условие в HAVING |
Столбец не из GROUP BY в списке SELECT | ERROR 1055 (only_full_group_by); в MySQL до 5.7.5 — значение из случайной строки | Добавить в GROUP BY или обернуть в агрегат / ANY_VALUE() |
Расчёт на порядок строк после GROUP BY | С MySQL 8.0 группы не сортируются | Явный ORDER BY |
AVG(price) при пропусках цены | Строки с NULL не входят ни в сумму, ни в делитель | AVG(COALESCE(price, 0)), если пропуск означает ноль |
COUNT(price > 50) для подсчёта по условию | Считаются все строки с ценой: 6 вместо 3 | COUNT(CASE WHEN price > 50 THEN 1 END) или SUM(price > 50) в MySQL |
COUNT(price), когда нужно число строк | Строки с NULL пропускаются: 6 вместо 8 | COUNT(*) |
SUM на пустой выборке | NULL; в Java getInt() молча возвращает 0 | COALESCE(SUM(kg), 0) или rs.wasNull() |
Условие на обычный столбец в HAVING | MySQL выполнит, но строки сначала группируются, а потом отбрасываются; индекс не используется | Перенести условие в WHERE |
Часто задаваемые вопросы
Можно ли использовать HAVING без GROUP BY?
Да. Без GROUP BY вся выборка считается одной группой, и HAVING решает, вернуть её или нет: SELECT SUM(kg) FROM sales HAVING SUM(kg) > 100; вернёт одну строку со значением 200, а при условии > 500 — пустой результат. Условия на обычные столбцы MySQL тоже пропустит в HAVING, но их лучше писать в WHERE. Разница — в разделе WHERE и HAVING.
Чем COUNT(*) отличается от COUNT(1) и COUNT(id)?
COUNT(*) и COUNT(1) в MySQL равнозначны: оба считают строки, и оптимизатор выполняет их одинаково, так что COUNT(1) не быстрее. COUNT(столбец) считает только значения, не равные NULL; для столбца NOT NULL, такого как id, результат совпадёт с COUNT(*), а для price в учебной таблице будет 6 вместо 8. Остальные формы — в разделе COUNT.
Как найти дубликаты в таблице с помощью GROUP BY?
Сгруппировать по столбцам, которые должны быть уникальными, и оставить группы, где больше одной строки: SELECT product, COUNT(*) FROM sales GROUP BY product HAVING COUNT(*) > 1; вернёт яблоки (3), виноград (2) и лимоны (2). Чтобы увидеть сами повторяющиеся строки, добавьте GROUP_CONCAT(id) — получится список их id.
Как вывести строку с максимальным значением в каждой группе?
MAX(kg) с GROUP BY shop вернёт только число, а не продукт. Нужен подзапрос: SELECT shop, product, kg FROM sales WHERE (shop, kg) IN (SELECT shop, MAX(kg) FROM sales GROUP BY shop); вернёт виноград АТБ (20), яблоки «Бруснички» (54) и две строки «Дигмы» — виноград и лимоны по 34 кг, потому что максимум у них общий. В MySQL 8.0+ то же решают оконной функцией ROW_NUMBER() OVER (PARTITION BY shop ORDER BY kg DESC): она оставит ровно одну строку на магазин.
Чем GROUP BY отличается от DISTINCT?
Без агрегатных функций результат одинаковый: SELECT DISTINCT shop FROM sales и SELECT shop FROM sales GROUP BY shop вернут три магазина, и MySQL часто выполняет оба запроса одинаково. Разница в назначении: DISTINCT только убирает повторы строк результата, а GROUP BY нужен, чтобы посчитать что-то по каждой группе и отфильтровать группы через HAVING. Про DISTINCT — в уроке про SELECT.
Комментарии