← вся теориятеория к собесу · SQL

GROUP BY и HAVING в SQL

Зачем это спрашивают

Агрегации - хлеб аналитики, и валят на них не на сложном, а на мелочах: NULL внутри AVG, целочисленное деление в конверсии, агрегат, попавший в WHERE. Умение посчитать несколько метрик с разными условиями одним запросом - маркер того, что SQL у тебя рабочий, а не курсовый.

Типовые формулировки: «конверсия по городам одним запросом», «сколько у нас пользователей?», «города, где заказов больше ста».

// Почти любая задача вида «посчитай метрику по группам» на собесе решается одной из трёх идиом с этих карточек. Стоит просто узнать их в лицо.

GROUP BY: пресс, который давит строки в группы

GROUP BY берёт строки, раскладывает их в кучки по значению ключа и заменяет каждую кучку одной строкой. Отсюда жёсткое ограничение, о которое все спотыкаются: в SELECT могут остаться только сами ключи группировки и агрегаты. Любая другая колонка - вопрос «а какое из десяти значений в кучке ты хочешь?», на который база отвечает ошибкой.

Фильтров два, и путать их дорого. WHERE работает ДО группировки и отсеивает исходные строки. HAVING работает ПОСЛЕ и отсеивает уже готовые группы. «Города, где заказов больше ста» - это HAVING, потому что число заказов появляется только после того, как кучки собраны. Агрегата в WHERE не бывает в принципе: на момент WHERE считать ещё нечего.

// Агрегат без GROUP BY тоже законен - тогда вся таблица считается одной группой и на выходе получается ровно одна строка. Это удобно для долей: агрегат по группе поделить на агрегат по всему набору.

агрегат: функция, которая превращает много строк в одно число: SUM, COUNT, AVG, MIN, MAX

Три разных COUNT и молчаливые пропуски

Возьми колонку со значениями 1, 1, 2 и NULL. COUNT(*) вернёт 4 - он считает строки и на содержимое не смотрит. COUNT(v) вернёт 3 - он считает заполненные значения и пропуски игнорирует. COUNT(DISTINCT v) вернёт 2 - он считает разные значения. Три функции, одна колонка, три разных числа.

Поэтому вопрос «сколько у нас пользователей» на собесе всегда требует встречного уточнения: строк в таблице, заполненных идентификаторов или уникальных людей. Тот, кто уточняет, выглядит взрослее того, кто сразу пишет COUNT(*).

Пропуски портят и среднее. AVG по значениям 10, 20 и NULL вернёт 15, а не 10: NULL просто выпал из знаменателя. Если бизнесу нужно среднее «в том числе по пустым», это либо SUM(col)/COUNT(*), либо COALESCE(col, 0) до агрегации - функция, подставляющая значение по умолчанию вместо пропуска.

// И отдельная мина - целочисленное деление. В Postgres 45/100 честно равно нулю, потому что оба числа целые и результат тоже обязан быть целым. Конверсия превращается в ноль, запрос не падает, отчёт уходит заказчику. Пиши 45*1.0/100 и получишь 0.45.

Условная агрегация: несколько метрик за один проход

Идиома, которая закрывает половину аналитических задач: метрику с условием считают внутри агрегата, а не отдельным запросом. SUM(CASE WHEN status='paid' THEN amount END) даст выручку только по оплаченным, потому что для остальных строк CASE вернёт NULL, а SUM пропуски игнорирует. В Postgres то же самое читается лучше через COUNT(*) FILTER (WHERE status='paid').

Доля и конверсия считаются тем же приёмом, но фокус тут стоит понять, а не запомнить. AVG(CASE WHEN условие THEN 1.0 ELSE 0 END) превращает условие в колонку из единиц и нулей. Среднее по такой колонке - это сумма единиц, делённая на общее число строк, то есть ровно доля строк, где условие выполнилось. Единица обязательно с точкой, иначе целочисленное деление вернёт ноль.

// DISTINCT - не замена GROUP BY: он убирает дубликаты из результата, а не строит группы и агрегаты. И если DISTINCT внезапно «починил» суммы, ты замаскировал размножение строк на джойне. Чинить надо джойн, а не результат.

GROUPING SETS / ROLLUP: несколько уровней группировки одним запросом: строки по городам и сразу общий итог, без UNION вручную

Как отвечать: «Одним запросом: заказы, оплаченные и конверсия в оплату по городам»

Группирую по городу и считаю всё условной агрегацией в один проход. COUNT(*) даёт все заказы, COUNT(*) FILTER (WHERE status = 'paid') - оплаченные, а конверсия - это AVG(CASE WHEN status = 'paid' THEN 1.0 ELSE 0 END): условие превращается в единицы и нули, среднее по ним и есть доля. Единица с точкой обязательна, иначе целочисленное деление молча вернёт ноль по всем городам. Если дальше нужны только города с конверсией ниже десяти процентов, фильтрую их в HAVING, а не в WHERE: это условие на агрегат, а WHERE отрабатывает раньше, чем агрегат вообще появится.

Паттерн одного прохода плюс две классические ловушки, обезвреженные прямо по ходу ответа, с объяснением почему. Это заметно сильнее, чем просто правильный запрос.

На чём валят

−AVG по колонке с пропусками: знаменатель только из заполненных, и «средний чек» получается не тот, что ждёт бизнес.
−WHERE COUNT(*) > 5 - агрегата в WHERE не бывает, это HAVING.
−45/100 = 0: целочисленное деление превращает конверсию в ноль без всякой ошибки.
−Ставить COUNT(DISTINCT …), чтобы «починить» цифры после джойна: это маскирует размножение строк и вдобавок тормозит.
−Отвечать «COUNT(*)» на вопрос «сколько у нас пользователей», не уточнив, что именно считаем.

Проверьте себя

Пять вопросов из банка по этой подтеме. Всего их 42, остальные разбираются в тренажёре.

#aggregation_groupby1 / 5
Нужна дневная выручка с построчной детализацией «доля заказа в дне» одним запросом. Какой подход канонический?
A)Сделать два отдельных запроса и соединить их уже в приложении
B)Коррелированный подзапрос в SELECT, вычисляемый на каждую строку
C)SUM(amount) OVER (PARTITION BY order_date) рядом с amount → доля
D)GROUP BY order_date, order_id — детализация одновременно по дню и по заказу
показать ответ и разбор
+C)SUM(amount) OVER (PARTITION BY order_date) рядом с amount → доля
// разбор: Оконный агрегат — ровно этот случай: агрегат группы без схлопывания строк. GROUP BY по (дате, заказу) даст сумму заказа, а не дня; коррелированный подзапрос семантически верен, но это O(n) подзапросов и хуже читается. Паттерн «строка + её группа» стоит выучить до автоматизма.
#aggregation_groupby2 / 5
Что делает GROUP BY в SQL?
A)Сортирует все строки итогового результата по возрастанию одной колонки
B)Удаляет из результата дублирующиеся строки таблицы
C)Фильтрует строки по условию ещё до выполнения агрегатных функций
D)Схлопывает строки с одним значением в группу под агрегаты (SUM/COUNT)
показать ответ и разбор
+D)Схлопывает строки с одним значением в группу под агрегаты (SUM/COUNT)
// разбор: GROUP BY собирает строки с одинаковыми значениями группирующих колонок в одну группу, по которой считаются агрегаты (COUNT, SUM, AVG). В результате — по одной строке на группу. Поэтому неагрегированную колонку, которой нет в GROUP BY, нельзя класть в SELECT: непонятно, какое из значений группы брать.
#aggregation_groupby3 / 5
Что делает ключевое слово DISTINCT?
A)Сортирует итоговые строки и нумерует их по порядку
B)Убирает из результата повторяющиеся строки, оставляя уникальные
C)Считает общее число строк в результирующей выборке
D)Оставляет те строки, где во всех колонках стоит NULL
показать ответ и разбор
+B)Убирает из результата повторяющиеся строки, оставляя уникальные
// разбор: DISTINCT возвращает только уникальные комбинации значений выбранных колонок, отбрасывая повторы. На больших данных он недёшев (требует сортировки/хеширования), а COUNT(DISTINCT col) в аналитике часто заменяют приближённым approx_count_distinct.
#aggregation_groupby4 / 5
В колонке bonus у части строк NULL. Чем COUNT(bonus), SUM(bonus) и AVG(bonus) отличаются в обращении с NULL?
A)Все три считают NULL как ноль и включают такие строки в расчёт наравне со всеми остальными строками таблицы
B)Все три при встрече хотя бы одного NULL в колонке возвращают NULL как итоговое значение всего агрегата сразу
C)Агрегаты игнорируют NULL: COUNT(bonus) их не считает, SUM/AVG суммируют/усредняют лишь ненулевые значения
D)NULL корректно обрабатывает COUNT(*), а SUM и AVG на данных с NULL падают с ошибкой
показать ответ и разбор
+C)Агрегаты игнорируют NULL: COUNT(bonus) их не считает, SUM/AVG суммируют/усредняют лишь ненулевые значения
// разбор: Стандартные агрегаты пропускают NULL: COUNT(bonus) считает только строки с ненулевым bonus (в отличие от COUNT(*), считающего все строки), SUM(bonus) суммирует ненулевые, AVG(bonus) делит сумму ненулевых на ИХ число, а не на общее. Отсюда ловушка: при наличии NULL AVG(bonus) ≠ SUM(bonus)/COUNT(*). NULL не считается нулём, не «заражает» весь агрегат, и ошибки тоже нет.
#aggregation_groupby5 / 5
Нужно одним запросом по каждому дню посчитать число заказов И отдельно число ОПЛАЧЕННЫХ заказов. Идиоматичный приём?
A)Два отдельных запроса с разным условием WHERE и последующее ручное объединение их результатов уже в коде приложения
B)HAVING status = 'paid' — он отфильтрует оплаченные заказы прямо внутри той же самой группировки по дню без потерь
C)DISTINCT по статусу — он сам разложит заказы на оплаченные и неоплаченные в две отдельные колонки результата запроса
D)Условная агрегация: SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) рядом с COUNT(*) в одном GROUP BY
показать ответ и разбор
+D)Условная агрегация: SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) рядом с COUNT(*) в одном GROUP BY
// разбор: Условная агрегация считает несколько срезов в одном проходе: COUNT(*) — все заказы дня, SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) (или COUNT(*) FILTER (WHERE ...) в Postgres) — только оплаченные, всё в одной группировке по дню. Это чище и быстрее двух запросов. HAVING фильтрует группы целиком (потеряете общий счётчик), а DISTINCT колонки не «разворачивает».

дальше

Теорию прочитали. Навык ставится повторением

В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, лимит только на количество вопросов в день.

Открыть тренажёр Вся тема: SQL