Что такое оконные функции в базах данных?

Оконные функции (Window Functions) — это расширение SQL, которое позволяет выполнять вычисления над набором строк, связанных с текущей строкой, без свёртки этих строк в одну, как это делает GROUP BY. Каждая строка исходного набора сохраняется, и к ней добавляется результат вычисления.

Ключевые концепции и синтаксис:

Оконная функция вызывается с обязательным предложением OVER(), которое определяет "окно" — группу строк, над которой производится расчёт.

SELECT 
    EmployeeID,
    Department,
    Salary,
    -- Оконная функция: средняя зарплата по отделу
    AVG(Salary) OVER (PARTITION BY Department) AS AvgDeptSalary,
    -- Оконная функция: ранг зарплаты внутри отдела
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRankInDept
FROM Employees;

Компоненты предложения OVER():

1. PARTITION BY — разбивает весь набор данных на группы (партиции). Вычисления выполняются в рамках каждой партиции независимо. Аналог GROUP BY, но без свёртки строк.
2. ORDER BY — определяет порядок строк внутри партиции (или всего окна), что критично для ранжирующих функций (RANK, ROW_NUMBER) и функций доступа к смежным строкам (LAG, LEAD).
3. ROWS/RANGE BETWEEN — определяет кадр (frame) внутри партиции относительно текущей строки (например, «текущая строка и две предыдущие»).

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

• Агрегатные: SUM(), AVG(), COUNT(), MIN(), MAX(). Работают как обычные агрегаты, но над окном.
• Ранжирующие:
  • ROW_NUMBER() — последовательная нумерация строк в порядке, заданном ORDER BY.
  • RANK() — ранг с пропусками номеров при одинаковых значениях.
  • DENSE_RANK() — ранг без пропусков номеров.
• Функции смещения (доступа к соседним строкам):
  • LAG(column, offset) — возвращает значение из строки, находящейся на offset строк выше текущей.
  • LEAD(column, offset) — возвращает значение из строки, находящейся на offset строк ниже текущей.
  • FIRST_VALUE(column), LAST_VALUE(column) — возвращают первое/последнее значение в окне.

Практический пример анализа скользящего среднего:

SELECT 
    OrderDate,
    DailyRevenue,
    AVG(DailyRevenue) OVER (
        ORDER BY OrderDate 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- Кадр: 7 дней (текущий + 6 предыдущих)
    ) AS SevenDayMovingAvg
FROM DailySales
ORDER BY OrderDate;

Оконные функции — один из самых мощных инструментов SQL для сложного аналитического запроса без необходимости использования курсоров или обработки на стороне приложения.