HAVING
Как отфильтровать уже готовые группы после GROUP BY
Теория
Представьте ситуацию. Мы посчитали, сколько пользователей зарегистрировалось из каждого города. Но теперь нам не нужны города, где зарегистрирован всего один человек. Мы хотим оставить только те города, где пользователей хотя бы два. То есть нужна фильтрация. Что мы знаем о фильтрации?
Ответ: За фильтрацию в SQL отвечает оператор WHERE.
Но в этом случае оператор WHERE не поможет, потому что он работает раньше группировки и фильтрует отдельные строки, а не готовые группы.
Добавим немного описания механизма работы того, почему для таких задач не подходит оператор WHERE. Что можно фильтровать этим оператором? Все столбцы из базовой таблицы. То есть столбцы, которые вы можете получить, просто написав SELECT запрос к таблице, например такой: SELECT user_id, city, registration_date FROM users. В этом запросе для того, чтобы выполнить фильтрацию пользователей по городам, дате регистрации, идентификатору, не нужно выполнять никаких дополнительных вычислений и сложных преобразований, потому что эта информация и так содержится в базовой таблице.
Можно ли из данной таблицы узнать точное количество пользователей в каждом городе? — Нет. Чтобы получить эту информацию, нужно сгруппировать данные по городам с помощью GROUP BY и применить функцию COUNT(), которая подсчитает количество пользователей в каждой группе.
Ключевой момент: в исходной таблице отсутствовал столбец с количеством пользователей. Мы получили этот столбец только после выполнения нескольких дополнительных операций и вычислений, а именно с помощью операции GROUP BY. До этого момента такого столбца не существовало.
Судя по всему, в SQL существует какой-то другой способ для фильтрации результатов, полученных после группировки. И это действительно так. За подобную манипуляцию отвечает еще один оператор фильтрации HAVING.
Этот оператор работает после GROUP BY и проверяет уже не отдельную строку таблицы, а итог по группе.
Простым языком, разница такая:
• WHERE — определяет, какие строки будут включены в расчет до группировки;
• HAVING — определяет, какие группы данных, которые уже были подсчитаны, будут включены в итоговый результат.
Необходимость использования оператора HAVING для решения задачи можно увидеть по таким формулировкам:
• показать только те города, где пользователей больше 5;
• оставить только те статусы, где заказов не меньше 3;
• показать только те отделы, где средняя зарплата выше 90000.
То есть здесь нас интересует не просто строка таблицы, а итоговые данные по группе: количество, сумма, среднее значение, максимум или минимум.
Ответ: За фильтрацию в SQL отвечает оператор WHERE.
Но в этом случае оператор WHERE не поможет, потому что он работает раньше группировки и фильтрует отдельные строки, а не готовые группы.
Добавим немного описания механизма работы того, почему для таких задач не подходит оператор WHERE. Что можно фильтровать этим оператором? Все столбцы из базовой таблицы. То есть столбцы, которые вы можете получить, просто написав SELECT запрос к таблице, например такой: SELECT user_id, city, registration_date FROM users. В этом запросе для того, чтобы выполнить фильтрацию пользователей по городам, дате регистрации, идентификатору, не нужно выполнять никаких дополнительных вычислений и сложных преобразований, потому что эта информация и так содержится в базовой таблице.
Можно ли из данной таблицы узнать точное количество пользователей в каждом городе? — Нет. Чтобы получить эту информацию, нужно сгруппировать данные по городам с помощью GROUP BY и применить функцию COUNT(), которая подсчитает количество пользователей в каждой группе.
Ключевой момент: в исходной таблице отсутствовал столбец с количеством пользователей. Мы получили этот столбец только после выполнения нескольких дополнительных операций и вычислений, а именно с помощью операции GROUP BY. До этого момента такого столбца не существовало.
Судя по всему, в SQL существует какой-то другой способ для фильтрации результатов, полученных после группировки. И это действительно так. За подобную манипуляцию отвечает еще один оператор фильтрации HAVING.
Этот оператор работает после GROUP BY и проверяет уже не отдельную строку таблицы, а итог по группе.
Простым языком, разница такая:
• WHERE — определяет, какие строки будут включены в расчет до группировки;
• HAVING — определяет, какие группы данных, которые уже были подсчитаны, будут включены в итоговый результат.
Необходимость использования оператора HAVING для решения задачи можно увидеть по таким формулировкам:
• показать только те города, где пользователей больше 5;
• оставить только те статусы, где заказов не меньше 3;
• показать только те отделы, где средняя зарплата выше 90000.
То есть здесь нас интересует не просто строка таблицы, а итоговые данные по группе: количество, сумма, среднее значение, максимум или минимум.
Синтаксис
SELECT column_name, -- выводим сгруппированный столбец
aggregate_function(other_column) -- считаем итоговое значение внутри каждой группы
FROM table_name -- указываем таблицу, из которой берём данные
GROUP BY column_name -- собираем строки в группы по column_name
HAVING aggregate_function(other_column) > value; -- оставляем только группы, где итоговое значение больше value
Сначала запрос читает строки из таблицы после FROM. Потом, если есть WHERE, он отбрасывает ненужные строки. После этого GROUP BY собирает оставшиеся строки в группы, а агрегатные функции считают итог внутри каждой группы.
После этого выполняется HAVING.
Этот оператор проверяет результат группировки и решает, какие группы оставить, а какие убрать.
Поэтому внутри HAVING обычно и пишут агрегатные функции: COUNT(*), AVG(salary), SUM(quantity) и так далее.
Ведь проверяем мы именно итог по группе.
Если условие относится к отдельным строкам до группировки, ему место в WHERE. Если условие относится к уже посчитанной группе, ему место в HAVING.
После этого выполняется HAVING.
Этот оператор проверяет результат группировки и решает, какие группы оставить, а какие убрать.
Поэтому внутри HAVING обычно и пишут агрегатные функции: COUNT(*), AVG(salary), SUM(quantity) и так далее.
Ведь проверяем мы именно итог по группе.
Если условие относится к отдельным строкам до группировки, ему место в WHERE. Если условие относится к уже посчитанной группе, ему место в HAVING.
Примеры
1. Города, где зарегистрировано больше пяти пользователей
Сначала пользователи собираются в группы по городам, потом COUNT(*) считает количество пользователей в каждом городе, и только после этого HAVING оставляет те города, где пользователей больше пяти. Это классический сценарий для HAVING.
🔄 Попробуйте изменить запрос:
- • Поменяйте условие на HAVING COUNT(*) >= 2
- • Уберите функцию COALESCE и посмотрите, как будет выглядеть группа с NULL
2. Отделы, где средняя зарплата выше 90000
Нас интересует средняя зарплата по отделу. Значит, сначала нужно выполнить группировку по department_id, посчитать среднее, а потом через HAVING отфильтровать только те отделы, где этот итог выше нужного порога.
🔄 Попробуйте изменить запрос:
- • Поменяйте порог на 93000
- • Найдите отделы, в которых минимальная зарплата больше 90000
3. Статусы заказов в 2024 году, которые встречаются минимум два раза
Наглядный пример, на котором видно разницу между WHERE и HAVING. Сначала WHERE оставляет только заказы за 2024 год, затем идёт группировка по статусу, и только потом HAVING убирает редкие группы.
🔄 Попробуйте изменить запрос:
- • Поменяйте условие на HAVING COUNT(*) > 6
- • Попробуйте условие COUNT(*) >= 2 прописать в WHERE, а не в HAVING. Объясните результат
4. Почему в HAVING нужно писать полное выражение, а не псевдоним
Сам запрос написан правильно: в HAVING используется полное выражение COUNT(*). Это подходящий момент, чтобы показать один нюанс PostgreSQL: если заменить выражение на псевдоним workers_count, мы получим ошибку. Поэтому в HAVING необходимо писать само агрегатное выражение целиком.
🔄 Попробуйте изменить запрос:
- • Попробуйте заменить COUNT(*) в HAVING на workers_count и посмотрите, что произойдёт
- • Объясните такое поведение
5. Пользователи, у которых есть минимум два заказа с указанным статусом
Сначала мы убираем заказы без статуса, потому что они нам не нужны для этой задачи. Потом группируем строки по пользователям. А дальше HAVING оставляет только тех пользователей, у которых таких заказов хотя бы два.
🔄 Попробуйте изменить запрос:
- • Поменяйте условие на HAVING COUNT(*) >= 5
- • Попробуйте заменить WHERE status IS NOT NULL на WHERE status IS NULL и сравните результат
Типичные ошибки
Путают WHERE и HAVING
WHERE работает по отдельным строкам до группировки. HAVING работает по уже готовым группам после GROUP BY. Это главное различие.
Пытаются писать агрегатное условие в WHERE
Запись вроде WHERE COUNT(*) > 2 не подходит. В этот момент SQL ещё не посчитал группы. Такое условие должно быть в HAVING.
Пишут в HAVING обычное условие, которое лучше подошло бы для WHERE
Например, если нужно оставить только заказы за 2024 год, это лучше сделать через WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31', а не писать это в HAVING. Так запрос и понятнее, и логичнее, к тому же зачастую в HAVING это не будет работать.
Пытаются использовать в HAVING псевдоним из SELECT
В PostgreSQL такая запись приведет к ошибке. Поэтому в HAVING необходимо писать само агрегатное выражение целиком: HAVING COUNT(*) >= 2, а не псевдоним вроде orders_count.
Практика
Проверь себя
Ответьте на вопросы, чтобы закрепить материал:
1
В чём главное отличие HAVING от WHERE?
2
Что не так в условии WHERE COUNT(*) > 2?
3
Напишите запрос к таблице orders, который выведет worker_id и количество заказов каждого работника за 2024 год. В результат должны попасть только те работники, у которых таких заказов больше одного. Отсортируйте результат по количеству заказов по убыванию, а при равенстве — по worker_id.
4
Если группа не проходит условие в HAVING, что произойдёт?
5
Напишите запрос к таблице products. Нужно сгруппировать товары по категориям после фильтра price BETWEEN 50000 AND 120000 и оставить только те категории, где товаров минимум два. Если категория не указана, вместо неё покажите 'без категории'. В результате выведите два столбца: category_label и products_count. Отсортируйте результат по названию категории.
6
Что произойдёт в PostgreSQL, если в HAVING попробовать использовать псевдоним из SELECT вместо полного агрегатного выражения?
7
Напишите запрос к таблице order_items, который покажет product_id и общее количество проданных единиц товара по каждому продукту. В расчёт должны попасть только строки, где quantity >= 2. В результате оставьте только те товары, у которых после такого фильтра суммарное количество больше 10. Назовите итоговый столбец total_quantity и отсортируйте результат по этому количеству по убыванию, а затем по product_id.
8
Если запрос уже выполнил GROUP BY и теперь нужно оставить только те группы, где COUNT(*) > 3, какое ключевое слово нужно использовать? Введите одно слово.