Агрегация, GROUP BY и HAVING: вопросы для собеседования (часть 3)
GROUP BY, HAVING, COUNT, SUM, AVG — агрегатные функции встречаются практически в каждой SQL-задаче на собеседовании. Интервьюеры проверяют, понимаете ли вы разницу между WHERE и HAVING, умеете ли группировать по нескольким полям и фильтровать по результатам агрегации. Без уверенного владения этим блоком невозможно решить ни одну аналитическую задачу на SQL.
Вопросы 11–15 из 32
11Есть таблица `orders(user_id, amount)`. Какой запрос посчитает сумму всех заказов по каждому пользователю?
A`SELECT user_id, SUM(amount) FROM orders;`: агрегат по всей таблице без разбивки по столбцу `user_id`
B`SELECT user_id, amount FROM orders GROUP BY user_id, amount;`: группировка по двум столбцам без агрегата выручки
C`SELECT SUM(user_id), amount FROM orders GROUP BY amount;`: сумма идентификаторов в разрезе значения суммы заказа
D`SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;`: сумма заказов по каждому пользователю отдельно
Ответ: Для агрегатов по группам нужно явно указывать неагрегированные колонки в `GROUP BY`.
При использовании агрегатных функций и обычных колонок в `SELECT` все неагрегированные колонки должны быть перечислены в `GROUP BY`. Для суммы по пользователю используем запрос `SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;`. Без `GROUP BY` сумма посчитается по всей таблице, а группировка по `amount` не даст разбивки по пользователю.
Подробный разбор → 12Вы хотите вывести только те категории товаров, где суммарные продажи больше 10000. Таблица `sales` с колонками `category` и `amount`. Какой запрос корректен?
A`SELECT category, SUM(amount) FROM sales WHERE SUM(amount) > 10000 GROUP BY category;`
B`SELECT category, SUM(amount) FROM sales GROUP BY category HAVING SUM(amount) > 10000;`
C`SELECT category, amount FROM sales GROUP BY category HAVING amount > 10000;`
D`SELECT category, SUM(amount) FROM sales HAVING SUM(amount) > 10000;`
Ответ: Условия по агрегатам (`SUM`, `COUNT` и др.) задаются в `HAVING` после `GROUP BY`.
`WHERE` фильтрует отдельные строки до группировки и не работает с агрегатами вроде `SUM`. Для условия по сумме нужна `HAVING` после `GROUP BY`: `SELECT category, SUM(amount) FROM sales GROUP BY category HAVING SUM(amount) > 10000;`. Без `GROUP BY` ссылка на `category` рядом с агрегатом некорректна, а условие на `amount` без агрегата сравнивает построчные значения, а не суммы.
Подробный разбор → 13Два выражения: `SUM(CASE WHEN status='paid' THEN amount END)` и `SUM(amount) FILTER (WHERE status='paid')`. Что верно?
AТолько `FILTER` учитывает строки, где условие ложно, а `SUM(CASE WHEN ...)` всегда исключает их полностью
BТолько `SUM(CASE WHEN ...)` подходит для запросов с `GROUP BY`, а `FILTER` работает только с оконными функциями
CРезультаты разные: `FILTER` суммирует значения, а `SUM(CASE WHEN ...)` считает количество совпадений с условием
DОба выражения дают одинаковый результат: суммируют `amount` только по строкам, где `status = 'paid'`
Ответ: Оба выражения семантически эквивалентны: суммируют `amount` только по строкам, где `status='paid'`.
`SUM(CASE WHEN cond THEN val END)` возвращает `NULL` для не совпавших строк (нет `ELSE`), а `SUM` игнорирует `NULL` — итог: суммируются только строки, где условие истинно. `SUM(val) FILTER (WHERE cond)` делает то же самое через более явный синтаксис. Разница лишь в стиле: `FILTER` — стандарт SQL:2003, читается чище; `CASE WHEN` — универсален для всех СУБД.
Подробный разбор → 14В PostgreSQL нужно посчитать сумму продаж только по активным клиентам, при этом не исключая остальных клиентов из итоговой выборки. Какой синтаксис подойдёт?
AНаписать `WHERE status = 'active'` перед `GROUP BY`, чтобы убрать неактивных клиентов из выборки
BДобавить `FILTER (WHERE status = 'active')` после агрегатной функции `SUM` в `SELECT`
CИспользовать `HAVING status = 'active'` после `GROUP BY` для фильтрации итоговых групп клиентов
DПоместить условие в `ON` при `JOIN` со вспомогательной таблицей статусов клиентов
Ответ: `FILTER (WHERE ...)` применяется к конкретной агрегатной функции и не влияет на остальные агрегаты в `SELECT`.
Синтаксис `aggregate_func(...) FILTER (WHERE condition)` — стандарт SQL:2003, реализованный в PostgreSQL. Он позволяет фильтровать строки для одного агрегата независимо от других: `SUM(amount) FILTER (WHERE status='active')` суммирует только активных клиентов, а `COUNT(*)` той же группы посчитает всех. Это чище, чем `SUM(CASE WHEN status='active' THEN amount END)`, и сразу читается. Условие в `WHERE` отфильтровало бы неактивных клиентов вообще, что не соответствует задаче.
Подробный разбор → 15Запрос: `SELECT dept, SUM(cost) FROM expenses GROUP BY dept HAVING SUM(cost) > (SELECT AVG(limit) FROM budgets)`. Что он вернёт?
AВсе отделы и их суммарные расходы, отсортированные по убыванию суммы в группе
BОтделы, у которых хотя бы одна статья расходов превышает средний норматив по таблице
CОтделы, у которых сумма расходов превышает максимальный норматив из таблицы `budgets`
DОтделы, у которых сумма расходов превышает средний норматив по таблице `budgets`
Ответ: Скалярный подзапрос в `HAVING` считает средний норматив, а сам `HAVING` оставляет отделы с суммой расходов выше этого порога.
В `HAVING` допустимы скалярные подзапросы, возвращающие одно значение. Здесь `SELECT AVG(limit) FROM budgets` выполняется один раз и даёт средний норматив по всей таблице. Внешний запрос группирует строки по столбцу отдела и оставляет только те группы, где сумма расходов превышает этот порог. Это стандартный приём, чтобы сравнить агрегат группы с агрегатом по другой таблице.
Подробный разбор →