Агрегация, GROUP BY и HAVING: вопросы для собеседования (часть 5)
GROUP BY, HAVING, COUNT, SUM, AVG — агрегатные функции встречаются практически в каждой SQL-задаче на собеседовании. Интервьюеры проверяют, понимаете ли вы разницу между WHERE и HAVING, умеете ли группировать по нескольким полям и фильтровать по результатам агрегации. Без уверенного владения этим блоком невозможно решить ни одну аналитическую задачу на SQL.
Вопросы 21–25 из 32
21Запрос фильтрует по `created_at > '2024-01-01'` и `SUM(amount) > 1000`. Как правильно расставить эти условия?
AУсловие `created_at > '2024-01-01'` — в `WHERE`, а `SUM(amount) > 1000` — в `HAVING`
BОба условия можно поставить в `HAVING` — оптимизатор сам перенесёт их в `WHERE`
CОба условия поставить в `WHERE` — `HAVING` нужен только для строковых фильтров
DУсловие `SUM(amount) > 1000` — в `WHERE`, а `created_at > '2024-01-01'` — в `HAVING`
Ответ: Неагрегированное условие `created_at` ставят в `WHERE` — оно уменьшает данные до группировки. Условие на агрегат `SUM(amount)` можно разместить только в `HAVING`.
Логический порядок: `WHERE` → `GROUP BY` → `HAVING`. Условие на не-агрегированный столбец в `WHERE` отсекает строки до группировки — меньше данных обрабатывается. Условие `SUM(amount) > 1000` невозможно вынести в `WHERE`, так как агрегат ещё не вычислен на этом этапе — оно должно быть в `HAVING`. Размещение агрегатного условия в `WHERE` приведёт к синтаксической ошибке.
22Запрос: `SELECT dept, STRING_AGG(name, ', ') FROM employees GROUP BY dept`. Что вернёт `STRING_AGG` для каждого отдела?
AМассив объектов с именами сотрудников в формате PostgreSQL-массива
BСтроку с именами сотрудников отдела, объединёнными через запятую
CЧисло уникальных сотрудников в каждом отделе в текстовом формате
DСлучайное имя одного сотрудника из каждого отдела в виде строки
Ответ: `STRING_AGG(expr, delimiter)` конкатенирует все значения группы в одну строку, разделяя их указанным разделителем.
`STRING_AGG(col, sep)` — агрегатная функция, которая склеивает строковые значения группы через разделитель `sep`. Порядок можно задать через `STRING_AGG(col, sep ORDER BY sort_col)`. Аналог в других СУБД: `GROUP_CONCAT` в MySQL, `LISTAGG` в Oracle. `ARRAY_AGG` делает то же самое, но возвращает PostgreSQL-массив, а не текстовую строку.
23Запрос: `SELECT dept, name, MAX(salary) FROM employees GROUP BY dept`. Что произойдёт в PostgreSQL?
AЗапрос выполнится, и `name` будет первым значением из каждой группы в алфавитном порядке
BЗапрос выполнится, но `name` будет случайным значением из каждой группы
CЗапрос завершится ошибкой: `name` не входит в `GROUP BY` и не обёрнут агрегатной функцией
DЗапрос выполнится, и `name` будет последним вставленным значением в каждой группе
Ответ: PostgreSQL выдаст ошибку: каждый столбец в `SELECT` должен либо входить в `GROUP BY`, либо быть обёрнут агрегатом — `name` не соответствует ни одному условию.
Стандарт SQL и PostgreSQL требуют, чтобы в `SELECT`-списке `GROUP BY`-запроса присутствовали только столбцы из `GROUP BY`, агрегатные функции или выражения, функционально зависимые от ключа группировки. `name` не в `GROUP BY dept`, поэтому PostgreSQL выдаст ошибку. MySQL в режиме без `ONLY_FULL_GROUP_BY` допустит запрос, но вернёт произвольное значение — недетерминированный результат.
24Запрос использует `ARRAY_AGG(product_name)` без `ORDER BY` внутри функции. Что верно о порядке элементов в массиве?
AПорядок элементов в массиве совпадает с порядком первичного ключа таблицы
BПорядок детерминирован: он совпадает с порядком физической вставки строк
CПорядок задаётся через `ORDER BY` во внешнем `SELECT` и автоматически применяется к `ARRAY_AGG`
DБез явного `ORDER BY` внутри `ARRAY_AGG` порядок элементов не гарантирован
Ответ: Без `ORDER BY` внутри `ARRAY_AGG(... ORDER BY ...)` порядок элементов не определён стандартом и может меняться от запуска к запуску.
Порядок строк внутри группы без явной сортировки зависит от плана выполнения, параллелизма и физического расположения данных. Стандарт SQL не гарантирует порядок без `ORDER BY`. Чтобы получить стабильный результат, нужно писать `ARRAY_AGG(product_name ORDER BY product_name)` или `ARRAY_AGG(product_name ORDER BY created_at)`. `ORDER BY` во внешнем `SELECT` упорядочивает строки результата, но не элементы внутри агрегата.
25Запрос: `SELECT dept, SUM(salary), RANK() OVER(ORDER BY SUM(salary) DESC) FROM employees GROUP BY dept`. Когда сработает оконная функция `RANK()`?
AОконная функция применится к исходным строкам до группировки — окно видит все строки таблицы
BОконная функция вычисляется после `GROUP BY` и видит по одной строке на группу
CЗапрос завершится ошибкой — оконные функции несовместимы с `GROUP BY` в одном запросе
DОконная функция и `GROUP BY` выполнятся параллельно, и результаты будут объединены
Ответ: Оконные функции вычисляются после `GROUP BY` и `HAVING` — `RANK()` видит уже сгруппированные строки, по одной на каждый отдел.
Логический порядок выполнения SQL: `FROM` → `WHERE` → `GROUP BY` → `HAVING` → оконные функции → `SELECT` → `ORDER BY`. Поэтому `RANK() OVER(ORDER BY SUM(salary) DESC)` получает на вход результат группировки — по одной строке на отдел с вычисленной суммой — и ранжирует эти агрегированные строки. Оконная функция не может «заглянуть» в исходные строки внутри группы.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram