Агрегация, 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Только строки по парам `region` и `category` без подытогов и общего итога: итоги нужно считать отдельным `UNION ALL`
BСтроки по каждой `category` с `NULL` в столбце `region` и общий итог: разбивка по отдельным регионам отсутствует
CДекартово произведение `region` и `category` с `COUNT(*)` в каждой ячейке независимо от наличия данных
DСтроки по парам `region` и `category`, подытоги по регионам с `NULL` в `category` и общий итог с `NULL` в обоих столбцах
Ответ: `ROLLUP(a, b)` генерирует три уровня группировки: `(a, b)`, `(a)` и `()`. Строки с итогами помечаются `NULL` в соответствующих столбцах.
`GROUP BY ROLLUP(region, category)` генерирует группировки `(region, category)`, `(region)` и `()`. Для таблицы продаж получаем подсчёт по каждой паре регион-категория, подытог по каждому региону с `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. При значении 0.5 возвращает медиану с линейной интерполяцией между соседними значениями. Аналог `PERCENTILE_DISC(0.5)` возвращает ближайшее реальное значение без интерполяции. Обе работают вместе с `GROUP BY`. Полусумма `MAX` и `MIN` равна медиане только в симметричном распределении, а вариант с `LIMIT/OFFSET` не работает внутри агрегации по отделам.
Подробный разбор → 28Нужно получить топ-3 продукта по выручке внутри каждой категории. Какой подход корректен в PostgreSQL?
A`GROUP BY category ORDER BY revenue DESC LIMIT 3`: ограничение по числу строк применится к каждой категории отдельно
B`HAVING RANK() <= 3` поверх обычной агрегации с `GROUP BY category` и сортировкой выручки по убыванию
C`DISTINCT ON (category)` с `ORDER BY category, revenue DESC LIMIT 3` для выбора трёх лучших товаров категории
D`ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC)` в подзапросе с фильтром по номеру `<= 3`
Ответ: Паттерн «топ-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`, так как псевдонимы из `SELECT` запрещены в `GROUP BY`
BВ PostgreSQL допустимо написать `GROUP BY level`: псевдоним из `SELECT` разрешён в `GROUP BY` как расширение стандарта
CПеренести выражение `CASE WHEN` в `WHERE` и группировать по результату фильтрации без повторного использования псевдонима
DЗаменить `GROUP BY` на `HAVING level = 'Junior'`, чтобы группировка работала с условными выражениями `CASE WHEN`
Ответ: PostgreSQL разрешает использовать псевдонимы из `SELECT` в `GROUP BY` — можно написать `GROUP BY level`.
По стандарту SQL псевдоним из `SELECT` недоступен в `GROUP BY`, но PostgreSQL расширяет стандарт и позволяет это. Можно написать `GROUP BY level`, `GROUP BY 1` (порядковый номер колонки) или повторить выражение `CASE WHEN` целиком. Самый читаемый вариант — использовать псевдоним. `WHERE` фильтрует строки до агрегации и не заменяет группировку, а `HAVING` отбирает уже сгруппированные строки и тоже не годится вместо `GROUP BY`.
Подробный разбор → 30Нужно одним запросом получить итоги отдельно по каждому городу, отдельно по каждому продукту и общий итог — три уровня без их совместной комбинации. Какое выражение даёт ровно эти три уровня?
A`GROUP BY ROLLUP(city, product)` иерархически от пары столбцов вниз до общего итога одной свёрткой
B`GROUP BY CUBE(city, product)` по всем подмножествам столбцов, включая совместную пару `(city, product)`
C`GROUP BY city, product UNION ALL` отдельные блоки с итогами по каждому из столбцов и общим
D`GROUP BY 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),())` — единственный точный способ получить ровно нужные три уровня без лишних строк.
Подробный разбор →