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

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

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

• PARTITION BY: Разделяет результирующий набор на группы (партиции), внутри которых производятся вычисления. Аналог GROUP BY, но без агрегации.
• ORDER BY: Определяет логический порядок строк внутри каждой партиции.
• ROWS/RANGE BETWEEN: Определяет границы фрейма (подмножества строк внутри партиции) для вычислений относительно текущей строки.

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

1. Агрегатные: SUM(), AVG(), COUNT(), MIN(), MAX().
2. Ранжирующие: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
3. Функции смещения: LAG() (значение из предыдущей строки), LEAD() (значение из следующей строки), FIRST_VALUE(), LAST_VALUE().

Практические примеры:

-- 1. Ранжирование сотрудников по зарплате в каждом отделе
SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank_in_dept
FROM employees;

-- 2. Вычисление скользящего среднего дохода за последние 3 дня
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3day
FROM daily_sales;

-- 3. Сравнение продаж каждого менеджера с лучшим результатом в его городе
SELECT
    city,
    manager,
    sales,
    FIRST_VALUE(sales) OVER (
        PARTITION BY city
        ORDER BY sales DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as best_sales_in_city
FROM manager_performance;

Использование оконных функций значительно упрощает сложные аналитические запросы, которые раньше требовали самосоединений (self-joins) или коррелированных подзапросов.