Оконные функции

OVER и PARTITION BY, row_number, rank и dense_rank, lag и lead, нарастающий итог.

Этот блок — на базе «Компания»: отделы, сотрудники и история окладов. У сотрудника в salaries все оклады, какие у него были; действующий сейчас — запись, у которой to_date пустой (to_date IS NULL).

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

SELECT e.name, e.department_id, s.amount,
       round(avg(s.amount) OVER (PARTITION BY e.department_id)) AS dept_avg
FROM employees e
JOIN salaries s ON s.employee_id = e.id AND s.to_date IS NULL;

OVER (…) превращает функцию в оконную. PARTITION BY делит строки на окна — здесь по отделам, и средний оклад считается внутри своего отдела. Без PARTITION BY окно одно — весь результат.

Номера и места

ORDER BY внутри OVER задаёт порядок строк в окне. Три функции нумеруют строки по-разному:

Функция	Оклады 300, 300, 250
`row_number()`	1, 2, 3 — номера разные, кому из равных достанется 1, решает случай
`rank()`	1, 1, 3 — равным одно место, следующее — с пропуском
`dense_rank()`	1, 1, 2 — равным одно место, без пропусков

SELECT e.name, s.amount,
       rank() OVER (ORDER BY s.amount DESC) AS place
FROM employees e
JOIN salaries s ON s.employee_id = e.id AND s.to_date IS NULL
WHERE e.department_id = 5;

Соседние строки — lag и lead

lag(amount) берёт значение из предыдущей строки окна, lead(amount) — из следующей. У первой строки предыдущей нет — будет NULL.

SELECT employee_id, from_date, amount,
       lag(amount) OVER (PARTITION BY employee_id ORDER BY from_date) AS previous
FROM salaries
WHERE employee_id IN (3, 4);

Нарастающий итог

sum(…) OVER (ORDER BY …) складывает значения от первой строки окна до текущей; строки с одинаковым значением в ORDER BY складываются вместе. Оконная функция может работать и поверх группировки: сначала GROUP BY, потом окно по уже сгруппированным строкам — sum(count(*)) OVER (ORDER BY …).

Оконные функции вычисляются после WHERE, GROUP BY и HAVING, поэтому фильтровать по их результату в том же запросе нельзя: WHERE place = 1 упадёт с ошибкой. Запрос с окном заворачивают в CTE или подзапрос и фильтруют снаружи.

Оконные функции

Номера и места

Соседние строки — lag и lead

Нарастающий итог

Задачи