Привет, Хабр!

Оконные функции — главный инструмент аналитика в SQL: нарастающие итоги, ранги, скользящие средние, сравнение строки с соседями. И почти каждый, кто ими пользуется, в какой‑то момент натыкается на одно и то же.

Вы пишете нарастающий итог: SUM(amount) OVER (ORDER BY txn_date). На большинстве данных результат выглядит правильным. А потом в выборку попадают две операции с одной и той же датой — и нарастающий итог «прыгает»: обе строки показывают одинаковое, уже просуммированное значение, как будто вторая операция посчиталась раньше, чем до неё дошла очередь. Или LAST_VALUE упорно возвращает значение текущей строки вместо последней в группе.

Причина у этих случаев одна. Между функцией и OVER стоит ещё одна, невидимая часть — кадр окна (window frame). Вы её не написали, поэтому за вас её написала база. И выбрала она не то, что вы предполагали: не построчный диапазон, а RANGE, который работает по значениям и при одинаковых ключах сортировки ведёт себя совсем не так, как ожидается.

В статье разберём, из чего собрана оконная функция, какой кадр база подставляет по умолчанию, чем ROWS отличается от RANGE, почему LAST_VALUE так часто врёт и как читать оконные функции в чужом коде.

Из чего собрана оконная функция

Любая оконная функция — это четыре части, и работают они по очереди.

PARTITION BY делит строки на независимые группы. ORDER BY задаёт внутри группы порядок. Дальше идёт та часть, которую почти никто не пишет явно: кадр определяет, какие именно строки видит функция, когда считает значение для текущей строки. И только потом сама функция — SUM, AVG, LAST_VALUE — применяется к строкам кадра.

Большинство останавливается на PARTITION BY и ORDER BY и считает, что этого достаточно. Но кадр существует всегда. Если вы его не указали, он не «выключен», он взят по умолчанию. И всё поведение функции определяется именно тем, какие строки попали в кадр.

Какой кадр база подставляет по умолчанию

Здесь и основная проблема. Правило стандарта SQL такое.

Если в OVER есть ORDER BY, но кадр не указан, подставляется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Если ORDER BY нет вообще, кадром становится вся группа — RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Это поведение одинаково в PostgreSQL, MySQL, SQL Server, Oracle и SQLite — оно записано в стандарте. И проблема в том, что, написав, ORDER BY, мы почти всегда подразумеваем построчный кадр, то есть ROWS. А получаем RANGE. Пока ключ сортировки уникален, разницы между ними нет, и проблема спит.

ROWS или RANGE: разберём на данных

Соберём маленький стенд — счёт и четыре операции по нему:

CREATE TABLE transactions (
    id        int,
    txn_date  date,
    amount    int
);

INSERT INTO transactions VALUES
    (1, '2024-01-01', 100),
    (2, '2024-01-02',  50),
    (3, '2024-01-02',  30),   -- та же дата, что и у строки 2
    (4, '2024-01-03',  20);

Две операции — вторая и третья — приходятся на одну дату. Это и есть «одинаковые значения ключа», на которых ROWS и RANGE расходятся.

Считаем нарастающий итог так, как его обычно и пишут:

SELECT id, txn_date, amount,
       SUM(amount) OVER (ORDER BY txn_date) AS running_total
FROM transactions;

 id |  txn_date  | amount | running_total
----+------------+--------+---------------
  1 | 2024-01-01 |    100 |           100
  2 | 2024-01-02 |     50 |           180
  3 | 2024-01-02 |     30 |           180
  4 | 2024-01-03 |     20 |           200

Посмотрите на строки 2 и 3. Нарастающий итог прыгнул со 100 сразу на 180, промежуточного 150 нет, и обе операции за 2 января показывают одинаковые 180.

Так работает RANGE, который подставился по умолчанию.

RANGE строит кадр не по позициям строк, а по значениям ключа сортировки. CURRENT ROW в режиме RANGE означает не «эта строка», а «все строки, у которых значение ключа равно текущему». Для строки 2 ключ — 2024-01-02, и в кадр попадают все строки с этой датой: и строка 2, и строка 3. Поэтому SUM для строки 2 — это 100 + 50 + 30. Для строки 3 кадр ровно такой же, и сумма та же.

Теперь попросим построчный кадр явно:

SELECT id, txn_date, amount,
       SUM(amount) OVER (
           ORDER BY txn_date, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM transactions;

 id |  txn_date  | amount | running_total
----+------------+--------+---------------
  1 | 2024-01-01 |    100 |           100
  2 | 2024-01-02 |     50 |           150
  3 | 2024-01-02 |     30 |           180
  4 | 2024-01-03 |     20 |           200

Вот теперь 100, 150, 180, 200 — нарастающий итог идёт по одной строке за раз. ROWS считает физические строки: CURRENT ROW здесь — ровно текущая строка, а не её «одногруппники».

Обратите внимание еще на то, что в ORDER BY добавился id. Без него у строк 2 и 3 одинаковый ключ, и какая из них «была раньше» в построчном кадре, не определено. Для построчных расчётов ключ сортировки должен быть уникальным.

LAST_VALUE: самая известная жертва кадра

Кадр по умолчанию заканчивается на CURRENT ROW. Для SUM это привычно — нарастающий итог и должен останавливаться на текущей строке. Но для LAST_VALUE ровно это делает результат бессмысленным:

SELECT id, amount,
       LAST_VALUE(amount) OVER (ORDER BY id) AS last_amount
FROM transactions;

 id | amount | last_amount
----+--------+-------------
  1 |    100 |         100
  2 |     50 |          50
  3 |     30 |          30
  4 |     20 |          20

last_amount равен amount той же строки. Логично: кадр для каждой строки — от начала и до CURRENT ROW, последняя строка такого кадра и есть текущая. LAST_VALUE вернул последнее значение кадра — просто кадр заканчивается на текущей строке, а не на конце группы.

Чтобы получить действительно последнее значение в группе, кадр надо расширить до конца:

SELECT id, amount,
       LAST_VALUE(amount) OVER (
           ORDER BY id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_amount
FROM transactions;

Теперь last_amount равен 20 для всех строк — значению последней операции. FIRST_VALUE, кстати, работает корректно по умолчанию по той же причине, по которой ломается LAST_VALUE: кадр начинается с UNBOUNDED PRECEDING, и первая строка кадра — действительно первая в группе.

Кадр — это инструмент

После примера с RANGE легко решить, что RANGE — «плохой режим, которого надо избегать». Это не так! RANGE строит кадр по значениям, иногда именно это и нужно.

Классический пример — скользящее среднее по календарным дням, а не по числу строк. Построчный кадр «семь строк назад» сломается, если в какие‑то дни записей нет, а в какие‑то несколько. Кадр по значению решает задачу честно (пример в синтаксисе PostgreSQL):

AVG(amount) OVER (
    ORDER BY txn_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)

Здесь RANGE берёт все строки, чья дата попадает в последние семь календарных дней, независимо от их количества. Для скользящего среднего по фиксированному числу строк нужен ROWS, для среднего по календарному окну — RANGE. Это не «лучше или хуже», это две разные задачи. Проблема не в RANGE, а в том, что он применяется по умолчанию там, где его никто не выбирал.

Как читать оконные функции в чужом коде

Несколько признаков, на которых стоит остановиться при разборе запроса.

• ORDER BY в OVER без явного указания кадра — функция получает RANGE ... CURRENT ROW. Убедитесь, что нужен именно он, а не ROWS.

• ORDER BY по неуникальному столбцу с кадром по умолчанию — у одинаковых ключей общий кадр, и значения для них «склеятся». Для нарастающих итогов это почти всегда ошибка.

• LAST_VALUE без UNBOUNDED FOLLOWING — почти наверняка возвращает значение текущей строки, а не последней в группе.

Нарастающий итог, числа которого где‑то «перепрыгивают» — верный признак RANGE на неуникальном ключе.

Построчный расчёт через ROWS с сортировкой по неуникальному столбцу без тай‑брейкера — порядок среди одинаковых ключей не определён, результат недетерминирован.

Что в итоге

Между функцией и OVER всегда стоит кадр. Если вы его не написали, его написала база — и по стандарту выбрала RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. На уникальном ключе сортировки это сходит с рук, потому что ROWS и RANGE дают там одно и то же. На неуникальном — результаты расходятся, и нарастающий итог начинает врать.

Практический вывод для нас такой. Для построчной аналитики — нарастающих итогов, скользящих средних по числу строк — пишите ROWS явно и добавляйте в ORDER BY уникальный тай‑брейкер. RANGE берите осознанно, когда нужен кадр по значению: календарное окно, диапазон по сумме. А LAST_VALUE без расширенного до UNBOUNDED FOLLOWING кадра лучше не использовать вовсе.

Оконные функции — только один пример того, как неявное поведение базы может влиять на результат запроса. Такие нюансы встречаются и в планах выполнения, индексах, транзакциях, блокировках и оптимизации.

На курсе «PostgreSQL для администраторов баз данных и разработчиков» разбирают PostgreSQL системно: от написания запросов до понимания того, как база выполняет их внутри и почему один и тот же SQL может работать по‑разному на реальных данных.

А если хочется сначала точечно погрузиться в SQL и работу баз данных, можно начать с бесплатных открытых уроков. Их ведут преподаватели‑практики: можно посмотреть на подход к теме, задать вопросы и понять, насколько хочется разбираться глубже.

• 21 мая, 20:00 — «SQL: Обобщенное табличное выражение (CTE) — как писать сложные запросы просто».
  _{Посмотрим, как CTE помогают собирать сложные SQL‑запросы в более понятную и читаемую структуру.}

• 2 июня, 20:00 — «Ты — индекс в Postgres, Я — индекс в ClickHouse. Мы — разные».
  _{Разберёмся, чем отличаются подходы к индексам в разных СУБД и почему это влияет на проектирование и поведение запросов.}