Window функции

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

Анатомия оконной функции

функция(...) OVER (
    PARTITION BY колонки_раздела
    ORDER BY колонки_сортировки
    спецификация_фрейма
)

Три компонента OVER-клаузы:

• PARTITION BY — разбивает строки на независимые группы (аналог GROUP BY, но без схлопывания)
• ORDER BY — определяет порядок строк внутри каждого раздела
• Frame — определяет, какие строки относительно текущей входят в окно (ROWS, RANGE, GROUPS)

Фрейм определяет «скользящее окно» вокруг текущей строки. Для каждой строки функция применяется к набору строк, определённому фреймом.

Ранжирующие функции

Эти функции присваивают порядковый номер каждой строке:

SELECT
    region,
    customer_id,
    SUM(amount) AS total,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn,
    RANK()       OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS drnk
FROM orders
GROUP BY region, customer_id;

Различия между функциями при одинаковых значениях:

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

Практика: Top-N на группу

-- Топ-3 клиента по выручке в каждом регионе
WITH ranked AS (
    SELECT
        region,
        customer_id,
        SUM(amount) AS total,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn
    FROM orders
    GROUP BY region, customer_id
)
SELECT region, customer_id, total
FROM ranked
WHERE rn <= 3;

Навигационные функции

Доступ к значениям соседних строк без self-join:

SELECT
    order_date,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY order_date) AS prev_day_revenue,
    LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS day_over_day
FROM daily_revenue;

Практика: процент изменения

-- Ежемесячная выручка с процентом изменения
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
        / LAG(revenue) OVER (ORDER BY month),
        1
    ) AS pct_change
FROM monthly_revenue;

Спецификация фрейма

Фрейм определяет диапазон строк для каждого вычисления. Три типа:

ROWS

Считает физические строки от текущей позиции:

-- Скользящее среднее за 7 дней (7 строк)
SELECT
    day,
    revenue,
    AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_revenue;

RANGE

Считает строки по значению ORDER BY. Полезно при пропусках:

-- RANGE учитывает значения, а не позиции
-- Если есть пропуски дат, RANGE корректно обработает диапазон
SELECT
    day,
    revenue,
    SUM(revenue) OVER (
        ORDER BY day
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ) AS rolling_7d_sum
FROM daily_revenue;

GROUPS

Считает группы одинаковых значений ORDER BY:

-- Сумма по текущей и двум предыдущим группам
SELECT
    score_bucket,
    count,
    SUM(count) OVER (
        ORDER BY score_bucket
        GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS cumulative
FROM score_distribution;

Границы фрейма

-- Кумулятивная сумма (от начала до текущей строки)
SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Центрированное окно (3 строки до, текущая, 3 после)
AVG(amount) OVER (ORDER BY day ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

-- Полный раздел
MAX(amount) OVER (PARTITION BY region ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Агрегатные функции в окне

Обычные агрегатные функции работают как оконные с добавлением OVER:

SELECT
    order_date,
    region,
    amount,
    -- Накопительная сумма по региону
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- Доля в общей выручке региона
    ROUND(
        amount * 100.0 / SUM(amount) OVER (PARTITION BY region),
        2
    ) AS pct_of_region
FROM orders;

Практический пример: аналитический отчёт

WITH daily AS (
    SELECT
        DATE_TRUNC('day', order_date) AS day,
        region,
        SUM(amount) AS revenue,
        COUNT(*) AS orders
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('day', order_date), region
)
SELECT
    day,
    region,
    revenue,
    orders,
    -- Скользящее среднее выручки (7 дней)
    ROUND(AVG(revenue) OVER w, 2) AS avg_7d,
    -- Кумулятивная выручка с начала
    SUM(revenue) OVER (
        PARTITION BY region ORDER BY day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    -- Ранг дня по выручке в регионе
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS revenue_rank
FROM daily
WINDOW w AS (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ORDER BY day DESC, revenue DESC;

Итоги

• Оконные функции вычисляют значение для каждой строки без схлопывания (в отличие от GROUP BY)
• PARTITION BY разбивает данные, ORDER BY задаёт порядок, фрейм ограничивает диапазон
• Ранжирование: ROW_NUMBER (уникальный), RANK (с пропусками), DENSE_RANK (без пропусков)
• Навигация: LAG/LEAD, FIRST_VALUE/LAST_VALUE, NTH_VALUE
• Фреймы: ROWS (физические строки), RANGE (по значению), GROUPS (группы значений)
• Агрегатные функции (SUM, AVG, COUNT) работают как оконные с OVER()