Вопрос с собеседования · Аналитика данных

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

JuniorSQL для аналитикаSQL
Ещё спрашивают так:
  • Чем ROW_NUMBER отличается от RANK и DENSE_RANK?
  • Как посчитать нарастающий итог в SQL?
  • Что делают LAG и LEAD?
Коротко

GROUP BY схлопывает группу в одну строку; оконная функция считает по группе строк (окну), сохраняя все строки. Окно задаёт OVER (PARTITION BY … ORDER BY … ROWS …). Основные: ранжирующие (ROW_NUMBER, RANK, DENSE_RANK), смещения (LAG, LEAD), агрегаты по окну (SUM, AVG, COUNT) — нарастающие итоги и скользящие средние.

  • ROW_NUMBER — 1, 2, 3, 4; RANK — 1, 2, 2, 4; DENSE_RANK — 1, 2, 2, 3
  • Окна вычисляются после WHERE и GROUP BY — фильтр по результату окна делают во внешнем запросе
  • SUM(x) OVER (ORDER BY d) без явной рамки берёт RANGE: строки с одинаковой датой суммируются вместе

А ещё В PostgreSQL и ClickHouse есть фильтр QUALIFY (ClickHouse) или подзапрос/CTE для отбора по окну

Подробно

SELECT
  user_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date)        AS order_no,
  LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date)     AS prev_order,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(amount) OVER (ORDER BY order_date
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS ma_7
FROM orders;

Части окна:

  • PARTITION BY — на какие независимые группы делить (как GROUP BY, но без схлопывания);
  • ORDER BY — порядок внутри окна, нужен ранжирующим функциям, LAG и нарастающим итогам;
  • рамка (ROWS / RANGE BETWEEN …) — какие строки относительно текущей входят в расчёт.

Ловушка рамки по умолчанию. При наличии ORDER BY рамка по умолчанию — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: все строки с той же датой считаются «текущими», и нарастающий итог прыгает сразу на сумму дня. Для построчного итога явно пишут ROWS.

Типичные задачи: номер заказа клиента, время с прошлой покупки (LAG), доля строки в сумме группы (amount / SUM(amount) OVER (PARTITION BY …)), топ-N в группе, скользящее среднее, разница с прошлым месяцем.

Что хотят услышать

  • Отличие окна от GROUP BY.
  • PARTITION BY, ORDER BY и рамка.
  • Различие ROW_NUMBER, RANK, DENSE_RANK.
  • LAG/LEAD и нарастающий итог.

Частые ошибки

  • Фильтровать по оконной функции в WHERE.
  • Не задавать рамку и получать нарастающий итог «скачками».

Проверьте себя

Один верный вариант. Подумайте, потом откройте ответ.

Показать варианты и ответ
  • ✗Оконная функция схлопывает группу в одну строку, как GROUP BY, но вычисляется раньше WHERE и может в нём использоваться
  • ✓GROUP BY схлопывает группу в строку, а оконная функция считает по окну, сохраняя все строки
  • ✗RANK и DENSE_RANK всегда дают одинаковый результат
  • ✗PARTITION BY в окне работает только вместе с GROUP BY

Что спросят дальше

Связанные вопросы

Партнёр платформы

Разбор собеседования с sotto

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

Разбор собеседования