Оконные функции SQL Server: порядок и границы окна

Оконные функции добавляют каждой строке накопительный остаток, предыдущее значение или порядковый номер, не сворачивая результат до одной строки на группу. Короткое выражение при этом может скрывать важное бизнес-решение. Какая проводка считается предыдущей? Получают ли операции одного дня одинаковый остаток? Участвуют ли в расчёте записи, которые отчёт не показывает?

Разделяйте секцию, порядок и рамку окна. PARTITION BY формирует независимые группы. ORDER BY внутри OVER задаёт последовательность вычисления. Рамка определяет участвующие строки для функций, поддерживающих такую настройку. Финальный ORDER BY управляет отображением результата и не заменяет остальные решения.

Проверяем одинаковые значения сортировки

В примере намеренно присутствуют две проводки с одной датой.

DECLARE @Ledger table (
    AccountId int, EntryId int, PostedOn date, Amount decimal(12,2)
);
INSERT @Ledger VALUES
(1, 1, '20250101', 100.00),
(1, 2, '20250101', -20.00),
(1, 3, '20250102', 50.00),
(2, 4, '20250101', 7.00);

SELECT AccountId, EntryId, Amount,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn
    ) AS DatePeerTotal,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningBalance,
    LAST_VALUE(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS FinalEntryAmount
FROM @Ledger
ORDER BY AccountId, PostedOn, EntryId;

Для счёта 1 колонка DatePeerTotal возвращает 80, 80 и 130. У упорядоченного агрегата без явно заданной рамки используется RANGE, включающий строки с таким же значением сортировки. Поэтому обе проводки за 1 января сразу видят сумму всего дня.

RunningBalance возвращает 100, 80 и 130. Явная рамка ROWS накапливает сумму в уникальной последовательности PostedOn, EntryId. Одного слова ROWS недостаточно, если порядок равных дат остаётся неоднозначным. Когда бизнес-порядок проводок отличается от порядка идентификаторов, его необходимо отдельно хранить и использовать в сортировке.

FinalEntryAmount равен 50 для каждой строки счёта 1. Рамка явно продолжается до конца секции. LAST_VALUE с рамкой до текущей строки часто возвращает текущее значение или значение последней равной строки, а не последнее значение всего счёта.

Для счёта 2 вычисление независимо и даёт 7. Отсутствие PARTITION BY смешало бы разные счета. Правильная итоговая сумма сама по себе не доказывает правильность промежуточных остатков.

Фильтр меняет вход вычисления

Следующий запрос выбирает последнюю проводку каждого счёта. Убывающий идентификатор разрешает совпадение дат.

;WITH Ranked AS (
    SELECT AccountId, EntryId, PostedOn, Amount,
        ROW_NUMBER() OVER (
            PARTITION BY AccountId
            ORDER BY PostedOn DESC, EntryId DESC
        ) AS rn
    FROM @Ledger
)
SELECT AccountId, EntryId, PostedOn, Amount
FROM Ranked
WHERE rn = 1
ORDER BY AccountId;

Фильтр по rn находится снаружи, поскольку результат оконной функции недоступен в WHERE на том же уровне запроса. Внешний запрос также позволяет показывать ограниченный период, сохраняя более ранние проводки для расчёта остатка.

Если перед суммированием оставить только 2 января, для счёта 1 получится 50 вместо полного остатка 130. При необходимости входящего остатка можно вычислить окно по нужной истории, а потом отфильтровать отображение. Другой вариант: отдельно получить начальную сумму и добавить движения периода. Это способно уменьшить объём работы, но обе части требуют согласованного состояния данных.

LAG означает предыдущую строку в заданной последовательности, а не обязательно предыдущий календарный день. Пропущенные даты автоматически не появляются. Для ежедневного сравнения иногда нужно сначала агрегировать данные по дням, затем присоединить календарь с днями без движений. Иначе недельный перерыв будет выглядеть как обычный переход между соседними строками.

Стоимость зависит от входной истории

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

Проверяйте фактическое число строк, выгрузку сортировок на диск, выделенную память и объём входа оконных операторов. Несколько видимых строк отчёта могут требовать обработки большой истории. Внешний TOP не делает вычисление по всей секции автоматически дешёвым.

Включайте в тесты совпадающие даты, разные счета, отрицательные суммы, секцию из одной строки и период отображения после первой проводки. Если Amount допускает NULL, определите, означает ли неизвестная сумма ошибку данных или её разрешено пропускать. Для отчётности полезно также проверить, что повторный запуск на неизменных данных выбирает те же последние записи. Запрос должен явно объяснять порядок и состав каждого вычисленного результата.

Техническая документация: Microsoft Learn: OVER clause · Microsoft Learn: ROW_NUMBER · Microsoft Learn: LAST_VALUE.