В отчёте нужен скользящий итог без вклада текущей строки. Какой механизм оконной рамки задаёт это поведение?

Краткий ответ

Для исключения текущей строки из уже сформированной оконной рамки используется механизм EXCLUDE CURRENT ROW. Он удаляет из кадра именно текущую строку, но не обязательно строки-«соседи» с тем же значением сортировки.

Это отличается от ограничения рамки строкой перед текущей: при RANGE или GROUPS в кадре могут оставаться строки с тем же значением ORDER BY.

Исторический контекст

Оконные функции появились как способ выполнять аналитические вычисления по связанному набору строк, не схлопывая результат до одной строки на группу. Это решило типичную проблему отчётов: можно было показывать исходные записи одновременно с накопительными, скользящими и сравнительными показателями.

По мере усложнения аналитики стало важно управлять не только границами кадра, но и составом строк внутри него. Механизм EXCLUDE позволяет исключать текущую строку или строки-ровесники без изменения общей логики окна.

Постановка проблемы

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

Неверное исключение особенно заметно при одинаковых значениях сортировки. Несколько строк могут считаться ровесниками, поэтому исключение всей группы строк даст другой результат, чем исключение только физически текущей строки.

Подробное решение

EXCLUDE CURRENT ROW применяется после определения оконной рамки. Сначала SQL выбирает раздел, порядок и границы кадра, затем исключает из него текущую строку перед вычислением агрегата.

SELECT
    operation_date,
    amount,
    SUM(amount) OVER (
        ORDER BY operation_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        EXCLUDE CURRENT ROW
    ) AS total_before_current
FROM operations;

Для первой строки кадр после исключения может оказаться пустым. В этом случае SUM обычно возвращает NULL, тогда как COUNT возвращает ноль; при необходимости результат нормализуют через COALESCE.

Важно различать варианты исключения:

• EXCLUDE CURRENT ROW — исключает только текущую строку;
• EXCLUDE GROUP — исключает текущую строку и всех её ровесников по оконному порядку;
• EXCLUDE TIES — исключает ровесников, но оставляет текущую строку;
• EXCLUDE NO OTHERS — стандартное поведение без исключения.

Понятие «ровесники» зависит от оконного порядка и от типа рамки. При ROWS строки рассматриваются как отдельные позиции. При RANGE или GROUPS одинаковые значения сортировки могут формировать общий набор ровесников.

Если требуется строго сумма предыдущих физических строк, часто понятнее задать рамку до строки перед текущей и обеспечить детерминированный порядок уникальным ключом. EXCLUDE CURRENT ROW полезен, когда нужно сохранить широкую рамку, но убрать только текущую запись. Поддержка синтаксиса EXCLUDE различается между СУБД, поэтому перед использованием нужно проверить документацию конкретной системы.

Ситуация из практики

В отчёте по платежам нужно сравнивать каждый платёж с общей суммой платежей клиента за выбранный период, не включая сам платёж. Команда рассматривала три варианта.

Первый вариант — посчитать общий итог отдельным агрегатным запросом и присоединить его к платежам. Он совместим с большим числом СУБД, но усложняет запрос и может привести к дублированию данных при неосторожном соединении.

Второй вариант — использовать рамку до предыдущей строки. Он хорошо подходит для последовательного накопительного итога, но требует однозначного порядка. При одинаковых датах без идентификатора порядок строк может быть недостаточно определённым.

Третий вариант — использовать EXCLUDE CURRENT ROW в оконной рамке. Он непосредственно выражает требование «всё окно, кроме текущей записи» и был выбран при наличии поддержки в используемой СУБД. Для переносимого решения добавили уникальный ключ в сортировку и применили рамку до предыдущей строки.

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

Что кандидаты часто упускают

1. Чем отличается EXCLUDE CURRENT ROW от рамки до строки перед текущей?

Рамка до строки перед текущей изменяет верхнюю границу кадра. EXCLUDE CURRENT ROW сначала формирует кадр, а затем убирает из него текущую строку. Поэтому при RANGE кадр после исключения текущей строки всё ещё может содержать другие строки с тем же значением сортировки.

2. Что произойдёт при одинаковых значениях ORDER BY?

При EXCLUDE CURRENT ROW строки с тем же значением сортировки обычно останутся в кадре, если они не являются самой текущей строкой. Если требуется исключить весь набор таких строк, нужен EXCLUDE GROUP; если нужно оставить текущую, но убрать остальных ровесников, используется EXCLUDE TIES.

3. Почему результат может быть NULL для первой строки?

После исключения текущей строки кадр первой записи может стать пустым. Агрегат SUM не имеет значения для пустого набора и возвращает NULL, тогда как COUNT возвращает ноль. Это важно учитывать при дальнейшей арифметике: NULL может распространиться на выражение, если его явно не заменить, например, нулём.