Оконные функции SQL — примеры и объяснения

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

Что такое оконная функция?

Обычная агрегатная функция (SUM, COUNT) с GROUP BY возвращает одну строку на группу. Оконная функция вычисляет значение для каждой строки, используя «окно» — набор связанных строк.

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

Каждый сотрудник сохраняется в результате, но получает среднюю зарплату по своему отделу.

Синтаксис OVER()

функция() OVER (
  [PARTITION BY столбец]   -- разбиение на группы
  [ORDER BY столбец]       -- порядок внутри группы
  [ROWS/RANGE frame]       -- границы окна
)

ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER() — уникальный номер строки в окне:

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

RANK() — ранг с пропусками при одинаковых значениях (1, 2, 2, 4).

DENSE_RANK() — ранг без пропусков (1, 2, 2, 3).

LAG и LEAD — доступ к соседним строкам

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY date) AS growth
FROM daily_sales;

LAG(column, N) — значение N строк назад. LEAD(column, N) — N строк вперёд.

Накопительная сумма (Running Total)

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

Скользящее среднее (Moving Average)

SELECT date, revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

NTILE — разбиение на группы

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Разбивает строки на N равных групп. Полезно для перцентилей и квартилей.

FIRST_VALUE, LAST_VALUE

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS top_earner
FROM employees;

Практические задачи

• Топ-3 сотрудника по зарплате в каждом отделе — ROW_NUMBER + PARTITION BY
• Разница с предыдущим месяцем — LAG
• Накопительный процент — SUM OVER / SUM общий × 100
• Медиана — PERCENTILE_CONT(0.5) WITHIN GROUP