Рамки ROWS и RANGE

Когда Вы уже умеете писать накопительный итог, естественный дополнительный вопрос звучит так: «В чём разница между ROWS и RANGE в рамке окна?» Это точный показатель знаний на среднем уровне. Многие кандидаты ежедневно используют рамки, но никогда не замечают, что эти два ключевых слова работают по-разному.

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

ROWS работает по позициям. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW буквально означает текущую строку и две физические строки непосредственно перед ней в упорядоченной последовательности.

При этом не имеет значения, совпадают ли значения ORDER BY у соседних строк. Три строки — значит три строки, без исключений. Именно такую рамку почти всегда следует использовать для скользящих средних и строгих накопительных итогов.

RANGE работает по значениям. Он включает все строки, чьё значение ORDER BY попадает в логический диапазон относительно значения текущей строки, а не выбирает фиксированное количество строк.

Самое важное следствие: при использовании RANGE все строки с одинаковым значением ORDER BY считаются одной группой равных строк. Для всех них устанавливается одна и та же рамка, поэтому они получают один и тот же результат.

Предположим, две продажи произошли 2024-03-02 и составили 30 и 40, а перед ними 2024-03-01 была продажа на 100.

• При RANGE до CURRENT ROW: обе строки за 03-02 являются равными, поэтому в обеих отображается 100 + 30 + 40 = 170.
• При ROWS до CURRENT ROW: первая строка за 03-02 показывает 130, а вторая — 170, поскольку каждая строка расширяет рамку на одну позицию.

Одни и те же данные, но разные числа. Именно это расхождение при одинаковых значениях составляет суть вопроса.

Напомним: запись OVER (ORDER BY ...) без явно заданной рамки по умолчанию означает RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

RANGE может принимать смещение значения, а не только UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW для столбца с датами или числами включает каждую строку, значение которой находится в пределах 7 единиц от текущего значения.

Для дат это даёт настоящее окно «последние 7 дней», корректно пропуская отсутствующие дни, тогда как ROWS 7 PRECEDING захватит предыдущие 7 строк независимо от пропусков. Знание этой тонкости — сильный ответ.

Обратная сторона: ROWS не учитывает пропуски в данных. Если в таблице продаж отсутствуют выходные, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW охватывает 7 зафиксированных дней, которые фактически могут приходиться на две календарные недели.

Поэтому выбор зависит от цели: последние N записей означают ROWS; последние N единиц значения (дней, долларов) — RANGE со смещением.

В хорошем ответе на собеседовании стоит честно отметить различия в поддержке:

• PostgreSQL поддерживает RANGE со смещениями (начиная с версии 11) и полностью поддерживает ROWS.
• сервер SQL поддерживает ROWS и RANGE, но RANGE — только с UNBOUNDED/CURRENT ROW, без числовых смещений.
• MySQL 8 поддерживает оба варианта, но набор типов смещения для RANGE ограничен.

Если числовое смещение RANGE недоступно, его можно воспроизвести с помощью соединения таблицы с самой собой или сгенерированного календаря. Упоминание этого показывает понимание особенностей промышленной эксплуатации.

Существует малоизвестный третий режим задания рамки: GROUPS. Он считает группы равных значений, а не строки и не значения. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW включает текущую группу равных значений и группу перед ней.

Обычно он не требуется, но его упоминание в ответ на вопрос «существуют ли другие режимы рамки?» демонстрирует глубокое понимание темы. Он поддерживается в PostgreSQL 11+ и ещё в нескольких системах.

Сведём всю тему к одному предложению, которое можно сказать на собеседовании:

«Используйте ROWS, когда имеется в виду фиксированное количество физических строк, RANGE — когда имеется в виду логический диапазон значения, по которому выполняется сортировка, и помните, что одинаковые значения приводят к разнице: рамка по умолчанию RANGE объединяет их в одну группу».

Одного этого предложения достаточно, чтобы полностью и уверенно ответить на вопрос.

Если поместить обе рамки в один запрос, различие станет наглядным. Выполните этот запрос на данных с повторяющимися датами и сравните два столбца построчно.

Для уникальных дат столбцы полностью совпадают; для одинаковых дат столбец RANGE повторяет одну и ту же сумму для равных значений, тогда как столбец ROWS увеличивается на один шаг за раз.

Две строки имеют одинаковое значение ORDER BY при рамке по умолчанию. Что произойдёт?

ROWS считает физические строки; RANGE считает по логическому значению столбца сортировки и объединяет одинаковые значения в группы равных строк. Рамка по умолчанию для OVER (ORDER BY ...) — это RANGE, поэтому наивная текущая сумма может объединить одинаковые значения сортировки.

Выбирайте ROWS для «последних N записей» и скользящих окон, нечувствительных к пропускам; выбирайте RANGE со смещением для «последних N дней/долларов», когда нужно учитывать пропуски. Знание о существовании GROUPS будет дополнительным плюсом. Далее мы применим рамку ROWS для построения скользящих средних.