Агрегация, 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`, если в столбце `amount` встретится хотя бы одно значение `NULL`.
BФункция `SUM` пропустит `NULL` и вернёт сумму непустых строк, а `AVG` разделит её на число непустых значений `amount`.
CОбе функции `SUM` и `AVG` заменят `NULL` на `0` в столбце `amount` и автоматически включат такие строки в расчёт.
DФункция `SUM` пропустит `NULL` и вернёт сумму непустых строк, а `AVG` разделит её на общее число строк, включая `NULL`.
Ответ: Обе функции игнорируют `NULL`: `SUM` вернёт `60`, а `AVG` разделит `60` на `3` и вернёт `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Уникальные `user_id` считаются отдельно внутри каждой группы по `country` и независимо для разных стран
BРезультат совпадает с `COUNT(user_id)` в каждой группе при наличии `GROUP BY country` в запросе
CСочетание `COUNT(DISTINCT user_id)` и `GROUP BY` в одном запросе требует подзапроса по таблице
DСчитает уникальных пользователей по всей таблице целиком, без учёта разбивки по столбцу `country`
Ответ: `COUNT(DISTINCT col)` внутри `GROUP BY` считает уникальные значения столбца отдельно в каждой группе, а не по всей таблице.
`COUNT(DISTINCT expr)` всегда работает в границах текущей группы. При группировке по стране уникальные `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` или внутри агрегатной функции по стандарту SQL
CБудет автоматически добавлен `GROUP BY user_id, created_at`, и группировка пройдёт по всей паре столбцов разом
DЗначение `created_at` во всех итоговых строках будет заменено на `NULL`, чтобы сделать запрос валидным
Ответ: Все неагрегированные колонки из `SELECT` должны входить в `GROUP BY`.
В стандартном SQL любая колонка, указанная в списке `SELECT` и не обёрнутая агрегатной функцией (`COUNT`, `SUM` и т.д.), должна быть перечислена в `GROUP BY`. Иначе запрос считается некорректным и завершается ошибкой. Некоторые СУБД (например, MySQL в нестрогом режиме) могут вернуть произвольное значение из группы, но это нестандартное поведение. Автоматическое добавление колонок в `GROUP BY` или замена на `NULL` стандартом не предусмотрены.
Подробный разбор → 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` сам по себе дедуплицирует строки заказов перед вычислением агрегата `SUM`
BСумма меньше реальной: `JOIN` отбрасывает заказы, у которых нет связанных строк в `order_items`
CСумма завышена: одна строка заказа дублируется по числу позиций и складывается несколько раз подряд
DСумма точная: `SUM` пропускает повторяющиеся значения внутри одной группы при сложении автоматически
Ответ: При `JOIN` «один ко многим» строка заказа дублируется по числу позиций, и `SUM(o.amount)` завышает итог.
Это классическая ошибка агрегации с `JOIN`. Если у заказа 3 позиции, `JOIN` порождает 3 строки с одинаковым `o.amount`, и `SUM` сложит сумму трижды — итог утроится. Решения: агрегировать `order_items` в подзапросе или `CTE` до `JOIN`; использовать `SUM(DISTINCT o.amount)` с осторожностью; или применять `COUNT(DISTINCT o.id)` для подсчёта заказов. Сама `GROUP BY` не дедуплицирует строки — она только группирует уже существующие.
Подробный разбор →