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

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

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

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

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

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

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

Если попытаться отобрать строки по результату оконной функции в WHERE того же уровня, запрос будет некорректен: WHERE обрабатывается раньше оконного вычисления. Ошибка возникает не из-за значения рейтинга, а из-за порядка логических фаз запроса.

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

Логический порядок обычно представляют так: FROM и JOIN, WHERE, GROUP BY, HAVING, вычисление выражений SELECT с оконными функциями, затем ORDER BY. Это модель семантики запроса, а не обязательная последовательность физических действий внутри оптимизатора.

Сначала нужно создать отдельный уровень запроса, на котором рейтинг станет обычным столбцом. Затем внешний запрос сможет использовать этот столбец в WHERE:

WITH ranked AS (
    SELECT
        category_id,
        product_id,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY price DESC
        ) AS position
    FROM products
)
SELECT category_id, product_id, price
FROM ranked
WHERE position <= 3;

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

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

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

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

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

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

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

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

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

Можно ли заменить внешний запрос фильтрацией в HAVING?

Обычно это не является корректной заменой. HAVING фильтрует группы после GROUP BY, а оконная функция вычисляется позже и не становится агрегатным выражением только из-за помещения условия в HAVING. Если оконный результат нужно отфильтровать, используйте отдельный уровень запроса или поддерживаемую СУБД конструкцию QUALIFY.

Почему агрегат можно передать в оконную функцию, хотя оконный результат нельзя передать в WHERE?

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

Почему одинаковая сортировка не гарантирует стабильный результат ROW_NUMBER?

Если несколько строк имеют одинаковые значения всех выражений в ORDER BY окна, СУБД не обязана назначать им номера в одном и том же порядке. Это особенно важно при фильтрации по первой строке: выбранная строка может меняться между запусками или планами. Для детерминированного результата добавляют уникальный или иной однозначно определяющий порядок столбец.