Фильтрация агрегированных данных с HAVING

Вот что мы еще не обсуждали, так это как отфильтровать группы после применения агрегатов? Иногда нам не нужны все факультеты — только те, где студентов больше сотни. Или нам важно посмотреть только на отделы, где средняя зарплата выше 50,000. Сегодня мы познакомимся с фильтрацией агрегированных данных с помощью HAVING.

Зачем нам HAVING, ведь у нас есть WHERE? Можно же просто поставить WHERE после GROUP BY :)

Всё не так просто! Во-первых, порядок операторов в SQL фиксирован и WHERE по этому порядку выполняется до GROUP BY.

А может можно подвинуть его после GROUP BY?

Тоже нет! Очень часто бывает нужно отфильтровать строки таблицы до группировки. Затем выполнить над отфильтрованными данными группировку. А затем отбросить какие-нибудь ненужные данные после группировки.

Тогда может просто взять оператор WHERE, скопировать его, назвать HAVING и разместить после GROUP BY?

Да, так и сделаем! :)

Отличие HAVING от WHERE

WHERE фильтрует строки до группировки.

Представьте, что вы отбираете торты по вкусу: клубничные и шоколадные — оставляете, а остальные — в сторону. Это задача для WHERE.

HAVING фильтрует после того, как данные были сгруппированы и агрегатные функции сделали свою магию.

Например, вы уже сгруппировали торты по столам, посчитали их количество и теперь хотите оставить только те столы, где тортов больше трех.

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

Синтаксис HAVING

Синтаксис почти такой же, как у WHERE, но работает он немного по-другому:

SELECT столбцы, агрегатные_функции
FROM таблица
GROUP BY столбцы
HAVING условие;

Этапы выполнения:

1. Сначала строки фильтруются через WHERE.
2. Затем данные группируются с помощью GROUP BY.
3. К результатам группировки применяются агрегатные функции.
4. Наконец, получившийся результат фильтруются с помощью HAVING.

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

Пример 1: Фильтрация факультетов с большим числом студентов

Вы хотите узнать, какие факультеты в университете имеют больше 100 студентов. Предположим, у нас есть таблица students:

Запрос:

SELECT faculty, COUNT(*) AS student_count
FROM students
GROUP BY faculty
HAVING COUNT(*) > 100;

Что тут происходит:

• Сначала мы группируем студентов по столбцу faculty с помощью GROUP BY.
• Затем агрегатная функция COUNT(*) считает количество студентов на каждом факультете.
• Наконец, HAVING отбрасывает все факультеты, где студентов 100 или меньше.

Результат:

Пример 2: Отделы с высокой средней зарплатой

Вы хотите найти только те отделы, где средняя зарплата сотрудников превышает 50,000. Предположим, у нас есть таблица employees:

Запрос:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Результат:

Обратите внимание: HAVING работает с результатами, которые были рассчитаны после GROUP BY.

Порядок выполнения WHERE, GROUP BY и HAVING

Фильтрация с помощью WHERE и HAVING выполняется на разных этапах. Чтобы лучше понять различие, давайте взглянем на пошаговый процесс запроса:

1. WHERE: фильтрация строк.

   На этом этапе обрабатываются все строки таблицы. Если строка не проходит условие WHERE, она вообще не попадает в дальнейшую обработку.

2. GROUP BY: группировка строк.

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

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

   К сгруппированным данным применяются агрегатные функции, такие как COUNT(), AVG(), SUM() и т.д.

4. HAVING: фильтрация групп.

   На этом этапе обрабатываются только результаты агрегатов. Условия HAVING применяются только к группам.

Особенности работы HAVING

Особенность 1: Работа с агрегатами

Главное отличие HAVING от WHERE — это работа с агрегатными функциями. Например:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

В этом запросе AVG(salary) нельзя использовать внутри WHERE, так как WHERE обрабатывает строки до выполнения группировки. Запрос типа:

SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000
GROUP BY department;

вызовет ошибку: aggregate functions are not allowed in WHERE.

Особенность 2: Фильтрация без группировки

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

SELECT AVG(salary) AS avg_salary
FROM employees
HAVING AVG(salary) > 50000;

Практический пример

Допустим, у нас есть магазин и таблица продаж sales:

Запрос: найти товары с общим объемом продаж больше 500.

SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(sales_amount) > 500;

Результат:

Типичные ошибки

Использование агрегатов в WHERE:

Например:

SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000
GROUP BY department;

Ошибка: агрегатные функции нельзя использовать в WHERE.

Ошибки с NULL:

Если данные содержат NULL, фильтрация может давать неожиданные результаты. Например:

SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 0;

Если столбец salary содержит только NULL, результат может быть нулевым или пустым.

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

2

Задача

SQL SELF, 8 уровень, 1 лекция

Недоступна

Фильтрация студентов по факультетам с количеством больше двух

Фильтрация студентов по факультетам с количеством больше двух

Открыть

2

Задача

SQL SELF, 8 уровень, 1 лекция

Недоступна

Фильтрация продуктов по общей сумме продаж

Фильтрация продуктов по общей сумме продаж

Открыть