Применение подзапросов в HAVING для фильтрации агрегированных данных

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

Вспоминаем HAVING

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

Прежде чем мы погрузимся в сочетание подзапросов и HAVING, давайте освежим в памяти, что такое HAVING и как он отличается от WHERE.

• WHERE фильтрует строки до выполнения группировки (GROUP BY).
• HAVING фильтрует данные после агрегирования, когда данные уже сгруппированы.

Представьте, что вы анализируете студентов и их оценки. С помощью WHERE вы можете исключить студентов с определёнными минимальными оценками, а вот HAVING позволит вам исключить целые группы студентов на основе их среднего или максимального балла.

Пример данных

Вот таблица с примерами студентов:

Таблица students:

Пример использования HAVING (без подзапросов)

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > 75;

Результат:

Факультет "History" в выборку не попал, потому что средний балл по нему ниже 75. Всё просто, да? Теперь добавим немного магии с подзапросами. В следующем примере мы можем, например, фильтровать по сравнению с общей средней по всем факультетам.

Подзапросы в HAVING

Подзапросы в HAVING — это отличная возможность добавить гибкости при фильтрации агрегированных данных. Они позволяют сравнивать агрегаты, такие как средняя оценка или максимум, с вычисленными значениями из других частей базы. Проще говоря, можно проверить: "А наш результат лучше среднего по больнице?"

Пример: фильтрация факультетов по средней оценке

Допустим, мы хотим найти те факультеты, где студенты учатся лучше остальных — то есть средний балл по факультету выше, чем в среднем по университету.

Вот наши данные:

Таблица students:

Для начала получим среднюю оценку по всем студентам:

SELECT AVG(grade) AS university_avg
FROM students;

Теперь применим подзапрос в HAVING:

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

Результат:

Что здесь происходит?

1. Подзапрос (SELECT AVG(grade) FROM students) вычисляет общую среднюю оценку — в данном случае она равна 77.
2. Основной запрос группирует студентов по факультетам и считает для каждого средний балл.
3. HAVING сравнивает средний балл факультета с общей средней и пропускает только те факультеты, где результат выше.

Сравнение с использованием WHERE и HAVING

Чтобы понять разницу, представим, что вы хотите выбрать только тех студентов, у которых есть оценки выше среднего. Это можно сделать только с WHERE:

SELECT name, grade
FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

Результат (если взять таблицу из предыдущих примеров):

А вот если вы хотите посмотреть, на каких факультетах средний балл студентов выше среднего по университету, то без HAVING не обойтись — потому что вы фильтруете не строки, а группы:

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

Результат:

Коротко:

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

Пример: работа с несколькими агрегатами

Давайте разберём ещё один случай. Допустим, у нас есть таблица students, в которой хранятся данные об оценках студентов и их факультетах:

Таблица students:

Теперь мы хотим найти факультеты, где:

1. Средняя оценка студентов выше, чем в среднем по университету.
2. Максимальная оценка на факультете выше 90.

Для этого напишем такой запрос:

SELECT department, AVG(grade) AS avg_grade, MAX(grade) AS max_grade
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND MAX(grade) > 90;

Что происходит в этом запросе:

• AVG(grade) > (SELECT AVG(grade) FROM students) — проверяем, что факультет в среднем сильнее других.
• MAX(grade) > 90 — значит, там есть кто-то, кто блестяще сдал экзамен.

Результат:

Факультет "Math" оказался единственным, кто одновременно имеет средний балл выше общего и выдающегося студента с оценкой выше 90.

Пример: выборка групп с минимальным отклонением

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

Вот таблица students, с которой мы будем работать:

Разделим задачу на этапы:

1. Сначала вычислим разницу максимум-минимум по всему университету:

   SELECT MAX(grade) - MIN(grade) AS range_university
   FROM students;
   

2. Теперь создадим основной запрос и соединим его с этим подзапросом:

SELECT department, MAX(grade) - MIN(grade) AS range_department
FROM students
GROUP BY department
HAVING (MAX(grade) - MIN(grade)) < ( SELECT MAX(grade) - MIN(grade) FROM students );

Результат выполнения запроса:

Группы "Physics" и "Math" показали более стабильные оценки — у них разброс меньше, чем по университету в целом.

Оптимизация запросов с HAVING и подзапросами

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

Используйте индексы. Если подзапрос выполняется по столбцу, который участвует в WHERE или JOIN, убедитесь, что на этом столбце есть индекс.

Избегайте переполнения данных. Если подзапрос возвращает слишком много промежуточных результатов, разбейте его на этапы или используйте временные таблицы.

Профилируйте запросы с помощью EXPLAIN. Всегда проверяйте, как PostgreSQL выполняет ваш запрос. Если видите, что подзапрос выполняется многократно, подумайте о его оптимизации.

Сравните с CTE. В некоторых случаях использование WITH (Common Table Expressions) может быть быстрее и удобнее для чтения. Но о нем в следующих лекциях :P

Комбинирование подзапросов, HAVING и GROUP BY

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

Пример: сравнение факультетов по среднему баллу и числу студентов

Допустим, вы хотите выбрать факультеты, где:

1. Средний балл выше среднего по университету.
2. Количество студентов больше, чем на факультете с самой низкой средней оценкой.

Вот исходная таблица students:

Запрос:

SELECT department, AVG(grade) AS avg_grade, COUNT(*) AS student_count
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND COUNT(*) > (
       SELECT COUNT(*)
       FROM students
       GROUP BY department
       ORDER BY AVG(grade)
       LIMIT 1
   );

Этот запрос показывает возможности комбинирования подзапросов в HAVING и GROUP BY для анализа сразу по нескольким критериям. Результат:

Факультет History не попал в выборку, потому что у него самая низкая средняя оценка и наименьшее число студентов. Physics и Math — оба выше среднего и по баллам, и по численности.

Типичные ошибки и их предотвращение

Ошибка с NULL. Если данные содержат NULL, подзапросы с HAVING могут вернуть неожиданные результаты. Используйте COALESCE для обработки таких случаев:

SELECT AVG(grade)
FROM students 
WHERE grade IS NOT NULL;

Избыточные данные в подзапросе. Если подзапрос возвращает избыточный результат, это скажется на производительности. Всегда уточняйте условия подзапроса.

Неправильное понимание порядка исполнения. Помните, что HAVING выполняется после группировки, а подзапросы могут выполняться до основного запроса.

Отсутствие индексов. Если столбцы, участвующие в подзапросе, не индексированы, это значительно замедлит выполнение запроса.

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