До этого момента мы говорили об окне как о «всей партиции» или «всех строках от начала до текущей». Это были упрощения — реальное окно гораздо гибче. У окна есть frame (рамка) — подмножество строк партиции, которое именно сейчас используется для вычисления функции. Frame можно сделать «последние 7 строк», «3 строки до и 3 после», «строки этого часа», «всё кроме последней».

Это самая глубокая тема window functions и самая полезная для практики — moving averages, скользящие медианы, временные тренды все строятся через frame.

Из чего состоит frame

Полный синтаксис:

{ROWS | RANGE | GROUPS} BETWEEN <start> AND <end>

Каждая граница — одно из:

• UNBOUNDED PRECEDING — от самого начала партиции;
• UNBOUNDED FOLLOWING — до самого конца партиции;
• CURRENT ROW — текущая строка;
• N PRECEDING — N строк назад;
• N FOLLOWING — N строк вперёд.

Тип frame (ROWS, RANGE, GROUPS) определяет, что именно считается «строкой». Об этом ниже.

Для строки 5 в окно ROWS BETWEEN 2 PRECEDING AND CURRENT ROW попадают строки 3, 4, 5 — то есть мы получим, например, SUM = 100 + 180 + 220 = 500. Для строки 6 окно сдвинется на одну позицию — попадут строки 4, 5, 6. Это и есть скользящее окно.

Дефолтный frame — нюанс, который кусает

Если в OVER есть ORDER BY, но нет явного frame — действует дефолт:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

То есть «все строки от начала окна до текущей включительно». Это объясняет, почему SUM(x) OVER (ORDER BY date) даёт running total — не магия, просто дефолтный frame.

Если в OVER нет ORDER BY — frame по умолчанию: «вся партиция целиком» (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Это объясняет, почему SUM(x) OVER (PARTITION BY ...) даёт сумму по всей партиции, а не нарастающую.

Многие новички путаются именно здесь: добавил ORDER BY в OVER — и привычное «общее SUM» внезапно стало нарастающим. Понимая дефолтный frame, это перестаёт быть сюрпризом.

ROWS vs RANGE — самое тонкое место

ROWS и RANGE различаются в том, что значит N PRECEDING/FOLLOWING.

• ROWS считает физические строки: 1 PRECEDING — это ровно одна строка назад.
• RANGE считает по значению ORDER BY: 1 PRECEDING это «строки со значением ORDER BY-ключа от (текущее - 1) до текущего».

Разница видна там, где в ORDER BY есть дубликаты.

В этом запросе каждый день уникален — ROWS BETWEEN 1 PRECEDING AND CURRENT ROW и RANGE BETWEEN 1 PRECEDING AND CURRENT ROW совпадают, потому что между соседними днями всегда дистанция в 1 день. Но если у тебя несколько строк с одинаковой датой и ORDER BY date — RANGE для CURRENT ROW включит все строки с этой датой, а ROWS — только одну (физически текущую).

Правило простое: для скользящих агрегатов по позиции (последние 3 транзакции) — ROWS. Для скользящих по времени (последние 7 дней по факту) — RANGE с интервалом. Дефолтный frame — RANGE, что часто незаметно, но в задачах с дублями ORDER BY-ключа критически важно.

RANGE BETWEEN INTERVAL — moving average по реальному времени

В PostgreSQL RANGE BETWEEN N PRECEDING работает только с числами и интервалами. Это позволяет делать истинный moving average по времени, а не по числу строк.

Заметь: окно меряется во временном промежутке. Если 5 дней без платежей, а потом сразу несколько — окно «забудет» те, что были раньше 7 дней назад. С ROWS BETWEEN 6 PRECEDING AND CURRENT ROW это бы не сработало — пришлось бы фильтровать по количеству транзакций, а не по реальному времени.

Running total — самый частый кейс

Все примеры выше — это частные случаи скользящего окна. Самый классический — running total: сумма от начала до текущей строки.

Эквивалентный запрос с дефолтным frame:

Оба запроса дают одинаковый результат, потому что paid_at уникален в пределах одного клиента (нет двух платежей в одну и ту же миллисекунду). Если бы были одновременные платежи — RANGE-дефолт схватил бы их все в одну группу, а ROWS — нет. Это редко важно на практике, но полезно знать.

Moving average по N строкам

Скользящее среднее по последним 3 платежам клиента — частая задача для сглаживания шумных рядов:

Обрати внимание на нюанс края: для самого первого платежа клиента в окно попадает только 1 строка (он сам), для второго — 2 строки. Только начиная с третьей AVG считается по полному окну 3. Это края — частая проблема при moving statistics: первые N-1 значения «недоопределены». В отчётах это решают через NULL или дисклеймер «данные с начала периода».

FILTER inside window — точечное исключение

В PostgreSQL можно добавить FILTER (WHERE ...) к агрегатной функции внутри окна — суммировать только подходящие строки. Это уменьшает потребность в CASE WHEN.

Эта возможность недоступна для непустых ranking-функций (ROW_NUMBER, RANK), но прекрасно работает с SUM, COUNT, AVG. Часто заменяет коррелированный подзапрос на 5 строк.

GROUPS — третий редкий тип frame

GROUPS BETWEEN N PRECEDING AND ... — это «N групп строк с одинаковым ORDER BY-значением назад». Полезно, когда ORDER BY-ключ — это категория или временной шаг с дублями. Используется редко, упоминаю для полноты.

Чек-лист

• Frame — подмножество строк партиции, по которому считается функция для текущей строки.
• Полный синтаксис: {ROWS | RANGE | GROUPS} BETWEEN <start> AND <end>.
• Дефолтный frame с ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running от начала до сейчас).
• Дефолтный frame без ORDER BY: вся партиция целиком.
• ROWS — по физическим строкам. RANGE — по значениям ORDER BY-ключа. Различия видны при дубликатах ключа.
• RANGE BETWEEN INTERVAL '7 days' PRECEDING — moving average по времени, а не по количеству строк.
• FILTER (WHERE ...) работает с агрегатами и внутри окна — даёт точечное исключение строк.
• Края скользящего окна — частая проблема: первые N-1 значений работают на неполном окне.