Фреймы оконных функций SQL: ROWS, RANGE и GROUPS

Когда вы пишете SUM(amount) OVER (ORDER BY date), PostgreSQL автоматически использует дефолтный фрейм. Понимание фреймов открывает контроль над тем, какие строки попадают в расчёт агрегата.

Полный синтаксис оконной функции

function_name() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame_clause]
)

frame_clause:

{ ROWS | RANGE | GROUPS }
BETWEEN frame_start AND frame_end

-- frame_start / frame_end:
UNBOUNDED PRECEDING  -- первая строка раздела
N PRECEDING          -- N строк назад
CURRENT ROW          -- текущая строка
N FOLLOWING          -- N строк вперёд
UNBOUNDED FOLLOWING  -- последняя строка раздела

Дефолтный фрейм: что происходит без явного указания

-- Без frame_clause
SUM(amount) OVER (ORDER BY date)

-- Эквивалентно:
SUM(amount) OVER (ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Важно: с ORDER BY дефолт — RANGE ... CURRENT ROW, что включает все строки с одинаковым значением ORDER BY.

Без ORDER BY — ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (весь раздел).

ROWS vs RANGE: ключевая разница

ROWS: по позиции строк

SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Фрейм определяется физическими позициями строк. Каждая строка уникальна.

RANGE: по значениям ORDER BY

SUM(amount) OVER (ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Фрейм включает все строки с одинаковым значением ORDER BY как у текущей.

Пример разницы:

Данные: три заказа 01.03 и один 02.03

С RANGE все строки одной даты видят итоговую сумму за эту дату — «прыжок». С ROWS — нарастающий итог строки за строкой.

Для нарастающего итога используйте ROWS.

Скользящее окно: N PRECEDING / N FOLLOWING

-- Скользящая сумма за последние 7 дней включительно
SELECT
    sale_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d

FROM daily_sales;

-- Скользящее среднее: 3 дня до и 3 после (центрированное)
AVG(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
)

GROUPS: по группам значений (PostgreSQL 11+)

-- Фрейм: N групп уникальных значений ORDER BY
SUM(amount) OVER (
    ORDER BY category
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
-- Включает текущую группу + одну до + одну после

GROUPS — нечто среднее между ROWS и RANGE. Удобен когда ORDER BY — нечисловой ключ.

EXCLUDE: исключить строки из фрейма (PostgreSQL 14+)

-- Исключить текущую строку из агрегата (сумма соседей)
SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    EXCLUDE CURRENT ROW
)

-- Исключить текущую строку и «равные» (TIES)
EXCLUDE CURRENT ROW    -- только текущая строка
EXCLUDE TIES           -- строки с таким же ORDER BY значением
EXCLUDE GROUP          -- текущая + TIES (аналог EXCLUDE CURRENT ROW для RANGE)
EXCLUDE NO OTHERS      -- ничего не исключать (по умолчанию)

Пример: сумма только соседних строк (без текущей)

SELECT
    id,
    amount,
    SUM(amount) OVER (
        ORDER BY id
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        EXCLUDE CURRENT ROW
    ) AS neighbors_sum
FROM orders;

Практические паттерны

Нарастающий итог (корректный)

SUM(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Скользящее среднее за 7 дней

AVG(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

Весь раздел (сравнить с итогом)

SUM(revenue) OVER (PARTITION BY category)  -- весь раздел, нет ORDER BY
-- Дефолт без ORDER BY: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Нарастающий MAX (исторический максимум)

MAX(price) OVER (
    ORDER BY trade_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS all_time_high

Сравнение с N предыдущими строками

-- Доля текущей продажи в сумме последних 5
revenue / SUM(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS share_of_last_5

Производительность фреймов

Для больших таблиц убедитесь что есть индекс на столбце ORDER BY оконной функции.

Итог: шпаргалка фреймов

-- Нарастающий итог (с нарастанием строка за строкой)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Скользящее окно за последние N строк
ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW

-- Симметричное скользящее окно
ROWS BETWEEN N PRECEDING AND N FOLLOWING

-- Весь раздел (нет ORDER BY)
-- Дефолт когда нет ORDER BY

-- Вся таблица (нет PARTITION BY и ORDER BY)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Явно указывайте фрейм — это делает намерение понятным и предотвращает неожиданное поведение с дублирующимися значениями ORDER BY.