Что такое оконные функции в SQL и как работает OVER (PARTITION BY)?

Оконные функции (window functions) выполняют вычисление по набору строк, связанному с текущей строкой, — по так называемому «окну». Главное отличие от обычной агрегации: оконная функция не схлопывает строки. Каждая исходная строка остаётся в результате, а функция добавляет к ней рассчитанное значение.

Сравните:

-- GROUP BY: на выходе ОДНА строка на отдел
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Оконная функция: ВСЕ строки сохраняются, рядом — средняя по отделу
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Второй запрос показывает каждого сотрудника и тут же среднюю по его отделу — без потери детализации. С GROUP BY так не сделать без join обратно.

Разберём синтаксис OVER (PARTITION BY ... ORDER BY ... ):

• OVER — обязательное ключевое слово, делающее функцию оконной и задающее окно.
• PARTITION BY — делит строки на группы (партиции), внутри каждой функция считается независимо. По смыслу похоже на GROUP BY, но строки не объединяются. Без него — всё одно окно.
• ORDER BY (внутри OVER) — упорядочивает строки в окне; нужен для функций, зависящих от порядка (ROW_NUMBER, RANK, LAG, нарастающие суммы).

Категории оконных функций:

1. Ранжирующие: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n).
2. Агрегатные в роли оконных: SUM(), AVG(), COUNT(), MIN(), MAX() с OVER.
3. Смещения: LAG(), LEAD() — доступ к предыдущей/следующей строке; FIRST_VALUE(), LAST_VALUE().

Пример нарастающего итога (running total) — здесь важен ORDER BY внутри окна:

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

Зачем всё это: оконные функции решают задачи, которые на чистом GROUP BY либо невозможны, либо требуют громоздких self-join и подзапросов — топ-N по группе, ранжирование, скользящие средние, сравнение строки с соседними, доля от итога.