Агрегация, GROUP BY и HAVING: вопросы для собеседования (часть 2)
GROUP BY, HAVING, COUNT, SUM, AVG — агрегатные функции встречаются практически в каждой SQL-задаче на собеседовании. Интервьюеры проверяют, понимаете ли вы разницу между WHERE и HAVING, умеете ли группировать по нескольким полям и фильтровать по результатам агрегации. Без уверенного владения этим блоком невозможно решить ни одну аналитическую задачу на SQL.
Вопросы 6–10 из 32
6Нужно сгруппировать события по календарному месяцу в PostgreSQL, чтобы все дни одного месяца попали в одну группу. Какой подход правильный?
AНаписать `GROUP BY YEAR(event_time), MONTH(event_time)` — стандартный синтаксис для всех СУБД
BИспользовать `GROUP BY EXTRACT(month FROM event_time)` — извлекает номер месяца из даты
CНаписать `GROUP BY DATE_TRUNC('month', event_time)` — усекает временную метку до начала месяца
DПрименить `GROUP BY CAST(event_time AS DATE)` — обрезает время, оставляя только дату
Ответ: `DATE_TRUNC('month', ts)` усекает метку до `YYYY-MM-01 00:00:00` — все дни одного месяца принимают одно значение и попадают в одну группу.
`DATE_TRUNC(unit, timestamp)` возвращает метку времени, усечённую до указанной единицы. `DATE_TRUNC('month', ts)` превращает любую дату месяца в `YYYY-MM-01 00:00:00`. `EXTRACT(month FROM ts)` возвращает только номер месяца 1–12 и смешивает разные годы — январь 2023 и январь 2024 окажутся в одной группе. `CAST(ts AS DATE)` группирует по дням, а не месяцам.
Подробный разбор → 7Как `GROUP BY` обрабатывает значения `NULL` в столбце группировки?
AЗначения `NULL` игнорируются и не попадают ни в одну из групп результата
BКаждое значение `NULL` образует свою отдельную группу, как уникальное значение
CВсе строки со значением `NULL` попадают в одну общую группу, как равные
DЗапрос завершится ошибкой, если в столбце группировки встречаются значения `NULL`
Ответ: Все строки с `NULL` в столбце группировки попадают в одну общую группу.
При выполнении `GROUP BY` значения `NULL` считаются равными друг другу. Поэтому все строки, где значение колонки равно `NULL`, объединяются в одну группу, точно так же, как одинаковые ненулевые значения. Это важная особенность: в обычных сравнениях `NULL = NULL` даёт `UNKNOWN`, но `GROUP BY` ведёт себя иначе.
Подробный разбор → 8Нужно получить по каждому менеджеру число выполненных и число отменённых заказов в одной строке результата. Какой подход позволяет это сделать?
AОтфильтровать строки по статусу через условие в `WHERE` перед `GROUP BY`: одно из значений отбросится из выдачи
BСоздать отдельный подзапрос для каждого значения статуса и соединить результаты через `JOIN` по `manager_id`
CИспользовать отдельный `GROUP BY`-запрос для каждого значения статуса: получится две таблицы вместо одной строки
DУсловная агрегация: `SUM(CASE WHEN status='done' THEN 1 END)` и аналогичный аналог для отменённых внутри `GROUP BY`
Ответ: Условная агрегация через `SUM(CASE WHEN ... THEN 1 END)` внутри `GROUP BY` даёт две колонки в одной строке.
Условная агрегация через `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` или `COUNT(CASE WHEN ... THEN 1 END)` позволяет считать строки по нескольким условиям одновременно внутри одного `GROUP BY`-запроса. Это избавляет от необходимости писать несколько отдельных подзапросов или `JOIN`-ов к одной таблице. В PostgreSQL аналогичный результат даёт синтаксис `FILTER (WHERE ...)`.
Подробный разбор → 9Нужно получить количество заказов по паре (`user_id`, `status`) из таблицы `orders`. Какой запрос верный?
A`SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id;`
B`SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status;`
C`SELECT user_id, status, COUNT(*) FROM orders GROUP BY status, COUNT(*);`
D`SELECT user_id, status, COUNT(*) FROM orders GROUP BY 1;`
Ответ: При группировке по нескольким полям все поля без агрегата должны быть перечислены в `GROUP BY`.
Чтобы посчитать количество строк по комбинации значений, нужно указать все поля комбинации в `GROUP BY`. Запрос `SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status;` корректен. Если в `GROUP BY` указать только `user_id`, СУБД пожалуется на `status` как на не-агрегированное поле. Если попытаться сгруппировать по `COUNT(*)`, синтаксис вообще невалиден. Запись `GROUP BY 1` группирует только по первому полю и не даёт разрез по `status`.
Подробный разбор → 10Нужно посчитать сумму оплаченных заказов по каждому пользователю. В таблице `orders` есть поля `user_id`, `amount`, `status`. Какой запрос корректен и наиболее эффективен?
A`SELECT user_id, SUM(amount) FROM orders GROUP BY user_id HAVING status = 'paid';`
B`SELECT user_id, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY user_id;`
C`SELECT user_id, SUM(amount) FROM orders GROUP BY user_id WHERE status = 'paid';`
D`SELECT user_id, SUM(amount) FROM orders HAVING status = 'paid' GROUP BY user_id;`
Ответ: `WHERE` фильтрует строки до группировки, а `HAVING` — уже агрегированные группы.
Фильтрацию по обычному (не агрегированному) полю `status` лучше делать в `WHERE`, чтобы отбрасывать лишние строки до агрегации и не суммировать неоплаченные заказы. `HAVING` применяется уже к группам после `GROUP BY` и подходит для условий на агрегаты, например `HAVING SUM(amount) > 1000`. Варианты с `HAVING status = 'paid'` без агрегата формально могут отработать, но это менее эффективно и не отражает смысл оператора. Порядок `GROUP BY ... WHERE` синтаксически некорректен.
Подробный разбор →