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

0. Что такое оконные функции и чем они отличаются от GROUP BY

Оконная функция — это функция, которая выполняет вычисления по набору строк, связанных с текущей строкой (это называется "окном"), но не сворачивает строки в одну.

Ключевое отличие от GROUP BY

GROUP BY:

• Сворачивает множество строк в одну строку на группу
• Ты теряешь детали по отдельным строкам
• В SELECT можно использовать только поля из GROUP BY и агрегаты

Оконные функции:

• Не сворачивают строки — каждая строка остаётся
• Ты можешь добавить к каждой строке агрегированное значение по группе
• В SELECT можно использовать любые поля

Пример: средняя зарплата по отделу

Через GROUP BY (теряем детали):

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Результат:

Мы видим среднюю зарплату по отделам, но не видим, кто сколько зарабатывает.

Через оконную функцию (сохраняем детали):

SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

Результат:

Мы видим каждого сотрудника и при этом среднюю зарплату по его отделу.

💡 Когда использовать оконные функции:

• Когда нужно добавить агрегат к каждой строке (например, "зарплата сотрудника vs средняя по отделу")
• Когда нужно ранжировать строки внутри группы
• Когда нужно сравнить строку с предыдущей/следующей
• Когда нужно посчитать накопительный итог

1. Базовый синтаксис: OVER(), PARTITION BY, ORDER BY

Структура оконной функции

функция() OVER (
    [PARTITION BY столбец1, столбец2, ...]
    [ORDER BY столбец3, столбец4, ...]
    [frame_clause]  -- для агрегатных функций
)

PARTITION BY: разбиваем на группы

PARTITION BY работает как GROUP BY, но не сворачивает строки. Он разбивает данные на партиции (группы), и оконная функция применяется внутри каждой партиции независимо.

SELECT 
    name,
    department_id,
    salary,
    COUNT(*) OVER (PARTITION BY department_id) AS dept_count
FROM employees;

Результат:

Каждая строка получает количество сотрудников в своём отделе.

ORDER BY: сортировка внутри партиции

ORDER BY в оконной функции определяет порядок строк внутри партиции. Это важно для функций ранжирования и для агрегатных функций с frame clause.

SELECT 
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Результат:

Внутри каждого отдела сотрудники отсортированы по убыванию зарплаты, и каждой строке присвоен номер.

OVER() без PARTITION BY: всё окно — вся таблица

Если не указать PARTITION BY, окно — это все строки результата.

SELECT 
    name,
    salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

Каждая строка получит общую сумму зарплат (760 000).

2. Функции ранжирования: ROW_NUMBER(), RANK(), DENSE_RANK()

Эти функции присваивают номер каждой строке внутри партиции. Разница — в том, как они обрабатывают одинаковые значения.

ROW_NUMBER(): уникальный номер для каждой строки

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

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

Результат:

Если бы у двух сотрудников была одинаковая зарплата, они всё равно получили бы разные номера (какой именно — зависит от порядка обработки, недетерминировано).

RANK(): одинаковые значения получают одинаковый номер, следующий номер пропускается

SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Если бы у Анны и Павла была одинаковая зарплата (120 000):

DENSE_RANK(): одинаковые значения получают одинаковый номер, следующий номер НЕ пропускается

SELECT 
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Если бы у Анны и Павла была одинаковая зарплата (120 000):

💡 Когда что использовать:

• ROW_NUMBER() — когда нужен уникальный номер (например, для пагинации)
• RANK() — когда нужно показать "место" с пропусками (как в спорте: 1, 2, 2, 4)
• DENSE_RANK() — когда нужно показать "место" без пропусков (1, 2, 2, 3)

Практический пример: топ-2 сотрудника по зарплате в каждом отделе

WITH ranked AS (
    SELECT 
        name,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rn <= 2;

Результат:

3. Функции смещения: LEAD() и LAG()

Эти функции позволяют получить значение из следующей (LEAD) или предыдущей (LAG) строки в окне.

LAG(): значение из предыдущей строки

SELECT 
    name,
    department_id,
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

Результат:

Для первой строки в каждой партиции prev_salary = NULL, потому что предыдущей строки нет.

LEAD(): значение из следующей строки

SELECT 
    name,
    department_id,
    salary,
    LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
FROM employees;

Результат:

Смещение на несколько строк

Можно указать, на сколько строк сместиться:

SELECT 
    name,
    salary,
    LAG(salary, 2) OVER (ORDER BY salary) AS salary_2_rows_back
FROM employees;

Значение по умолчанию

Если нет предыдущей/следующей строки, можно указать значение по умолчанию:

SELECT 
    name,
    department_id,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

Теперь вместо NULL будет 0.

💡 Практическое применение:

• Посчитать разницу между текущей и предыдущей зарплатой
• Найти рост/падение показателей
• Сравнить текущий месяц с предыдущим

Пример: рост зарплаты по сравнению с предыдущим сотрудником в отделе

SELECT 
    name,
    department_id,
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary,
    salary - LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS salary_diff
FROM employees;

4. Агрегатные функции в окне: накопительные итоги и скользящие средние

Обычные агрегатные функции (SUM, AVG, COUNT, MIN, MAX) можно использовать как оконные. Но для них важно понимать frame clause — какую часть окна использовать.

Базовая агрегация в окне

Без ORDER BY агрегат считается по всей партиции:

SELECT 
    name,
    department_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

Каждая строка получает сумму зарплат по своему отделу.

Накопительный итог (running total)

С ORDER BY агрегат считается накопительно — от начала партиции до текущей строки:

SELECT 
    name,
    department_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total
FROM employees;

Результат:

Для Анны: 110000 + 120000 = 230000. Для Марии: 110000 + 120000 + 130000 = 360000.

Явное указание frame clause

По умолчанию с ORDER BY используется frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (от начала партиции до текущей строки).

Можно указать явно:

SELECT 
    name,
    department_id,
    salary,
    SUM(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS sum_last_2
FROM employees;

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW — сумма текущей строки и одной предыдущей.

Результат:

Для Анны: 110000 + 120000 = 230000. Для Марии: 120000 + 130000 = 250000.

Скользящее среднее (moving average)

SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM employees;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING — средняя по текущей, предыдущей и следующей строке.

Другие варианты frame clause

• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — вся партиция (то же, что без ORDER BY)
• ROWS BETWEEN 3 PRECEDING AND CURRENT ROW — последние 4 строки (включая текущую)
• RANGE BETWEEN ... — работает со значениями, а не с позициями строк

💡 ROWS vs RANGE:

• ROWS — считает по количеству строк
• RANGE — считает по значениям (если есть дубликаты, они попадают в один frame)

5. Комбинированные примеры

Пример 1: Сотрудники с указанием их ранга по зарплате в отделе и средней зарплаты по отделу

SELECT 
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;

Результат:

Пример 2: Рост зарплаты по сравнению с предыдущим сотрудником в отделе

SELECT 
    name,
    department_id,
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary,
    salary - LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS salary_growth
FROM employees
WHERE LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) IS NOT NULL;

⚠️ Ошибка: нельзя использовать оконные функции в WHERE. Нужно обернуть в CTE:

WITH salary_data AS (
    SELECT 
        name,
        department_id,
        salary,
        LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
    FROM employees
)
SELECT 
    name,
    department_id,
    salary,
    prev_salary,
    salary - prev_salary AS salary_growth
FROM salary_data
WHERE prev_salary IS NOT NULL;

Пример 3: Накопительный итог зарплат по компании

SELECT 
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary) AS running_total,
    SUM(salary) OVER () AS grand_total
FROM employees;

6. Ограничения оконных функций

⚠️ Оконные функции нельзя использовать в WHERE, GROUP BY, HAVING.

Почему? Потому что оконные функции выполняются после WHERE, GROUP BY, HAVING, но до ORDER BY.

Порядок выполнения с оконными функциями:

Если нужно отфильтровать по результату оконной функции, используй CTE или подзапрос:

-- НЕ РАБОТАЕТ:
SELECT name, salary
FROM employees
WHERE RANK() OVER (ORDER BY salary DESC) <= 3;

-- РАБОТАЕТ:
WITH ranked AS (
    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk <= 3;

7. Практика: 8 задач на этот этап

Попробуй решить самостоятельно, не подглядывая ниже.

1. Для каждого сотрудника выведи его зарплату и общую сумму зарплат по компании.
2. Для каждого сотрудника выведи его зарплату, среднюю зарплату по его отделу и разницу между ними.
3. Проранжируй сотрудников по зарплате внутри каждого отдела (используй ROW_NUMBER()).
4. Найди топ-1 сотрудника по зарплате в каждом отделе (используй CTE и ROW_NUMBER()).
5. Для каждого сотрудника выведи его зарплату и зарплату предыдущего сотрудника в том же отделе (по возрастанию зарплаты).
6. Посчитай накопительный итог зарплат по компании (отсортируй по зарплате).
7. Для каждого сотрудника выведи его зарплату и среднюю зарплату по трём сотрудникам (предыдущий, текущий, следующий) в его отделе.
8. Найди сотрудников, которые зарабатывают больше, чем предыдущий сотрудник в их отделе (по возрастанию зарплаты).

-- 1
SELECT 
    name,
    salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

-- 2
SELECT 
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department_id) AS diff
FROM employees;

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

-- 4
WITH ranked AS (
    SELECT 
        name,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rn = 1;

-- 5
SELECT 
    name,
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

-- 6
SELECT 
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

-- 7
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM employees;

-- 8
WITH with_prev AS (
    SELECT 
        name,
        department_id,
        salary,
        LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
    FROM employees
)
SELECT name, department_id, salary
FROM with_prev
WHERE salary > prev_salary;

Mini-чеклист, что этап освоен

Ты готова к шестому этапу, если можешь без подсказки объяснить:

• чем оконные функции отличаются от GROUP BY;
• что делает PARTITION BY и ORDER BY в оконной функции;
• чем ROW_NUMBER() отличается от RANK() и DENSE_RANK();
• как работают LEAD() и LAG();
• что такое накопительный итог и как его посчитать;
• почему оконные функции нельзя использовать в WHERE.