GROUP BY и HAVING

В прошлом уроке агрегатные функции — COUNT, SUM, AVG и другие — сворачивали всю таблицу в одну итоговую строку. Это полезно, но часто нужно не одно число на всю базу, а отдельный результат для каждой категории товаров, каждого клиента или каждого месяца. Именно это делает GROUP BY: он разбивает строки на группы, и агрегатная функция вычисляется внутри каждой группы независимо.

GROUP BY: разбивка строк на группы и агрегация каждой группы

Когда вы добавляете GROUP BY column к запросу с агрегатной функцией, база данных сначала раскладывает все строки по «стопкам» — по уникальным значениям указанного столбца, — а затем применяет функцию к каждой стопке отдельно. Результат содержит ровно одну строку на группу.

Предположим, есть таблица products со столбцами title, category и price. Запрос:

SELECT category, COUNT(*) AS количество
FROM products
GROUP BY category;

вернёт столько строк, сколько уникальных значений есть в столбце category. Для каждого значения COUNT(*) считает, сколько товаров в эту группу попало.

Группировать можно и по нескольким столбцам — тогда группа формируется по уникальной комбинации значений. Запрос:

SELECT category, price, COUNT(*) AS количество
FROM products
GROUP BY category, price;

создаёт отдельную группу для каждой пары (category, price). Если два товара стоят одинаково и лежат в одной категории, они попадут в одну группу; если хотя бы одно значение отличается — в разные.

Теперь о главном ограничении. В SELECT рядом с агрегатными функциями разрешено указывать только те столбцы, которые перечислены в GROUP BY. Смысл простой: каждая группа содержит несколько строк, и у этих строк могут быть разные значения в «лишнем» столбце — базе данных нечего вернуть, кроме произвольного выбора.

SQLite в этой ситуации не выдаёт ошибку: он молча берёт значение из какой-то строки группы (так называемый bare column). Но это значение непредсказуемо — оно может быть первым, последним или любым другим в зависимости от внутреннего порядка хранения. Рассчитывать на него нельзя:

-- Опасный запрос: name не входит в GROUP BY
SELECT category, title, COUNT(*) AS количество
FROM products
GROUP BY category;

Здесь title для каждой категории вернётся непредсказуемым. Исправление зависит от вопроса. Добавьте столбец в GROUP BY, если он задаёт зерно результата; применяйте MIN или MAX только когда действительно нужен минимум или максимум; иначе уберите столбец из SELECT либо перестройте запрос.

HAVING: условие на агрегат и фиксированный порядок предложений запроса

После того как GROUP BY сформировал группы и вычислил агрегаты, встаёт вопрос: как оставить только те группы, которые удовлетворяют какому-то условию? Это задача HAVING.

Предположим, нужно найти только те категории товаров, в которых суммарная стоимость запасов превышает 10 000:

SELECT category, SUM(price) AS итого
FROM products
GROUP BY category
HAVING SUM(price) > 10000;

Важно понимать момент применения: HAVING видит результат уже вычисленного агрегата. Движок сначала строит все группы, считает SUM(price) для каждой, а потом проверяет условие. Группы, не прошедшие проверку, просто не попадают в итог.

В условии HAVING можно использовать любую агрегатную функцию — ту же, что в SELECT, или другую. Например, отобрать категории, где одновременно больше трёх товаров и средняя цена выше 500:

SELECT category, COUNT(*) AS количество, AVG(price) AS средняя_цена
FROM products
GROUP BY category
HAVING COUNT(*) > 3 AND AVG(price) > 500;

Теперь о порядке предложений — он фиксирован синтаксисом SQL и не подлежит перестановке:

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT

Если написать HAVING перед GROUP BY или ORDER BY перед HAVING, SQLite вернёт синтаксическую ошибку. Порядок отражает логику выполнения: сначала выбираем таблицу (FROM), фильтруем строки (WHERE), группируем (GROUP BY), фильтруем группы (HAVING), сортируем (ORDER BY) и ограничиваем количество (LIMIT). Зная этот порядок, вы всегда можете восстановить правильный синтаксис запроса любой сложности.

WHERE против HAVING: момент применения фильтра и совместное использование

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

WHERE применяется до группировки. Строки, не прошедшие условие, исчезают из набора данных до того, как GROUP BY успел их увидеть. Агрегатная функция даже не знает об их существовании.

HAVING применяется после группировки. К этому моменту все группы уже собраны и агрегаты вычислены. HAVING просто выбрасывает группы, которые не удовлетворяют условию.

Практически это означает: попытка написать WHERE COUNT(*) > 3 завершится ошибкой — в момент выполнения WHERE никаких агрегатов ещё не существует. Агрегатные функции в WHERE запрещены синтаксически.

Вот запрос, где оба предложения работают вместе:

SELECT category, COUNT(*) AS количество
FROM products
WHERE price > 100
GROUP BY category
HAVING COUNT(*) >= 3;

Что здесь происходит: WHERE price > 100 сначала отсекает все дешёвые товары — в группировку идут только строки, прошедшие фильтр. Затем GROUP BY category собирает оставшиеся строки по категориям. Наконец, HAVING COUNT(*) >= 3 оставляет только те категории, в которых после фильтрации по цене осталось хотя бы три товара.

Это принципиально иной результат, чем если бы WHERE отсутствовал: без него в подсчёт вошли бы и дешёвые товары. Предварительный фильтр может только сохранить или уменьшить COUNT(*) каждой категории, поэтому некоторые категории перестанут проходить порог >= 3, но новые из-за фильтра не появятся.

Практическое правило выбора: если условие можно проверить на уровне отдельной строки без агрегации — используйте WHERE; если условие требует знания итогового значения по группе — используйте HAVING. Фильтрация через WHERE ещё и эффективнее: чем меньше строк дойдёт до группировки, тем меньше работы у GROUP BY.

WHERE отбирает заказы, HAVING — группы

Нужно найти клиентов с оплаченной выручкой больше 100. У первого клиента два оплаченных заказа, у второго есть один оплаченный и один отменённый. Сначала исключим отмены, затем сгруппируем оставшиеся строки и проверим сумму каждой группы.

import sqlite3
with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE orders (customer TEXT, amount INTEGER, status TEXT)")
    db.executemany("INSERT INTO orders VALUES (?, ?, ?)", [("A",80,"paid"),("A",70,"paid"),("B",60,"paid"),("B",1000,"cancelled")])
    result = db.execute("SELECT customer, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY customer HAVING SUM(amount) > 100 ORDER BY customer").fetchall()
    print(result)
    assert result == [("A",150)]

Если убрать WHERE, отменённый заказ сделает клиента B лидером. Если перенести агрегатную проверку в WHERE, перепутаются стадии обработки. Перед оптимизацией запроса вручную посчитайте ожидаемый результат: хороший план выполнения не исправляет неправильное правило.

Продолжите разбор: Концепция оконной функции и OVER с PARTITION BY.