Чем оконная функция отличается от GROUP BY?

ЗнаниеJuniorЧасто

Проверила команда СопроводОбновлено

Короткий ответ

GROUP BY схлопывает строки: на выходе одна строка на группу, и в SELECT остаются только столбцы группировки и агрегаты. Оконная функция с OVER считает агрегат по группе строк, но каждая строка остается в результате со всеми столбцами. Поэтому окно берут, когда рядом с каждой строкой нужно число по ее группе: среднее, ранг, итог.

Почему этот вопрос задают

Задачи на топ-N, накопительный итог и сравнение с соседней строкой решаются окнами. Интервьюеру важно, чтобы вы сами выбирали между окном и группировкой под задачу.

Как рассуждать

  1. Начните с числа строк на выходе: группировка схлопывает их, окно сохраняет
  2. Объясните, что PARTITION BY делит строки на разделы так же, как GROUP BY на группы
  3. Покажите один и тот же avg() в двух запросах и сравните результат

Ответ с примером

GROUP BY собирает строки в группы и возвращает по одной строке на группу. Имя ученика в такой запрос уже не поставить: у группы много учеников. Оконная функция считает тот же агрегат по набору строк, который задает OVER, и оставляет каждую строку на месте. PARTITION BY внутри OVER делит строки на разделы, ORDER BY задает порядок для ранжирования и накопительных расчетов.

Детская школа программирования сравнивает оценки учеников со средним по курсу. Первый запрос вернул две строки, по одной на курс. Второй вернул все шесть оценок, и рядом с каждой стоит среднее по ее курсу: видно, что Боря отстает от группы Python на 7 баллов. Окна считаются после WHERE, GROUP BY и HAVING, поэтому в WHERE их не поставить.

SQL
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?

Похожие вопросы

ЗнаниеJuniorЧасто

Чем ROW_NUMBER отличается от RANK и DENSE_RANK?

Все три функции нумеруют строки окна в порядке из ORDER BY, а различаются на равных значениях. ROW_NUMBER дает каждой строке свой номер, даже при равенстве. RANK дает равным строкам одинаковый номер и пропускает следующие: 1, 2, 2, 4. DENSE_RANK тоже дает одинаковый номер, но без пропусков: 1, 2, 2, 3.

  • оконные функции
239
ЗнаниеSeniorИногда

Чем ROWS отличается от RANGE в рамке оконной функции и какая рамка стоит по умолчанию?

ROWS отсчитывает рамку в строках: 2 PRECEDING — две строки перед текущей. RANGE отсчитывает по значению столбца сортировки и включает все строки, равные текущей по ORDER BY. Если в OVER есть ORDER BY, по умолчанию стоит RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, без ORDER BY рамка — весь раздел.

  • оконные функции
  • рамка окна
187
ЗадачаMiddleЧасто

Выведите выручку по месяцам и ее изменение к предыдущему месяцу в процентах

Сначала сверните поездки в помесячную выручку, потом возьмите выручку прошлого месяца окном LAG(revenue) OVER (ORDER BY month). Изменение в процентах — 100.0 * (revenue - prev) / prev, знаменатель оберните в NULLIF(prev, 0). У первого месяца сравнивать не с чем, поэтому там остается NULL: ноль или 100% исказили бы график.

  • LAG
  • выручка
  • оконные функции
245
ЗадачаMiddleЧасто

Посчитайте накопительную выручку по дням месяца, включая дни без заказов

Соберите календарь дней через generate_series и присоедините к нему заказы через LEFT JOIN, чтобы пустые дни не пропали. Сверните заказы до одной строки на день через GROUP BY и coalesce(sum(o.amount), 0.00): без coalesce у пустого дня вместо нуля будет NULL. Поверх дневной выручки посчитайте sum(revenue) OVER (ORDER BY day): окно сложит все дни от начала периода до текущего.

  • оконные функции
  • generate_series
210

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Агрегаты с OVER, ранжирующие row_number(), rank() и dense_rank(), а также lag() и lead() для сравнения с соседней строкой. Этого хватает на большинство задач: топ-N в группе, накопительный итог, изменение к прошлому дню.

Если в результате нужна каждая строка и рядом с ней число по группе или по соседним строкам, это окно. Подсказки в формулировке: «для каждого», «по сравнению с предыдущим», «нарастающим итогом», «топ-3 в каждой категории».

На junior достаточно понимать результат. Если спросят, скажите, что PostgreSQL сортирует строки по PARTITION BY и ORDER BY окна и считает функцию за один проход. Поэтому индекс по этим столбцам иногда избавляет от сортировки.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

Назовите одно достижение, близкое к задачам новой работы, и расскажите его как историю: какая была проблема, что сделали лично вы и что изменилось в цифрах. Сильнее всего звучит результат, который держится и после вас: новый порядок работы, правило, схема. Если результат общий, прямо скажите, какая часть в нем ваша.

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

Назовите свое правило: что вы отдаете команде и что оставляете себе, и покажите его на одной задаче. Расскажите, кому и почему передали, какие полномочия и рамки дали, как проверяли без мелкого контроля. Закончите честным итогом: что получилось хуже или медленнее, чем у вас, и что вы выиграли.

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

Поговорите сначала с каждым по отдельности: что случилось, с чего началось и что мешает работать. Потом соберите обоих вместе и обсуждайте работу: кто за какие данные и шаги отвечает. За такой ссорой часто стоит размытая ответственность. Запишите договоренности, назовите новый срок для сорванного отчета и через пару недель проверьте, что все работает.

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227