Використання підзапитів у 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 відкривають тобі купу можливостей для аналізу даних на рівні агрегатів. Ти можеш фільтрувати групи за складними умовами, порівнювати результати між групами й створювати складні аналітичні запити. Вітаю, тепер ти готовий застосовувати ці знання в реальних проектах!