Оконные функции за 7 минут

Агрегат с GROUP BY сворачивает строки в одну. Оконная функция считает то же самое, не сворачивая: каждая строка остаётся и получает значение, посчитанное по её «окну» соседей.

SELECT name, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

Каждый сотрудник на месте, рядом средняя зарплата его отдела.

Из чего состоит OVER

func(...) OVER (
  PARTITION BY ...   -- на какие группы делить (как GROUP BY, но без сворачивания)
  ORDER BY ...       -- порядок внутри группы
  ROWS BETWEEN ...   -- рамка: какие строки группы учитывать
)

Пустой OVER () означает «вся выборка как одно окно».

Ранжирование

На зарплатах 300, 200, 200, 100 по убыванию:

salary	ROW_NUMBER	RANK	DENSE_RANK
300	1	1	1
200	2	2	2
200	3	2	2
100	4	4	3

• ROW_NUMBER уникальный номер, при равенстве порядок произвольный (добавляйте второй ключ сортировки).
• RANK одинаковый при равенстве, потом пропуск.
• DENSE_RANK одинаковый при равенстве, без пропусков.
• NTILE(4) делит строки на 4 примерно равные корзины.

Топ-N в каждой группе

Оконную функцию нельзя использовать в WHERE (она считается после WHERE, на шаге SELECT). Поэтому заворачиваем:

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, id) AS rn
  FROM employees e
) t
WHERE rn <= 2;

Нужны все с равной зарплатой на втором месте? Берите DENSE_RANK.

LAG и LEAD

Значение из предыдущей или следующей строки окна:

SELECT day, revenue,
       revenue - LAG(revenue) OVER (ORDER BY day) AS diff
FROM daily_revenue;

У первой строки LAG вернёт NULL (или значение по умолчанию: LAG(revenue, 1, 0)). FIRST_VALUE берёт первое значение окна.

Накопительные суммы и рамка

SUM(amount) OVER (ORDER BY day)

Это нарастающий итог. Почему? Если в OVER есть ORDER BY, рамка по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: от начала до текущей строки включая все строки с тем же значением ORDER BY. При дублях дат две строки получат одинаковую сумму. Чтобы считать строго построчно, пишите ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Скользящее среднее за 3 дня:

AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Ловушка LAST_VALUE: с рамкой по умолчанию «последняя» строка это текущая. Нужна рамка до UNBOUNDED FOLLOWING.

Именованное окно

SELECT ..., RANK() OVER w, SUM(x) OVER w
FROM t
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

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

• Оконная функция в WHERE или HAVING.
• ROW_NUMBER без однозначной сортировки и нестабильный результат.
• Нарастающий итог через RANGE по умолчанию при повторяющихся значениях.
• LAST_VALUE без явной рамки.

Что спросят на собесе

• Чем оконная функция отличается от GROUP BY.
• Разница ROW_NUMBER, RANK и DENSE_RANK.
• Как взять топ-3 по зарплате в каждом отделе.
• Как посчитать нарастающий итог и разницу с прошлым днём.
• Что такое рамка окна и какая она по умолчанию.