HAVING w SQL: filtrowanie grup po agregacji, przykłady i pułapki

HAVING filtruje grupy. WHERE decyduje, które wiersze w ogóle wejdą do liczenia, GROUP BY dzieli je na grupy, a HAVING patrzy na wynik agregacji i zostawia tylko te grupy, które spełniają warunek. Typowe pytania: którzy klienci wydali co najmniej 500, które kategorie mają przynajmniej dwóch kupujących, w które dni było ponad sto zamówień.

Sama składnia jest krótka. Kłopoty zaczynają się na granicach: wartość równa progowi, NULL w sumie, alias, który działa w jednym silniku, a w drugim kończy się błędem. Na tym się tu skupiam. Jeśli dopiero zaczynasz grupować dane, zajrzyj najpierw do hasła GROUP BY albo do artykułu Podstawy SQL: od pierwszej tabeli do wyniku, któremu można zaufać, gdzie HAVING pojawia się na tle całego zapytania.

Składnia i kolejność wykonania

SELECT kolumna_grupująca, AGREGAT(kolumna)
FROM tabela
WHERE warunek_na_wierszach
GROUP BY kolumna_grupująca
HAVING warunek_na_grupach
ORDER BY kolumna_grupująca;

W HAVING piszę warunek tak samo jak w WHERE: porównania, AND, OR, BETWEEN, IN. Różnica polega na tym, co mogę w nim sprawdzić. HAVING widzi kolumny z GROUP BY i wyniki agregatów, takich jak SUM czy COUNT. Agregat w HAVING nie musi przy tym występować w SELECT.

Kolejność, w jakiej piszę klauzule, różni się od kolejności, w jakiej baza logicznie je przetwarza:

1. FROM i JOIN: skąd biorę wiersze.
2. WHERE: które wiersze wchodzą do liczenia.
3. GROUP BY: jak dzielę je na grupy.
4. Agregaty: SUM, COUNT, AVG liczone osobno dla każdej grupy.
5. HAVING: które grupy zostają.
6. SELECT: co pokazuję i pod jakimi nazwami.
7. ORDER BY, a na końcu LIMIT.

To kolejność logiczna. Planista zapytań może fizycznie zrobić coś sprytniej, ale wynik musi wyglądać tak, jakby trzymał się tej listy. Wynikają z niej dwie zasady, które oszczędzają mi większość błędów. W WHERE nie ma jeszcze sum ani liczników, bo grupy nie istnieją. Alias z SELECT powstaje dopiero po HAVING, więc standard SQL nie pozwala go tam użyć. Wyjątki opisuję niżej.

Przykład: klienci, którzy wydali co najmniej 500

Pracuję na małej, syntetycznej tabeli zamówień. Kwoty są w całych jednostkach, żeby wyniki były czytelne. Dwa zamówienia Dana nie mają kwoty, a jedno zamówienie Bena jest anulowane. Oba szczegóły przydadzą się dalej.

-- example: fixture
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  category TEXT NOT NULL,
  amount INTEGER,
  status TEXT NOT NULL
);

INSERT INTO orders (order_id, customer, category, amount, status) VALUES
  (1, 'Ada', 'books', 120, 'paid'),
  (2, 'Ada', 'books', 80, 'paid'),
  (3, 'Ada', 'games', 300, 'paid'),
  (4, 'Ben', 'games', 500, 'paid'),
  (5, 'Ben', 'games', 150, 'cancelled'),
  (6, 'Cleo', 'books', 200, 'paid'),
  (7, 'Cleo', 'music', 350, 'paid'),
  (8, 'Dan', 'music', NULL, 'paid'),
  (9, 'Dan', 'music', NULL, 'paid'),
  (10, 'Eva', 'books', 40, 'paid');

Szukam klientów, których opłacone zamówienia dają razem co najmniej 500:

-- example: basic
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) >= 500
ORDER BY customer;

Ben ma w tabeli zamówienia na 650, ale 150 z tego to zamówienie anulowane. WHERE odrzucił ten wiersz, zanim cokolwiek zostało zsumowane, więc HAVING zobaczył już 500. Gdybym usunął linię z WHERE, Ben miałby 650. Tak sprawdzam, czy warunek stoi we właściwym miejscu. Status dotyczy pojedynczego zamówienia, więc trafia do WHERE. Próg 500 dotyczy sumy klienta, więc trafia do HAVING.

Dan i Eva wypadli z wyniku. Eva ma 40, a u Dana suma jest pusta. Do Dana wrócę przy NULL.

Wartość równa progowi

Ten sam warunek z ostrym znakiem:

-- example: strict
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 500
ORDER BY customer;

Ada i Ben mają dokładnie 500, więc > 500 ich odrzuca. Brzmi banalnie, ale w raportach to częsty powód rozjazdów: w wymaganiach ktoś pisze „powyżej 500”, a ktoś inny rozumie „od 500”. Zanim napiszę warunek, dopytuję, co ma się stać z wartością równą progowi. BETWEEN 100 AND 500 obejmuje oba końce przedziału, więc także zaliczyłby Adę i Bena.

Warunki łączę przez AND i OR jak w WHERE. Tu dokładam liczbę zamówień, której nawet nie pokazuję w wyniku:

-- example: multi
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) >= 500 AND COUNT(*) >= 2
ORDER BY customer;

Ben odpada, bo po odrzuceniu anulowanego zamówienia zostało mu jedno.

HAVING bez GROUP BY

HAVING nie wymaga GROUP BY. Bez niego cały zbiór po WHERE jest jedną grupą, a zapytanie zwraca jeden wiersz, gdy warunek jest spełniony, albo zero wierszy, gdy nie jest.

-- example: whole-table
SELECT COUNT(*) AS paid_orders, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
HAVING COUNT(*) >= 5;

Po zmianie progu na >= 10 dostanę pusty wynik. Bez HAVING takie zapytanie zawsze zwraca dokładnie jeden wiersz, nawet na pustej tabeli, gdzie COUNT(*) daje 0. Przydaje mi się to w kontrolach jakości: podsumowanie pojawia się tylko wtedy, gdy danych jest wystarczająco dużo.

Ten zapis działa w PostgreSQL, MySQL i SQL Server. SQLite dopuszcza HAVING bez GROUP BY od wersji 3.39.0 z 2022 roku, a starsze wersje zgłaszają błąd. Stąd w starszych poradnikach zdanie, że HAVING wymaga GROUP BY.

Alias w HAVING zależy od silnika

Kusi, żeby napisać krócej:

-- example: alias
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING total >= 500
ORDER BY customer;

SQLite 3.53.1 i MySQL 8.4 zwracają ten sam wynik co pierwszy przykład: Ada, Ben i Cleo. PostgreSQL 17 kończy na błędzie:

ERROR:  column "total" does not exist

SQL Server też tego nie przyjmie. Według dokumentacji Microsoft alias z SELECT jest dostępny dopiero w klauzulach przetwarzanych później, na przykład w ORDER BY. MySQL opisuje alias w HAVING wprost jako własne rozszerzenie standardu.

W kodzie, który ma działać w więcej niż jednym silniku, mam dwie drogi. Powtarzam wyrażenie z agregatem, jak w pierwszym przykładzie. Albo liczę sumę w podzapytaniu i filtruję alias zwykłym WHERE na zewnątrz:

-- example: portable
SELECT customer, total
FROM (
  SELECT customer, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer
) AS totals
WHERE total >= 500
ORDER BY customer;

Przy dłuższych obliczeniach wolę ten drugi wariant, zwykle zapisany jako CTE z WITH. Wyrażenie liczę raz, a warunek czyta się jak zdanie.

NULL, duplikaty i COUNT(DISTINCT)

Agregaty pomijają NULL. SUM(amount) dla Ady to zwykła suma, ale dla Dana, który ma same puste kwoty, wynikiem jest NULL, a nie 0. Porównanie z NULL nie daje ani prawdy, ani fałszu, więc HAVING odrzuca taką grupę. Widać to przy pytaniu o klientów z małą sumą:

-- example: null-sum
SELECT customer, SUM(amount) AS total, COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) < 100
ORDER BY customer;

Dan ma dwa opłacone zamówienia, a w wyniku go nie ma. Jeśli brak kwoty ma się liczyć jako zero, piszę HAVING COALESCE(SUM(amount), 0) < 100 (więcej w haśle COALESCE). Zwykle jednak najpierw sprawdzam, skąd się wzięły puste kwoty, bo zero i brak danych niosą inną informację. Pomaga w tym różnica między COUNT(*) a COUNT(kolumna): pierwsze liczy wiersze, drugie tylko wartości różne od NULL.

-- example: missing-amounts
SELECT customer, COUNT(*) AS orders, COUNT(amount) AS priced
FROM orders
GROUP BY customer
HAVING COUNT(amount) < COUNT(*)
ORDER BY customer;

Podobna pułapka czeka przy duplikatach. COUNT(*) >= 2 znaczy „co najmniej dwa wiersze”, a nie „co najmniej dwie różne rzeczy”. Gdy pytam, którzy klienci kupowali w co najmniej dwóch kategoriach, liczę różne wartości:

-- example: distinct-categories
SELECT customer, COUNT(*) AS orders, COUNT(DISTINCT category) AS categories
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING COUNT(DISTINCT category) >= 2
ORDER BY customer;

Z warunkiem HAVING COUNT(*) >= 2 do wyniku dołączyłby Dan: ma dwa zamówienia, ale oba w tej samej kategorii. COUNT(DISTINCT ...) również pomija NULL, więc pusta kategoria nie zostałaby policzona jako osobna wartość.

Częste błędy i kiedy wybrać coś innego

Agregat w WHERE. To najczęstszy błąd na początku:

-- example: aggregate-in-where
SELECT customer, SUM(amount) AS total
FROM orders
WHERE SUM(amount) >= 500
GROUP BY customer;

SQLite odpowiada misuse of aggregate: SUM(), a PostgreSQL aggregate functions are not allowed in WHERE. W chwili wykonywania WHERE grupy jeszcze nie istnieją, więc nie ma czego sumować. Warunek przenoszę do HAVING.

Warunek na pojedynczym wierszu w HAVING. HAVING status = 'paid' przy grupowaniu po kliencie wygląda niewinnie. PostgreSQL go odrzuci z komunikatem, że status musi być w GROUP BY albo w agregacie. SQLite go wykona i sprawdzi status jednego, dowolnie wybranego wiersza z grupy, więc wynik zależy od przypadku. Status sprawdzam w WHERE.

Warunek na kolumnie grupującej w HAVING. HAVING customer <> 'Eva' zadziała poprawnie wszędzie, ale wolę go w WHERE. Dokumentacja PostgreSQL zwraca uwagę, że wtedy baza nie musi grupować i liczyć wierszy, które i tak odpadną. A czytelnik od razu widzi, co wchodzi do analizy.

Potrzebuję pojedynczych wierszy, a nie grup. HAVING zwraca jeden wiersz na grupę. Jeśli chcę zobaczyć wszystkie zamówienia klientów, którzy wydali co najmniej 500, łączę tabelę z podzapytaniem z HAVING albo liczę sumę funkcją okna SUM(amount) OVER (PARTITION BY customer) i filtruję ją w zapytaniu zewnętrznym.

Krótkie ćwiczenie

Na tej samej tabeli znajdź kategorie, w których opłacone zamówienia złożyło co najmniej dwóch różnych klientów, a ich suma wynosi co najmniej 400. Jeśli wszystko gra, dostaniesz dwie kategorie. Potem zmień próg na > 440 i sprawdź, która kategoria wypadła i dlaczego. Na koniec usuń WHERE i zobacz, czy anulowane zamówienie Bena zmienia listę kategorii, czy tylko jedną z sum.