Что такое оконные функции в SQL

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

Синтаксис оконных функций

Оконные функции используют предложение OVER(), которое определяет разбиение (PARTITION BY) и порядок (ORDER BY).

SELECT id, department, salary,
       AVG(salary) OVER(PARTITION BY department) AS avg_department_salary
FROM employees;

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

1. Агрегатные функции

Оконные версии агрегатных функций вычисляют значения в пределах окна без группировки строк.

SELECT id, name, salary,
       SUM(salary) OVER() AS total_salary,
       AVG(salary) OVER(PARTITION BY department) AS avg_salary_per_department
FROM employees;

2. Функции ранжирования

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

SELECT id, name, department, salary,
       ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num,
       RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employees;

3. Функции смещения

• LAG(column, offset, default) – возвращает значение из предыдущей строки.
• LEAD(column, offset, default) – возвращает значение из следующей строки.

SELECT id, name, salary,
       LAG(salary, 1, 0) OVER(ORDER BY id) AS previous_salary,
       LEAD(salary, 1, 0) OVER(ORDER BY id) AS next_salary
FROM employees;

4. Кумулятивные функции

• CUME_DIST() – относительный ранг строки в наборе данных.
• PERCENT_RANK() – нормализованный ранг.

SELECT id, name, salary,
       CUME_DIST() OVER(ORDER BY salary) AS cume_dist,
       PERCENT_RANK() OVER(ORDER BY salary) AS percent_rank
FROM employees;