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

Question

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

Тип вопроса: Базы данных Вероятность: 25% Категория: C# Разработчик

Ответ

Оконные функции (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():

PARTITION BY — разбивает весь набор данных на группы (партиции). Вычисления выполняются в рамках каждой партиции независимо. Аналог GROUP BY, но без свёртки строк.
ORDER BY — определяет порядок строк внутри партиции (или всего окна), что критично для ранжирующих функций (RANK, ROW_NUMBER) и функций доступа к смежным строкам (LAG, LEAD).
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 для сложного аналитического запроса без необходимости использования курсоров или обработки на стороне приложения.

Ответ 18+ 🔞

Давай разберем эту штуку, которая в 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():

PARTITION BY — это как бы разбивка на кучки. Весь твой результат режется на группы (партиции), и расчет идет внутри каждой группы отдельно. Похоже на GROUP BY, но строки не сливаются в одну.
ORDER BY — задает порядок строк внутри партиции. Без этого ранжирующие функции и функции смещения — просто кусок бесполезного кода.
ROWS/RANGE BETWEEN — это уже тонкая настройка, называется кадр. Тут ты говоришь: "ок, считай не по всей партиции, а, например, от текущей строки и на две строки назад". Для скользящих средних — самое то.

Какие бывают функции, которые можно в это окно запихнуть:

Обычные агрегаты, но по-новому: SUM(), AVG(), COUNT(). Работают так же, только не схлопывают всё в одну строку.
Ранжирующие (все друг на друга смотрят):
- ROW_NUMBER() — тупо нумерует строки по порядку.
- RANK() — присваивает ранг. Если значения одинаковые — ранг будет один, но следующий номер — с пропуском. Как в спорте: два золота, потом сразу бронза.
- DENSE_RANK() — тоже ранг, но без пропусков. Два золота, а следующее — серебро.
Функции-подсматриватели (к соседям лезут):
- LAG(column, offset) — смотрит на строчку, которая выше на offset позиций. "А что было вчера?"
- LEAD(column, offset) — смотрит на строчку, которая ниже. "А что будет завтра?"
- FIRST_VALUE(column), LAST_VALUE(column) — хватают первое и последнее значение в окне. С LAST_VALUE будь осторожен, по умолчанию её кадр может тебя обмануть.

Пример из жизни, чтобы вообще всё встало на свои места — скользящее среднее за неделю:

SELECT 
    OrderDate,
    DailyRevenue,
    AVG(DailyRevenue) OVER (
        ORDER BY OrderDate 
        -- Вот этот кадр — сердце скользящего среднего. Берем текущий день и 6 предыдущих.
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS SevenDayMovingAvg
FROM DailySales
ORDER BY OrderDate;

Короче, если раньше для такой аналитики приходилось городить огород с подзапросами или вообще выносить мозг приложению, то теперь можно одним запросом всё сделать. Инструмент, блядь, мощнейший, когда разберешься — обратно не захочешь.

Видео-ответы

▶

СОБЕСЕДОВАНИЕ PYTHON DEVELOPER | BALABOL IT PYTHON PODCAST Junior | Март 2023