Zastosowanie subquery w HAVING do filtrowania zgrupowanych danych

Czasem nie wystarczy tylko pogrupować dane i przefiltrować wynik, ale trzeba to zrobić z dodatkową logiką — na przykład porównać średnią ocenę studentów w grupie z jakimś zewnętrznym kryterium. Wtedy na scenę wchodzi HAVING z subquery — mega narzędzie, które pozwala podejmować mądrzejsze decyzje już w samym zapytaniu SQL.

Przypominamy sobie HAVING

Skupmy się na subquery używanych razem z HAVING, żeby filtrować dane na poziomie wartości zagregowanych. Po co? Jeśli WHERE pozwala filtrować pojedyncze wiersze, to HAVING działa już na zgrupowanych danych — to inny poziom analizy, który daje ci więcej możliwości.

Zanim zanurzymy się w połączenie subquery i HAVING, przypomnijmy sobie, czym jest HAVING i czym różni się od WHERE.

• WHERE filtruje wiersze przed wykonaniem grupowania (GROUP BY).
• HAVING filtruje dane po agregacji, kiedy dane są już pogrupowane.

Wyobraź sobie, że analizujesz studentów i ich oceny. Dzięki WHERE możesz wykluczyć studentów z określonymi minimalnymi ocenami, a HAVING pozwoli ci wykluczyć całe grupy studentów na podstawie ich średniej lub maksymalnej oceny.

Przykład danych

Oto przykładowa tabela studentów:

Tabela students:

Przykład użycia HAVING (bez subquery)

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

Wynik:

Kierunek "History" nie znalazł się w wyniku, bo średnia ocena jest tam niższa niż 75. Proste, nie? Teraz dorzucimy trochę magii z subquery. W następnym przykładzie możemy np. filtrować względem ogólnej średniej dla wszystkich kierunków.

Subquery w HAVING

Subquery w HAVING — to świetna opcja na większą elastyczność przy filtrowaniu danych zagregowanych. Pozwalają porównywać agregaty, takie jak średnia ocena czy maksimum, z wyliczonymi wartościami z innych części bazy. Mówiąc prościej, możesz sprawdzić: "Czy nasz wynik jest lepszy niż średnia w całym uniwerku?"

Przykład: filtrowanie kierunków po średniej ocenie

Załóżmy, że chcemy znaleźć te kierunki, gdzie studenci uczą się lepiej niż reszta — czyli średnia ocena na kierunku jest wyższa niż średnia na całym uniwersytecie.

Oto nasze dane:

Tabela students:

Najpierw wyciągniemy średnią ocenę dla wszystkich studentów:

SELECT AVG(grade) AS university_avg
FROM students;

Teraz użyjemy subquery w HAVING:

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

Wynik:

Co tu się dzieje?

1. Subquery (SELECT AVG(grade) FROM students) wylicza ogólną średnią ocenę — w tym przypadku to 77.
2. Główne zapytanie grupuje studentów po kierunkach i liczy dla każdego średnią ocenę.
3. HAVING porównuje średnią ocenę kierunku z ogólną średnią i przepuszcza tylko te kierunki, gdzie wynik jest wyższy.

Porównanie użycia WHERE i HAVING

Żeby zrozumieć różnicę, wyobraź sobie, że chcesz wybrać tylko tych studentów, którzy mają ocenę wyższą niż średnia. To można zrobić tylko przez WHERE:

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

Wynik (jeśli użyć tabeli z poprzednich przykładów):

A jeśli chcesz zobaczyć, na których kierunkach średnia ocen studentów jest wyższa niż średnia na uniwerku, to bez HAVING się nie obejdzie — bo filtrujesz nie wiersze, tylko grupy:

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

Wynik:

W skrócie:

• WHERE działa na pojedynczych wierszach przed grupowaniem.
• HAVING filtruje grupy po tym, jak są zagregowane.

Przykład: praca z wieloma agregatami

Przeanalizujmy jeszcze jeden przypadek. Załóżmy, że mamy tabelę students, w której są dane o ocenach studentów i ich kierunkach:

Tabela students:

Teraz chcemy znaleźć kierunki, gdzie:

1. Średnia ocena studentów jest wyższa niż średnia na uniwerku.
2. Maksymalna ocena na kierunku jest większa niż 90.

Do tego napiszemy takie zapytanie:

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;

Co się dzieje w tym zapytaniu:

• AVG(grade) > (SELECT AVG(grade) FROM students) — sprawdzamy, czy kierunek jest średnio mocniejszy od reszty.
• MAX(grade) > 90 — czyli jest tam ktoś, kto świetnie zdał egzamin.

Wynik:

Kierunek "Math" okazał się jedynym, który jednocześnie ma średnią ocenę wyższą od ogólnej i wybitnego studenta z oceną powyżej 90.

Przykład: wybór grup z minimalnym odchyleniem

Załóżmy, że chcesz znaleźć grupy, gdzie różnica między maksymalną a minimalną oceną studentów jest mniejsza niż różnica na uniwerku ogólnie.

Oto tabela students, na której będziemy pracować:

Podzielmy zadanie na etapy:

1. Najpierw policzymy różnicę maksimum-minimum dla całego uniwerku:

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

2. Teraz stworzymy główne zapytanie i połączymy je z tym subquery:

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 );

Wynik zapytania:

Grupy "Physics" i "Math" miały bardziej stabilne oceny — ich rozrzut jest mniejszy niż na uniwerku ogólnie.

Optymalizacja zapytań z HAVING i subquery

Pamiętaj, że zagnieżdżone subquery mogą mocno wpływać na wydajność, szczególnie w dużych bazach danych. Kilka tipów:

Używaj indeksów. Jeśli subquery działa na kolumnie, która jest w WHERE albo JOIN, upewnij się, że ta kolumna ma indeks.

Unikaj przeładowania danymi. Jeśli subquery zwraca za dużo wyników pośrednich, rozbij je na etapy albo użyj tymczasowych tabel.

Profiluj zapytania przez EXPLAIN. Zawsze sprawdzaj, jak PostgreSQL wykonuje twoje zapytanie. Jeśli widzisz, że subquery odpala się wielokrotnie, pomyśl o optymalizacji.

Porównaj z CTE. W niektórych przypadkach użycie WITH (Common Table Expressions) może być szybsze i wygodniejsze do czytania. Ale o tym pogadamy w kolejnych wykładach :P

Kombinowanie subquery, HAVING i GROUP BY

Dzięki subquery w HAVING możesz budować bardziej złożone filtry, szczególnie gdy trzeba jednocześnie brać pod uwagę agregaty, średnie i inne metryki. To wszystko pomaga wyciągać ciekawe insighty z realnych danych.

Przykład: porównanie kierunków po średniej ocenie i liczbie studentów

Załóżmy, że chcesz wybrać kierunki, gdzie:

1. Średnia ocena jest wyższa niż średnia na uniwerku.
2. Liczba studentów jest większa niż na kierunku z najniższą średnią oceną.

Oto wyjściowa tabela students:

Zapytanie:

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
   );

To zapytanie pokazuje możliwości łączenia subquery w HAVING i GROUP BY do analizy według kilku kryteriów naraz. Wynik:

Kierunek History nie znalazł się w wyniku, bo ma najniższą średnią ocenę i najmniej studentów. Physics i Math — oba powyżej średniej i pod względem ocen, i liczby studentów.

Typowe błędy i jak ich unikać

Błąd z NULL. Jeśli dane zawierają NULL, subquery z HAVING mogą zwrócić nieoczekiwane wyniki. Używaj COALESCE, żeby to ogarnąć:

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

Nadmiarowe dane w subquery. Jeśli subquery zwraca za dużo wyników, odbije się to na wydajności. Zawsze doprecyzuj warunki subquery.

Złe zrozumienie kolejności wykonania. Pamiętaj, że HAVING wykonuje się po grupowaniu, a subquery mogą wykonać się przed głównym zapytaniem.

Brak indeksów. Jeśli kolumny używane w subquery nie mają indeksów, zapytanie będzie dużo wolniejsze.

Subquery w HAVING otwierają ci masę możliwości do analizy danych na poziomie agregatów. Możesz filtrować grupy po złożonych warunkach, porównywać wyniki między grupami i budować zaawansowane zapytania analityczne. Gratulacje, teraz jesteś gotowy, żeby używać tej wiedzy w prawdziwych projektach!