Агрегация, GROUP BY и HAVING: вопросы для собеседования (часть 6)
GROUP BY, HAVING, COUNT, SUM, AVG — агрегатные функции встречаются практически в каждой SQL-задаче на собеседовании. Интервьюеры проверяют, понимаете ли вы разницу между WHERE и HAVING, умеете ли группировать по нескольким полям и фильтровать по результатам агрегации. Без уверенного владения этим блоком невозможно решить ни одну аналитическую задачу на SQL.
Вопросы 26–30 из 32
26Что вернёт запрос `SELECT region, category, COUNT(*) FROM sales GROUP BY ROLLUP(region, category)`?
AСтроки по каждой паре, подытоги по каждому региону с `NULL` в `category` и общий итог с `NULL` в обоих столбцах
BТолько строки по каждой уникальной паре `region` и `category` — итоги не добавляются
CСтроки по каждой категории с `NULL` в `region` и общий итог без разбивки по регионам
DДекартово произведение всех регионов и категорий со значениями для каждой ячейки
Ответ: `ROLLUP(a, b)` генерирует три уровня группировки: `(a, b)`, `(a)` и `()`. Строки с итогами помечаются `NULL` в соответствующих столбцах.
`GROUP BY ROLLUP(a, b)` генерирует группировки: `(a, b)`, `(a)`, `()`. Для таблицы продаж: подсчёт по каждой паре регион-категория, подытог по каждому региону с `NULL` вместо `category`, и общий итог с `NULL` в обоих столбцах. Функция `GROUPING(col)` позволяет отличить синтетический `NULL` от реального `NULL` в данных.
27Нужно вычислить медианную зарплату по каждому отделу в одном запросе. В PostgreSQL нет функции `MEDIAN()`. Какой синтаксис корректен?
AИспользовать `AVG(salary)` с условием `WHERE salary BETWEEN MIN(salary) AND MAX(salary)`
BИспользовать `SELECT salary FROM employees ORDER BY salary LIMIT 1 OFFSET COUNT(*)/2` в подзапросе
CИспользовать `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)` — упорядоченную агрегатную функцию
DИспользовать `(MAX(salary) + MIN(salary)) / 2` — полусумма крайних значений группы
Ответ: `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)` — стандартная упорядоченная агрегатная функция для вычисления медианы, работает с `GROUP BY`.
`PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY col)` — упорядоченная агрегатная функция стандарта SQL:2003. При `fraction=0.5` возвращает медиану с линейной интерполяцией. `PERCENTILE_DISC(0.5)` возвращает ближайшее реальное значение без интерполяции. Обе работают с `GROUP BY`. `(MAX + MIN) / 2` — среднее крайних значений, что не равно медиане при несимметричном распределении.
28Нужно получить топ-3 продукта по выручке внутри каждой категории. Какой подход корректен в PostgreSQL?
AНаписать `GROUP BY category ORDER BY revenue DESC LIMIT 3` — лимит применится внутри каждой группы
BДобавить `HAVING RANK() <= 3` к обычному `GROUP BY`-запросу с сортировкой
CИспользовать `DISTINCT ON (category)` с `ORDER BY category, revenue DESC LIMIT 3`
DИспользовать `ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC)` в подзапросе, затем отфильтровать по номеру строки
Ответ: Паттерн «топ-N внутри группы» требует оконной функции `ROW_NUMBER() OVER (PARTITION BY ...)` с последующей фильтрацией по номеру строки во внешнем запросе.
`LIMIT` без оконной функции ограничивает весь результат запроса, а не каждую группу отдельно. `DISTINCT ON (category)` даёт только топ-1. `HAVING` не знает о позиции строки внутри группы. Правильный подход: в `CTE` или подзапросе присвоить `ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn`, затем во внешнем запросе написать `WHERE rn <= 3`. При ничьих можно использовать `RANK()` вместо `ROW_NUMBER()`.
29В `SELECT` определена колонка `CASE WHEN years_exp < 1 THEN 'Junior' WHEN years_exp < 5 THEN 'Middle' ELSE 'Senior' END AS level`. Как правильно сгруппировать по этому выражению в PostgreSQL?
AОбязательно повторить полное выражение `CASE WHEN` в `GROUP BY` — псевдонимы там запрещены
BВ PostgreSQL можно написать `GROUP BY level` — псевдоним из `SELECT` допустим в `GROUP BY`
CВынести выражение `CASE WHEN` в `WHERE` и группировать по результату фильтрации
DИспользовать `HAVING level = 'Junior'` вместо `GROUP BY` для условных выражений
Ответ: PostgreSQL (в отличие от стандарта SQL) разрешает использовать псевдонимы из `SELECT` в `GROUP BY` — можно написать `GROUP BY level`.
По стандарту SQL псевдоним из `SELECT` недоступен в `GROUP BY`, но PostgreSQL расширяет стандарт и позволяет это. Можно написать `GROUP BY level`, `GROUP BY 1` (порядковый номер) или повторить выражение `CASE WHEN` целиком. Самый читаемый вариант — использовать псевдоним. В MySQL это тоже разрешено, а в SQL Server и Oracle — нет.
30Нужно в одном запросе получить итоги отдельно по каждому городу, отдельно по каждому продукту и общий итог — три уровня без их совместной комбинации. Какое выражение даёт именно эти три уровня?
A`ROLLUP(city, product)` — даёт нужные три уровня через иерархическую свёртку
B`CUBE(city, product)` — перебирает все подмножества и включает нужные уровни
CОбычный `GROUP BY city, product` с двумя отдельными `UNION ALL`-блоками для итогов
D`GROUPING SETS((city), (product), ())` — явно перечисляет три нужных уровня агрегации
Ответ: `GROUP BY GROUPING SETS((city),(product),())` явно перечисляет три нужных уровня: по городу, по продукту и общий итог — без лишних комбинаций.
`GROUPING SETS` позволяет задать точный список комбинаций группировки. `ROLLUP(city, product)` генерирует `(city, product)`, `(city)`, `()` — три уровня с лишней совместной комбинацией. `CUBE(city, product)` генерирует все четыре: `(city,product)`, `(city)`, `(product)`, `()` — с лишней парой. `GROUPING SETS((city),(product),())` — единственный точный способ получить ровно нужные три уровня без лишних строк.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram