Применение HAVING при построении запросов с группировкой
Основные принципы работы HAVING
Оператор HAVING применяется для фильтрации групп, созданных с помощью GROUP BY. В отличие от WHERE, который обрабатывает отдельные строки до группировки, HAVING работает с результатами агрегатных функций (COUNT, SUM, AVG, MIN, MAX и других).
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10
Как отфильтровать категории с количеством товаров более 10?
Пример выше показывает только те категории, где количество товаров превышает 10. Если это условие записать в WHERE, возникнет ошибка, так как COUNT(*) - агрегатная функция, недопустимая в WHERE.
SELECT category, COUNT(*)
FROM products
WHERE COUNT(*) > 10
GROUP BY category
При выполнении возникнет ошибка вида Invalid use of group function. Решение: перенести условие в HAVING.
Как применить HAVING без GROUP BY?
HAVING можно использовать и без GROUP BY. В этом случае вся таблица считается одной группой. Например, выборка товаров, если общее количество превышает 100:
SELECT 'Общее количество' AS description, COUNT(*)
FROM products
HAVING COUNT(*) > 100
Такой запрос вернёт одну строку, если условие истинно, и пустой результат в противном случае. Однако чаще HAVING применяется вместе с GROUP BY.
Чем отличается HAVING от WHERE?
WHERE фильтрует строки на этапе до группировки, а HAVING - после. Например, нужно найти категории, в которых средняя цена товаров превышает 500, но при этом учитывать только товары с ценой выше 100 (предварительная фильтрация строк):
SELECT category, AVG(price) AS avg_price
FROM products
WHERE price > 100
GROUP BY category
HAVING AVG(price) > 500
Пояснение:
- WHERE отбрасывает товары дешевле 100;
- GROUP BY группирует оставшиеся;
- HAVING оставляет только те группы, где средняя цена больше 500.
Как использовать HAVING с несколькими условиями?
Условия объединяются с помощью AND, OR, NOT. Пример: выбрать категории, у которых количество товаров больше 5 и средняя цена меньше 1000:
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5 AND AVG(price) < 1000
| category | cnt | avg_price | |------------|-----|-----------| | Одежда | 12 | 750.00 | | Обувь | 8 | 980.00 |
Как применить HAVING с подзапросом?
Подзапрос может возвращать значение для сравнения. Например, найти категории, средняя цена в которых выше средней цены по всем товарам:
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > (SELECT AVG(price) FROM products)
Подзапрос выполняется один раз для всего запроса (в большинстве СУБД).
Как использовать псевдонимы в HAVING?
Многие СУБД (MySQL, PostgreSQL) позволяют ссылаться на псевдоним столбца, определённый в SELECT. Однако в стандарте SQL это не гарантируется. Например:
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING cnt > 10
COUNT(*) > 10.
Расширенные примеры с HAVING
Пример 1: HAVING с CASE и условной агрегацией
Подсчитаем количество заказов со статусом 'completed' и 'cancelled' для каждого клиента, оставив только тех, у кого завершённых заказов больше, чем отменённых.
SELECT
customer_id,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count
FROM orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'completed' THEN 1 END) >
COUNT(CASE WHEN status = 'cancelled' THEN 1 END)
| customer_id | completed_count | cancelled_count | |-------------|-----------------|-----------------| | 101 | 15 | 2 | | 105 | 8 | 7 |
Пример 2: HAVING с DISTINCT внутри агрегата
Найти категории, в которых количество уникальных брендов больше 3.
SELECT category, COUNT(DISTINCT brand) AS unique_brands
FROM products
GROUP BY category
HAVING COUNT(DISTINCT brand) > 3
Пример 3: HAVING с GROUP_CONCAT (MySQL) и условием
Выбрать отделы, в которых хотя бы один сотрудник имеет зарплату выше 100000. Используем GROUP_CONCAT для отладки.
SELECT department,
GROUP_CONCAT(employee_name ORDER BY salary DESC) AS high_earners
FROM employees
GROUP BY department
HAVING MAX(salary) > 100000
| department | high_earners | |------------|-------------------------| | IT | Иванов, Петров | | Sales | Сидоров |
Пример 4: HAVING с несколькими агрегатами и комбинированным условием
Найти клиентов, у которых сумма заказов больше 5000 и количество заказов больше 3.
SELECT customer_id,
SUM(order_amount) AS total_spent,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 5000 AND COUNT(*) > 3
Пример 5: HAVING с подзапросом, возвращающим несколько значений (EXISTS)
Выбрать категории, в которых есть товары с ценой выше средней цены по всем товарам.
SELECT category
FROM products p
GROUP BY category
HAVING EXISTS (
SELECT 1
FROM products p2
WHERE p2.category = p.category
AND p2.price > (SELECT AVG(price) FROM products)
)
Пример 6: HAVING и оконные функции (SQL Server / PostgreSQL)
Хотя HAVING работает с GROUP BY, можно комбинировать с оконными функциями во внешнем запросе. Например, найти категории, где сумма продаж превышает среднюю сумму по всем категориям.
SELECT category, total_sales
FROM (
SELECT category, SUM(amount) AS total_sales,
AVG(SUM(amount)) OVER () AS avg_sales
FROM sales
GROUP BY category
) sub
WHERE total_sales > avg_sales
Здесь HAVING не используется, но тот же результат достигается через подзапрос. HAVING в этом случае не может напрямую ссылаться на оконные функции.