HAVING в SQL: как фильтровать группы после GROUP BY

При работе с SQL часто возникает задача отфильтровать не сами строки, а уже сгруппированные данные — например, оставить только клиентов с большим количеством заказов или категории с высокой выручкой. Для этого используют оператор HAVING, который работает вместе с GROUP BY. В статье разберем, чем он отличается от WHERE, как правильно его применять и как с его помощью фильтровать группы по COUNT, SUM, AVG и другим агрегатным функциям.

Как работает HAVING в связке с GROUP BY

GROUP BY и HAVING часто используют вместе. Сначала GROUP BY объединяет строки в группы по общему признаку, а потом HAVING отбирает только те из них, которые подходят под условие.

Например, есть таблица orders, в которой хранятся заказы клиентов. Чтобы посмотреть, сколько заказов сделал каждый клиент, нужно составить такой запрос:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id;

Здесь строки из таблицы orders группируются по customer_id, а счетчик COUNT(*) считает количество заказов в каждой группе. В результате получается по одной строке на каждого клиента с числом его заказов.

Далее можно вывести только тех клиентов, у которых больше трех заказов. Тогда и пригодится HAVING:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

Такой запрос сначала соберет строки по клиентам, посчитает количество заказов в каждой группе и только после этого оставит те группы, где значение COUNT(*) больше трех. То есть в результат попадут только клиенты, которые сделали более трех заказов.

Как правильно использовать HAVING

Синтаксис запроса выглядит примерно так:

SELECT столбцы
FROM таблица
WHERE условие_для_строк
GROUP BY столбцы_группировки
HAVING условие_для_групп
ORDER BY итоговые_столбцы;

В условии HAVING чаще всего используют агрегатные функции — COUNT(), SUM(), AVG(), MIN() и MAX(), о которых мы поговорим ниже.

Когда использовать HAVING и WHERE

Важно запомнить следующее:

• WHERE работает до группировки;
• HAVING работает после группировки.

Например, если нужно взять только заказы со статусом paid, то достаточно применить WHERE:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

В этом запросе сначала из таблицы берем только оплаченные заказы, а потом группируем по клиентам.

Но если нужно оставить только тех клиентов, у которых после подсчета оказалось, например, больше трех оплаченных заказов, то мы добавляем HAVING:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) > 3;

Здесь WHERE отбирает только строки со статусом paid, затем GROUP BY собирает их по customer_id, а HAVING оставляет только те группы, в которых количество заказов больше трех.

Как использовать HAVING с агрегатными функциями

HAVING COUNT()

В примерах выше уже встречалась функция COUNT. Теперь разберем ее подробнее. Она нужна для подсчета строк внутри группы. В запросах чаще всего используют варианты COUNT(*) и COUNT(column). COUNT(*) считает все строки в группе, а COUNT(column) считает только те строки, в которых значение этого столбца не равно NULL.

С HAVING функцию COUNT используют, когда нужно отобрать группы по числу записей. Например, если нужно оставить только те группы, где число записей попадает в диапазон, то можно использовать BETWEEN:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) BETWEEN 2 AND 5;

Такой запрос вернет клиентов, у которых от двух до пяти заказов включительно.

Можно использовать и COUNT(column), если нужно считать не все строки, а только те, где заполнено конкретное поле. Например, если в таблице orders поле delivery_date заполнено только у доставленных заказов, то можно посчитать не все заказы клиента, а только те, у которых есть дата доставки:

SELECT customer_id, COUNT(delivery_date) AS delivered_orders_count
FROM orders
GROUP BY customer_id
HAVING COUNT(delivery_date) > 1;

Здесь будут учитываться только строки, где delivery_date не равно NULL.

Важно помнить, что COUNT(*) и COUNT(column) могут давать разный результат, если в столбце есть значения NULL.

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

А вот в WHERE нельзя использовать агрегатную функцию. 

Например, такой запрос будет неверным:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer_id;

Так как WHERE работает до группировки, на этом этапе COUNT(*) еще не посчитан. Сначала SQL должен сгруппировать строки, затем вычислить количество записей в каждой группе, а потом можно применить условие через HAVING.

HAVING SUM(), AVG(), MIN() и MAX()

Функции SUM(), AVG(), MIN() и MAX() тоже работают с группами и позволяют фильтровать их по итоговому значению.

SUM()

Функцию SUM() используют, когда нужно отобрать группы по общей сумме. Например, если нужно найти клиентов, которые потратили больше 10 000, подойдет такой запрос:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

Сначала запрос группирует строки по customer_id, затем считает общую сумму заказов для каждого клиента и после этого оставляет только те группы, где SUM(amount) больше 10000. В результат попадут только клиенты, у которых суммарная стоимость всех заказов превышает этот порог.

AVG()

По такому же принципу работает AVG(), только вместо суммы считается среднее значение. Например, если нужно выбрать отели со средней оценкой не ниже 4.5, то запрос будет таким:

SELECT hotel_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY hotel_id
HAVING AVG(rating) >= 4.5;

Здесь отзывы сначала группируются по hotel_id, затем для каждой группы вычисляется средняя оценка, и HAVING оставляет только те отели, у которых AVG(rating) не меньше 4.5.

MIN()

Функцию MIN() используют, когда нужно отобрать группы по минимальному значению внутри. Например, если нужно найти категории, в которых самый дешевый товар стоит не менее 1000, запрос будет таким:

SELECT category_id, MIN(price) AS min_price
FROM products
GROUP BY category_id
HAVING MIN(price) >= 1000;

Здесь для каждой категории определяется минимальная цена, а затем HAVING оставляет только те категории, где это значение не меньше 1000.

MAX()

Функция MAX() работает похожим образом, но ищет максимальное значение в группе. Например, если нужно выбрать отделы, в которых максимальная зарплата превышает 300000, запрос будет таким:

В этом случае запрос сгруппирует сотрудников по отделам, найдет максимальную зарплату в каждом и вернет только те группы, где значение больше 300000.

HAVING с несколькими условиями

Бывает, что одного условия в HAVING недостаточно и нужно отобрать только те группы, которые соответствуют нескольким требованиям или подходят хотя бы под одно из них.

Для этого в HAVING используют логические операторы AND, OR и NOT.

AND

Например, нужно найти категории, в которых товаров больше пяти и при этом средняя цена выше 1000:

SELECT category_id, COUNT(*) AS products_count, AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5 AND AVG(price) > 1000;

Здесь запрос сначала группирует товары по category_id, затем считает количество товаров и среднюю цену в каждой группе. После этого HAVING оставляет только те категории, где одновременно выполняются оба условия: товаров больше пяти, а средняя цена выше 1000.

OR

Можно использовать оператор OR, если достаточно выполнения хотя бы одного условия. Например, запрос ниже вернет отделы, в которых либо работает меньше трех сотрудников, либо средняя зарплата превышает 200000:

SELECT department_id, COUNT(*) AS employees_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) < 3 OR AVG(salary) > 200000;

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

NOT

Оператор NOT нужен, когда условие в HAVING надо исключить. Например, если нужно вывести категории, в которых товаров не больше пяти, то можно написать так:

SELECT category_id, COUNT(*) AS products_count
FROM products
GROUP BY category_id
HAVING NOT COUNT(*) > 5;

Такой запрос оставит только те группы, где количество товаров не превышает 5. Но у этой конструкции есть более читаемый вариант:

SELECT category_id, COUNT(*) AS products_count
FROM products
GROUP BY category_id
HAVING COUNT(*) <= 5;

NOT также можно использовать с диапазонами. Например, если нужно оставить группы, у которых сумма продаж не попадает в диапазон от 10000 до 50000, то составим запрос с NOT:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) NOT BETWEEN 10000 AND 50000;

Такой запрос вернет клиентов, у которых общая сумма заказов либо меньше 10000, либо больше 50000.

Можно использовать IN вместе с NOT, когда, например, нужно исключить группы с определенным количеством записей:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) NOT IN (1, 2, 3);

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

HAVING и группировка по нескольким столбцам

GROUP BY позволяет группировать данные сразу по нескольким столбцам. В таком случае группа строится по сочетанию значений, а HAVING затем фильтрует эти составные группы.

Например, есть таблица employees:

Если нужно найти пары department_id и position, в которых сотрудников больше двух, то запрос будет таким:

SELECT department_id, position, COUNT(*) AS employees_count
FROM employees
GROUP BY department_id, position
HAVING COUNT(*) > 2;

Этот запрос сначала разобьет строки на группы по двум полям (отдел, должность):

• (1, developer); 
• (1, manager); 
• (2, developer).

А потом посчитает их количество в employees_count. Запрос будет считать количество сотрудников для каждой конкретной должности внутри каждого конкретного отдела. То есть если в одном отделе присутствуют должности manager и developer, то для них будут созданы разные группы — это касается и тех случаев, где department_id совпадает.

Результат подсчета будет таким:

В результате HAVING COUNT(*) > 2 оставит только те группы department_id и position, где сотрудников больше двух. В итоге останется одна строка:

Порядок столбцов в GROUP BY обычно не меняет сам набор групп. Записи GROUP BY department_id, position и GROUP BY position, department_id разобьют данные на те же самые сочетания значений. Но для читаемости порядок важен. Обычно столбцы ставят так, чтобы было удобнее воспринимать логику запроса. В этом примере сначала идет отдел, потом должность, потому что речь идет о должностях внутри отдела.

HAVING в SQL: коротко о главном

• HAVING используют, когда нужно отфильтровать результаты группировки. 
• В отличие от WHERE, оператор HAVING работает после GROUP BY. 
• HAVING проверяет не отдельные строки, а группы строк. 
• Чаще всего в HAVING используют агрегатные функции COUNT, SUM, AVG, MIN и MAX. 
• Через HAVING COUNT() можно отбирать группы по числу записей. 
• Через HAVING SUM() и HAVING AVG() можно фильтровать группы по сумме и среднему значению. 
• В HAVING можно задавать несколько условий через AND, OR и NOT. 
• HAVING особенно полезен в аналитических запросах, где важно не наличие строки, а итоговый показатель по группе.
• Если условие относится к отдельным строкам, то нужен WHERE. Если к результату группировки, то нужен HAVING.