Чем оконная функция отличается от GROUP BY?
Проверила команда Сопровод · Обновлено
Короткий ответ
GROUP BY схлопывает строки: на выходе одна строка на группу, и в SELECT остаются только столбцы группировки и агрегаты. Оконная функция с OVER считает агрегат по группе строк, но каждая строка остается в результате со всеми столбцами. Поэтому окно берут, когда рядом с каждой строкой нужно число по ее группе: среднее, ранг, итог.
Почему этот вопрос задают
Задачи на топ-N, накопительный итог и сравнение с соседней строкой решаются окнами. Интервьюеру важно, чтобы вы сами выбирали между окном и группировкой под задачу.
Как рассуждать
- Начните с числа строк на выходе: группировка схлопывает их, окно сохраняет
- Объясните, что
PARTITION BYделит строки на разделы так же, какGROUP BYна группы - Покажите один и тот же
avg()в двух запросах и сравните результат
Ответ с примером
GROUP BY собирает строки в группы и возвращает по одной строке на группу. Имя ученика в такой запрос уже не поставить: у группы много учеников. Оконная функция считает тот же агрегат по набору строк, который задает OVER, и оставляет каждую строку на месте. PARTITION BY внутри OVER делит строки на разделы, ORDER BY задает порядок для ранжирования и накопительных расчетов.
Детская школа программирования сравнивает оценки учеников со средним по курсу. Первый запрос вернул две строки, по одной на курс. Второй вернул все шесть оценок, и рядом с каждой стоит среднее по ее курсу: видно, что Боря отстает от группы Python на 7 баллов. Окна считаются после WHERE, GROUP BY и HAVING, поэтому в WHERE их не поставить.
SELECT course, round(avg(score), 1) AS avg_score
FROM scores
GROUP BY course
ORDER BY course;
SELECT student, course, score,
round(avg(score) OVER (PARTITION BY course), 1) AS course_avg
FROM scores
ORDER BY course, student;course | avg_score ---------+----------- Python | 85.0 Scratch | 84.0 (2 rows) student | course | score | course_avg ---------+---------+-------+------------ Аня | Python | 92 | 85.0 Боря | Python | 78 | 85.0 Вика | Python | 85 | 85.0 Гоша | Scratch | 70 | 84.0 Даша | Scratch | 88 | 84.0 Егор | Scratch | 94 | 84.0 (6 rows)
Оконная функция и GROUP BY: в чем разница
| Свойство | GROUP BY | Оконная функция |
|---|---|---|
| Строк на выходе | одна на группу | столько же, сколько на входе |
| Столбцы в SELECT | только группировка и агрегаты | любые столбцы строки |
| Функции | агрегатные | агрегатные, ROW_NUMBER, RANK, LAG, LEAD |
| Фильтр по результату | HAVING | во внешнем запросе |
Как ответить сильнее
Что добавит опытный кандидат
Что спросят следом
Доступно по подписке
Приемы опытных и готовые ответы
Частые ошибки
«Оконная функция — та же группировка, только удобнее»
Скажите, что окно строки не схлопывает: агрегат считается по группе строк, но каждая строка остается в результате со своими столбцами
«
count(*) OVER ()вернет одну строку с числом учеников»Объясните, что пустое окно охватывает все строки: число появится в каждой из шести строк, и сами строки останутся
«
ORDER BYвнутриOVERвлияет только на порядок вывода»Поправьте:
ORDER BYв окне задает порядок расчета и рамку, сортировку результата задает внешнийORDER BY«Окно и
GROUP BYв одном запросе использовать нельзя»Покажите, что можно: окно считается поверх сгруппированных строк, например
rank() OVER (ORDER BY sum(amount) DESC)
Сигналы для интервьюера
Сильный ответ
Показывает разницу через число строк на выходе и сам приводит задачу, где без окна пришлось бы соединять таблицу с собой
Тревожный сигнал
Пишет оконную функцию по шаблону, но не может сказать, почему ее нельзя поставить в WHERE
Красный флаг
Не знает row_number() и rank() и не может решить задачу «топ-3 в каждом курсе» без подсказки
Как еще могут спросить
- В чем разница между оконными функциями и группировкой?
- Зачем нужны оконные функции, если есть
GROUP BY? - Чем
PARTITION BYотличается отGROUP BY? - Что делает конструкция
OVERв SQL?











