Исполняемые доки SQLite: GROUP BY и HAVING

Q: В чём разница между WHERE и HAVING в SQLite?

WHERE отсеивает отдельные строки до группировки, а HAVING работает уже после агрегации и фильтрует целые группы. То есть WHERE amount > 100 оставит только строки со значением больше 100, а HAVING SUM(amount) > 100 — только те группы, у которых сумма получилась больше 100. Агрегатные функции вроде COUNT или SUM в WHERE использовать нельзя — для этого как раз и придуман HAVING.

Q: Можно ли использовать HAVING без GROUP BY в SQLite?

Можно. Без GROUP BY SQLite считает весь результат одной большой группой, и HAVING фильтрует её целиком. На выходе вы получите либо одну строку, либо ни одной. На практике такой приём встречается редко: обычно если есть HAVING, то рядом стоит и GROUP BY.

Q: Как отфильтровать группы по COUNT в SQLite?

Агрегат пишем в HAVING, а не в WHERE. Например, запрос SELECT customerid, COUNT() FROM orders GROUP BY customerid HAVING COUNT() > 1 вернёт клиентов, у которых больше одного заказа. В SQLite внутри HAVING можно сослаться и на алиас столбца из списка SELECT — это удобно.

GROUP BY: как сгруппировать строки в «корзины»

Агрегатные функции SQL — COUNT, SUM, AVG — сворачивают множество строк в одно число. А GROUP BY позволяет делать это по категориям: одно число на каждого клиента, на каждый месяц, на каждый статус. Каждое уникальное значение (или комбинация значений) превращается в отдельную строку результата.

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;

Три клиента — три строки на выходе. Шесть исходных строк исчезли: они схлопнулись в корзинки по клиентам, и внутри каждой посчитались COUNT(*) и SUM(amount).

Логика тут простая. GROUP BY customer говорит: «считай все строки с одинаковым клиентом одной группой». А агрегатные функции дальше работают уже отдельно по каждой такой группе.

Что можно класть в список SELECT

Вот на этом моменте многие спотыкаются. Когда вы используете GROUP BY, каждая колонка в SELECT должна либо присутствовать в GROUP BY, либо быть обёрнута в агрегатную функцию. Иначе непонятно, какое именно значение брать — из какой строки группы его тянуть?

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

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

-- Это работает: region сгруппирован, amount агрегирован.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

Если написать SELECT region, rep, SUM(amount) с GROUP BY region, SQLite спокойно это выполнит (он более снисходителен, чем другие СУБД, которые такой запрос отвергнут), но значение rep будет выбрано из группы произвольно. На каждый регион вы получите по одному имени менеджера, причём без всякой гарантии, какое именно. На это полагаться нельзя — добавляйте в GROUP BY все неагрегированные столбцы, которые показываете.

HAVING — фильтрация групп после агрегации

WHERE отсеивает строки до группировки, а HAVING фильтрует уже сами группы после неё. В этом и состоит вся разница between WHERE и HAVING, и именно поэтому условие вроде COUNT(*) > 1 нельзя засунуть в WHERE — на момент его выполнения счётчика ещё попросту не существует.

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;

У Клео всего один заказ, поэтому её группа отсеивается. Остаются Ада и Борис. Условие проверяется по агрегированному значению каждой группы, а не по отдельным строкам.

В HAVING можно прямо обращаться к псевдонимам колонок из SELECT — SQLite это разрешает:

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;

Так читается заметно приятнее, чем повторять SUM(amount) внутри HAVING.

WHERE и HAVING: используем вместе

WHERE и HAVING — это не «или одно, или другое». WHERE отсеивает строки до группировки, а HAVING отбирает уже сами группы, которые попадут в результат. На практике в реальных запросах почти всегда работают оба сразу.

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;

Читаем сверху вниз в порядке выполнения:

WHERE status = 'paid' — сразу выкидываем возвраты.
GROUP BY customer — то, что осталось, раскладываем по клиентам.
SUM(amount) считается внутри каждой группы.
HAVING SUM(amount) > 75 — оставляем только те группы, что прошли порог.

В итоге остаются Борис (80 + 20 = 100) и Клео (200). У Ады единственный оплаченный заказ был на 50 — до порога не дотягивает.

Несколько условий и группировка по нескольким столбцам

В HAVING работают те же логические операторы, что и в WHERE — AND, OR, NOT. А ещё можно группировать сразу по нескольким столбцам, получая подгруппы:

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;

Каждая пара (region, quarter) — это отдельная группа. В HAVING мы требуем сразу два условия: сумма больше 100 и как минимум две сделки. Под оба условия подходят только ('North', 'Q1') и ('South', 'Q2').

Практический приём: поиск дубликатов

Связка GROUP BY ... HAVING COUNT(*) > 1 — это классический способ найти повторяющиеся значения в столбце:

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;

Всплывают два дубликата. Дальше обычно решают, что с этим делать: объединить аккаунты, навесить ограничение UNIQUE или просто почистить данные — но сам запрос для поиска всегда одной и той же формы.

HAVING без GROUP BY

Случай редкий, но вполне рабочий. Если GROUP BY нет, вся выборка считается одной большой группой, и HAVING фильтрует её целиком — либо получаете агрегаты, либо пустой результат:

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;

Единственная строка в результате появилась потому, что сумма равна 160. Поменяйте порог на > 200 — и запрос не вернёт ни одной строки. На практике HAVING почти всегда идёт в паре с GROUP BY, но полезно помнить, что сам язык этого не требует.

Коротко о главном

GROUP BY схлопывает строки в группы по ключу; агрегатные функции считаются внутри каждой группы.
Каждая неагрегированная колонка из SELECT должна присутствовать и в GROUP BY.
WHERE отсеивает строки до группировки, а HAVING — уже готовые группы после неё.
Агрегатам вроде COUNT(*) и SUM(...) место в HAVING, но никак не в WHERE.
HAVING спокойно принимает составные условия и умеет ссылаться на алиасы из SELECT.

Дальше: внешние ключи

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

Часто задаваемые вопросы

В чём разница между WHERE и HAVING в SQLite?

WHERE отсеивает отдельные строки до группировки, а HAVING работает уже после агрегации и фильтрует целые группы. То есть WHERE amount > 100 оставит только строки со значением больше 100, а HAVING SUM(amount) > 100 — только те группы, у которых сумма получилась больше 100. Агрегатные функции вроде COUNT или SUM в WHERE использовать нельзя — для этого как раз и придуман HAVING.

Можно ли использовать HAVING без GROUP BY в SQLite?

Можно. Без GROUP BY SQLite считает весь результат одной большой группой, и HAVING фильтрует её целиком. На выходе вы получите либо одну строку, либо ни одной. На практике такой приём встречается редко: обычно если есть HAVING, то рядом стоит и GROUP BY.

Как отфильтровать группы по COUNT в SQLite?

Агрегат пишем в HAVING, а не в WHERE. Например, запрос SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 вернёт клиентов, у которых больше одного заказа. В SQLite внутри HAVING можно сослаться и на алиас столбца из списка SELECT — это удобно.

Связанные концепции

GROUP BY и HAVING в SQLite: фильтрация групп