GROUP BY i HAVING w SQLite: filtrowanie zagregowanych wyników

GROUP BY zwija wiersze w grupy

Funkcje agregujące, takie jak COUNT, SUM i AVG, sprowadzają wiele wierszy do jednej liczby. GROUP BY pozwala zrobić to dla każdej kategorii: jedna liczba na klienta, na miesiąc, na status. Każda unikalna wartość (lub kombinacja wartości) staje się jednym wierszem wyniku.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer;

Trzech klientów, trzy wiersze na wyjściu. Sześciu oryginalnych wierszy już nie ma: zostały zwinięte w grupy dla poszczególnych klientów, a COUNT(*) i SUM(amount) policzono w każdej z nich.

Model myślowy: GROUP BY customer mówi „traktuj wszystkie wiersze z tym samym klientem jako jedną grupę”. Agregaty działają potem na każdej grupie osobno.

Co można umieścić na liście SELECT

Na tym ludzie często się potykają. Przy GROUP BY każda kolumna na liście SELECT musi być albo w klauzuli GROUP BY, albo wewnątrz funkcji agregującej. W przeciwnym razie wartość jest niejednoznaczna: z którego wiersza grupy miałaby pochodzić?

CREATE TABLE sales (
    region TEXT,
    rep TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Ada', 100),
    ('North', 'Boris', 200),
    ('South', 'Cleo', 150);

-- To działa: region jest grupowany, a amount agregowany.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

Gdyby napisać SELECT region, rep, SUM(amount) z GROUP BY region, SQLite bez oporu by to wykonał (jest pobłażliwy tam, gdzie inne bazy to odrzucają), ale rep zostałby wybrany z grupy dowolnie. Dostałbyś jedno nazwisko przedstawiciela na region bez gwarancji, które. Nie polegaj na tym: grupuj po każdej niezagregowanej kolumnie, którą wyświetlasz.

HAVING filtruje grupy po agregacji

WHERE filtruje wiersze przed grupowaniem. HAVING filtruje grupy po grupowaniu. To cała różnica i dlatego nie możesz umieścić COUNT(*) > 1 w klauzuli WHERE: w chwili, gdy działa WHERE, liczność jeszcze nie istnieje.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada', 50), ('Ada', 30),
    ('Boris', 80), ('Boris', 20), ('Boris', 15),
    ('Cleo', 200);

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

Cleo złożyła tylko jedno zamówienie, więc jej grupa zostaje odfiltrowana. Zostają Ada i Boris. Warunek jest sprawdzany na zagregowanej wartości każdej grupy, a nie na pojedynczych wierszach.

W HAVING możesz bezpośrednio odwoływać się do aliasów kolumn z listy SELECT, SQLite na to pozwala:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING total >= 100;

Często czyta się to lepiej niż powtarzanie SUM(amount) w klauzuli HAVING.

WHERE a HAVING: używaj obu razem

Te dwie klauzule nie wykluczają się. WHERE zawęża, które wiersze biorą udział w grupowaniu, a HAVING zawęża, które grupy trafiają do wyniku. Większość prawdziwych zapytań używa obu.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 50, 'paid'),
    ('Ada', 30, 'refunded'),
    ('Boris', 80, 'paid'),
    ('Boris', 20, 'paid'),
    ('Cleo', 200, 'paid'),
    ('Cleo', 50, 'refunded');

SELECT customer, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 75;

Czytaj od góry do dołu w kolejności wykonania:

1. WHERE status = 'paid': całkowicie odrzuć zwrócone zamówienia.
2. GROUP BY customer: pogrupuj pozostałe wiersze według klienta.
3. SUM(amount) jest liczone dla każdej grupy.
4. HAVING SUM(amount) > 75: zostaw tylko grupy, które spełniają warunek.

Boris (80 + 20 = 100) i Cleo (200) przechodzą dalej. Jedyne opłacone zamówienie Ady to 50, co nie spełnia progu.

Wiele warunków i wiele kolumn grupujących

HAVING przyjmuje te same operatory logiczne co WHERE (AND, OR, NOT), a grupować możesz po więcej niż jednej kolumnie, żeby dostać podgrupy:

CREATE TABLE sales (
    region TEXT,
    quarter TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Q1', 100), ('North', 'Q1', 50),
    ('North', 'Q2', 300),
    ('South', 'Q1', 80),
    ('South', 'Q2', 120), ('South', 'Q2', 60);

SELECT region, quarter, SUM(amount) AS total, COUNT(*) AS deals
FROM sales
GROUP BY region, quarter
HAVING SUM(amount) > 100 AND COUNT(*) >= 2;

Każda para (region, quarter) to osobna grupa. Klauzula HAVING wymaga zarówno sumy powyżej 100, jak i co najmniej dwóch transakcji. Kwalifikują się tylko ('North', 'Q1') i ('South', 'Q2').

Praktyczny wzorzec: szukanie duplikatów

Zapytanie GROUP BY ... HAVING COUNT(*) > 1 to standardowy sposób na znalezienie powtarzających się wartości w kolumnie:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO users (email) VALUES
    ('ada@example.com'),
    ('boris@example.com'),
    ('ada@example.com'),
    ('cleo@example.com'),
    ('boris@example.com');

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Pojawiają się dwa duplikaty. Dalej zwykle decydujesz, czy scalić konta, dodać ograniczenie UNIQUE, czy wyczyścić dane, ale zapytanie wykrywające ma za każdym razem ten sam kształt.

HAVING bez GROUP BY

To nietypowe, ale poprawne. Bez GROUP BY cały zbiór wyników jest traktowany jako jedna grupa, a HAVING filtruje go w całości: dostajesz albo wszystkie zagregowane wartości, albo nic:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (50), (30), (80);

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM orders
HAVING SUM(amount) > 100;

Jedyny wiersz wyniku się pojawia, bo suma wynosi 160. Zmień próg na > 200, a zapytanie nie zwróci żadnego wiersza. W praktyce prawie zawsze łączysz HAVING z GROUP BY, ale dobrze wiedzieć, że język tego nie wymaga.

Krótkie podsumowanie

• GROUP BY zwija wiersze w grupy według klucza, a agregaty działają wewnątrz każdej grupy.
• Każda niezagregowana kolumna w SELECT powinna pojawić się w GROUP BY.
• WHERE filtruje wiersze przed grupowaniem, HAVING filtruje grupy po nim.
• Agregaty takie jak COUNT(*) i SUM(...) należą do HAVING, nigdy do WHERE.
• HAVING przyjmuje złożone warunki i może odwoływać się do aliasów z SELECT.

Dalej: klucze obce

Agregowanie jednej tabeli jest przydatne, ale większość prawdziwych schematów rozkłada dane na wiele tabel: zamówienia tu, klienci tam, produkty jeszcze gdzie indziej. Klucze obce to sposób na połączenie tych tabel tak, żeby relacje pozostały spójne. To temat następnego rozdziału.