Агрегаты по группам без GROUP BY
Используйте коррелированный подзапрос, чтобы вычислить максимум группы рядом с подробными строками.
«Агрегаты по группам без GROUP BY» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.
Задача о деталях и агрегате
Типичный вопрос на собеседовании: «Покажите каждую строку вместе с агрегатом для её группы». Например, выведите каждого сотрудника и максимальную зарплату в его отделе в одной строке.
Обычный GROUP BY сворачивает строки, поэтому он не может сохранить детализацию по каждому сотруднику. Вам одновременно нужны подробные строки и число уровня группы.
Коррелированный подзапрос элегантно решает эту задачу: он вычисляет агрегат группы для каждой подробной строки, ничего не сворачивая.
Почему обычный GROUP BY здесь не подходит
Если написать SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, получится одна строка на отдел, а имена отдельных сотрудников будут потеряны.
Если добавить name в SELECT, но не добавить его в GROUP BY, СУБД выдаст классическую ошибку «столбец должен присутствовать в GROUP BY».
Собеседующий проверяет, понимаете ли вы, что GROUP BY уменьшает число строк. Чтобы сохранить подробные строки, вычислите агрегат другим способом.
Коррелированный подзапрос приходит на помощь
Поместите агрегат группы в список SELECT как коррелированный подзапрос. Для каждой строки сотрудника запускается внутреннее вычисление MAX, ограниченное отделом этого сотрудника.
Корреляция e2.dept_id = e1.dept_id связывает агрегат с нужной группой, а внешний запрос по-прежнему возвращает одну строку на сотрудника.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;Сравнение каждой строки со своей группой
После того как агрегат группы появился в запросе, можно сравнивать с ним каждую строку. Частый вопрос: «Найдите сотрудников, которые зарабатывают больше среднего в своём отделе».
Здесь коррелированный AVG находится в WHERE, поэтому каждый сотрудник сравнивается со средним значением в своём отделе.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Вычисление разницы с агрегатом группы
Можно также показать, насколько каждая строка отличается от агрегата своей группы. Вычитание коррелированного среднего даёт разницу для каждой строки.
Обратите внимание: один и тот же коррелированный подзапрос можно повторно использовать в нескольких выражениях SELECT; СУБД вычисляет его для каждой строки при каждом появлении.
SELECT e1.name,
e1.salary,
e1.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;Поиск самого высокооплачиваемого сотрудника в группе
Чтобы вернуть только самого высокооплачиваемого сотрудника в каждом отделе, сравните каждую зарплату с коррелированным MAX и оставьте совпадения.
Этот шаблон возвращает и строки с одинаковым результатом: если два сотрудника получают максимальную зарплату в отделе, появятся оба. Такая обработка одинаковых результатов часто становится дополнительным вопросом на собеседовании.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Альтернатива с оконной функцией
Современный SQL предлагает более удобный инструмент: оконные функции. MAX(salary) OVER (PARTITION BY dept_id) вычисляет агрегат группы, не сворачивая строки и не выполняя повторный коррелированный просмотр.
Собеседующие любят, когда вы можете предложить оба решения и объяснить, что вариант с оконной функцией обычно работает лучше, поскольку просматривает таблицу один раз.
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;Компромиссы между коррелированным подзапросом и оконной функцией
Оба подхода возвращают один и тот же результат, но отличаются по свойствам:
- Коррелированный подзапрос: переносим между СУБД, работает даже в очень старых системах, но вычисляется заново для каждой строки.
- Оконная функция: один проход, намного быстрее на больших таблицах, но требует поддержки оконных функций в SQL.
Скажите, что именно вы выбрали бы и почему. Для разового запроса к небольшой таблице подойдёт любой вариант, а для масштабной аналитики предпочитайте оконную функцию.
Пример: заказы выше среднего клиента
Примените этот шаблон к заказам. Покажите заказы, сумма которых превышает среднюю стоимость заказов соответствующего клиента.
Коррелированный AVG ограничен условием o2.customer_id = o1.customer_id, поэтому для каждого заказа используется личная базовая величина его клиента.
SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);Учитывайте случаи с NULL и пустыми группами
Если в группе всего одна строка, её среднее равно значению этой строки, поэтому условие salary > avg ложно и строка исключается. Упомяните этот крайний случай заранее.
Кроме того, зарплаты со значением NULL игнорируются функциями AVG и MAX, что соответствует правилам агрегатов SQL. Если все значения в группе равны NULL, агрегат равен NULL, а сравнения дают UNKNOWN и исключают строку. Умение предвидеть такие случаи отличает полный ответ от поверхностного.
Расчёт ранга внутри группы
Ранг строки внутри группы можно выразить с помощью коррелированного COUNT. Чтобы найти ранг зарплаты каждого сотрудника в его отделе, посчитайте, сколько коллег получают больше.
Ранг 1 означает самую высокую зарплату. Добавление 1 превращает количество сотрудников с более высокой зарплатой в позицию, начинающуюся с 1, а корреляция ограничивает расчёт отделом.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;Быстрая проверка
Выберите причину, по которой коррелированный подзапрос лучше обычного GROUP BY для этой задачи.
Итоги: агрегаты по группам без GROUP BY
Главное:
- Коррелированный подзапрос добавляет агрегат уровня группы к каждой подробной строке, не сворачивая их.
- Используйте его в SELECT, чтобы показать агрегат, или в WHERE, чтобы сравнить каждую строку со своей группой.
- Шаблон
= MAX(...)возвращает все строки с одинаковым максимальным значением. - Оконная функция с
PARTITION BYвыполняет ту же задачу за один проход и обычно лучше масштабируется.
Предложите оба решения и обоснуйте свой выбор на собеседовании.
Часто задаваемые вопросы
Урок «Агрегаты по группам без GROUP BY» бесплатный?
Да — полный текст урока «Агрегаты по группам без GROUP BY» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.
Чему я научусь в уроке «Агрегаты по группам без GROUP BY»?
Используйте коррелированный подзапрос, чтобы вычислить максимум группы рядом с подробными строками. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Interview Prep?
Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.
Сколько времени занимает урок «Агрегаты по группам без GROUP BY»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Interview Prep?
Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Анатомия коррелированного подзапроса
- Агрегаты по группам без GROUP BY
- Коррелированные EXISTS и NOT EXISTS
- Переписывание коррелированных подзапросов через JOIN