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

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

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

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

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

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

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

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

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

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

WITH balances AS (
    SELECT
        account_id,
        operation_date,
        amount,
        SUM(amount) OVER (
            PARTITION BY account_id
            ORDER BY operation_date
            ROWS UNBOUNDED PRECEDING
        ) AS balance
    FROM operations
)
SELECT
    account_id,
    operation_date,
    balance,
    balance - LAG(balance) OVER (
        PARTITION BY account_id
        ORDER BY operation_date
    ) AS balance_change
FROM balances;

В CTE сначала рассчитывается накопительный balance. Во внешнем запросе LAG получает предыдущее значение уже вычисленного баланса, поэтому уровни вычисления не конфликтуют.

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

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

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

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

В отчёте по счетам требовалось показать текущий баланс после каждой операции и изменение баланса относительно предыдущей операции. Попытка вычислить накопительную сумму и сразу передать её в LAG в одном выражении нарушала правило вложенности оконных функций.

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

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

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

Можно ли заменить отдельный уровень запроса повторением выражения первой оконной функции?

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

Обязательно ли CTE физически материализуется перед второй оконной функцией?

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

Что произойдёт, если первая оконная функция использует неуникальный порядок сортировки?

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