Агрегация, GROUP BY и HAVING: вопросы для собеседования (часть 4)
GROUP BY, HAVING, COUNT, SUM, AVG — агрегатные функции встречаются практически в каждой SQL-задаче на собеседовании. Интервьюеры проверяют, понимаете ли вы разницу между WHERE и HAVING, умеете ли группировать по нескольким полям и фильтровать по результатам агрегации. Без уверенного владения этим блоком невозможно решить ни одну аналитическую задачу на SQL.
Вопросы 16–20 из 32
16В столбце `amount` значения `10`, `20`, `NULL`, `30`, `NULL`. Чему равны `SUM(amount)` и `AVG(amount)`?
AОбе функции `SUM` и `AVG` вернут `NULL`, если в столбце есть хотя бы одно пустое значение
BОбе функции пропустят `NULL`: `SUM` вернёт сумму не-`NULL` строк, `AVG` разделит её на число не-`NULL` строк
CОбе функции `SUM` и `AVG` заменят `NULL` на `0` и включат такие строки в расчёт автоматически
DФункция `SUM` вернёт сумму не-`NULL` строк, а `AVG` разделит её на общее число строк включая `NULL`
Ответ: Обе функции игнорируют `NULL`: `SUM` вернёт `60`, а `AVG` разделит `60` на `3` (число не-`NULL` строк) и вернёт `20`.
По стандарту SQL агрегатные функции `SUM`, `AVG`, `MIN`, `MAX` пропускают `NULL`-значения. `SUM` вернёт `10+20+30=60`. `AVG` разделит `60` на `3` (не на `5`) — вернёт `20`. Если нужно учитывать `NULL` как ноль, используют `AVG(COALESCE(amount, 0))` — тогда знаменатель будет `5`, а результат `12`.
17Нужно получить список пользователей, у которых суммарная сумма завершённых заказов больше 1000. Таблица `orders(user_id, amount, status)`, завершённые заказы имеют `status = 'done'`. Какой запрос корректен?
A`SELECT user_id, SUM(amount) FROM orders WHERE status = 'done' GROUP BY user_id HAVING SUM(amount) > 1000;`
B`SELECT user_id, SUM(amount) FROM orders GROUP BY user_id HAVING status = 'done' AND SUM(amount) > 1000;`
C`SELECT user_id, SUM(amount) FROM orders WHERE SUM(amount) > 1000 AND status = 'done' GROUP BY user_id;`
D`SELECT user_id, SUM(amount) FROM orders WHERE status = 'done' AND SUM(amount) > 1000 GROUP BY user_id;`
Ответ: `WHERE` фильтрует строки, `HAVING` — агрегированные группы.
Чтобы учесть только завершённые заказы, сначала фильтруем их в `WHERE status = 'done'`, затем группируем по пользователю и отбираем группы по сумме через `HAVING SUM(amount) > 1000`. Корректный шаблон: `SELECT user_id, SUM(amount) FROM orders WHERE status = 'done' GROUP BY user_id HAVING SUM(amount) > 1000;`.
18Запрос: `SELECT country, COUNT(DISTINCT user_id) FROM events GROUP BY country`. Таблица содержит повторяющиеся строки. Что посчитает функция?
AРазницы нет: `COUNT(DISTINCT user_id)` и `COUNT(user_id)` дают одинаковый результат при наличии `GROUP BY`
B`COUNT(DISTINCT user_id)` не работает внутри `GROUP BY` — нужен отдельный подзапрос
CВ каждой группе считаются уникальные `user_id` внутри этой группы, а не по всей таблице
D`COUNT(DISTINCT user_id)` с `GROUP BY` считает уникальных пользователей по всей таблице, игнорируя разбивку
Ответ: `COUNT(DISTINCT col)` внутри `GROUP BY` считает уникальные значения столбца в пределах каждой группы отдельно, а не по всей таблице.
`COUNT(DISTINCT expr)` всегда работает в границах текущей группы. При `GROUP BY country` уникальные `user_id` считаются отдельно для каждой страны. Если пользователь совершил события в двух странах, он будет засчитан в обеих группах — это ожидаемое поведение. Никакого ограничения на сочетание `DISTINCT` и `GROUP BY` нет.
19Что произойдёт в СУБД, строго следующей стандарту SQL, при выполнении запроса `SELECT user_id, created_at, COUNT(*) FROM orders GROUP BY user_id;`?
AЗапрос выполнится, и `created_at` будет произвольным значением из группы.
BЗапрос завершится ошибкой: `created_at` должен быть в `GROUP BY` или внутри агрегатной функции.
CБудет автоматически добавлен `GROUP BY user_id, created_at`.
D`created_at` во всех строках станет `NULL`.
Ответ: Все неагрегированные колонки из `SELECT` должны входить в `GROUP BY`.
В стандартном SQL любая колонка, указанная в списке `SELECT` и не обёрнутая агрегатной функцией (`COUNT`, `SUM` и т.д.), должна быть перечислена в `GROUP BY`. Иначе запрос считается некорректным и завершается ошибкой.
20Аналитик пишет: `SELECT o.user_id, SUM(o.amount) FROM orders o JOIN order_items i ON i.order_id = o.id GROUP BY o.user_id`. У одного заказа несколько позиций. Что произойдёт с суммой?
AСумма будет точной — `GROUP BY` автоматически дедуплицирует строки перед агрегацией
BСумма будет меньше реальной — `JOIN` отбрасывает заказы без связанных позиций
CСумма будет точной — `SUM` игнорирует дублирующиеся значения при сложении
DСумма будет завышена — один заказ дублируется для каждой связанной позиции
Ответ: При `JOIN` «один ко многим» строка заказа дублируется столько раз, сколько позиций у заказа. `SUM(o.amount)` посчитает сумму заказа несколько раз, завысив итог.
Это классическая ошибка агрегации с `JOIN`. Если у заказа 3 позиции, `JOIN` порождает 3 строки с одинаковым `o.amount`. `SUM` сложит все три — итог утроится. Решения: агрегировать `order_items` в подзапросе до `JOIN`; использовать `SUM(DISTINCT o.amount)` с осторожностью; или применять `COUNT(DISTINCT o.id)` для подсчёта заказов.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram