Агрегация, 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;`
B`SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;`
C`SELECT SUM(user_id), amount FROM orders GROUP BY amount;`
D`SELECT user_id, amount FROM orders GROUP BY user_id, amount;`
Ответ: Для агрегатов по группам нужно явно указывать колонки в `GROUP BY`.
При использовании агрегатных функций и обычных колонок в `SELECT` все неагрегированные колонки должны быть перечислены в `GROUP BY`. Для суммы по пользователю используем запрос `SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;`.
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` нельзя использовать с агрегатными функциями, она фильтрует отдельные строки до группировки. Для фильтрации групп по сумме продаж нужен `HAVING`: `SELECT category, SUM(amount) FROM sales GROUP BY category HAVING SUM(amount) > 10000;`.
13Два выражения: `SUM(CASE WHEN status='paid' THEN amount END)` и `SUM(amount) FILTER (WHERE status='paid')`. Что верно?
AТолько `FILTER` учитывает строки, где условие ложно — `CASE WHEN` всегда их игнорирует
BТолько `SUM(CASE WHEN ...)` подходит для `GROUP BY`-запросов, `FILTER` — только для оконных функций
CРезультаты различаются: `FILTER` суммирует строки, а `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')` после агрегатной функции в `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)`.
15Запрос: `SELECT dept, SUM(cost) FROM expenses GROUP BY dept HAVING SUM(cost) > (SELECT AVG(limit) FROM budgets)`. Что он вернёт?
AОтделы, суммарные расходы которых превышают среднее значение по таблице нормативов
BОтделы, суммарные расходы которых превышают максимальное значение по таблице нормативов
CВсе отделы с их расходами, отсортированные по убыванию суммы
DОтделы, в которых хотя бы одна статья расходов превышает средний норматив
Ответ: Подзапрос в `HAVING` вычисляет средний норматив по таблице `budgets`, а `HAVING` оставляет только те отделы, чья суммарная стоимость превышает это значение.
В `HAVING` допустимы скалярные подзапросы (возвращают одно значение). Здесь `SELECT AVG(limit) FROM budgets` выполняется один раз и возвращает одно число. Внешний запрос группирует по `dept` и оставляет группы, где `SUM(cost)` превышает этот порог. Это стандартный способ сравнить группу с внешним агрегатом.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram