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

Порядок в OVER (ORDER BY ...) задаёт последовательность только для вычисления оконной функции. Он не сортирует итоговый набор строк. Чтобы гарантировать порядок выдачи, нужен отдельный внешний ORDER BY в самом запросе.

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

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

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

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

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

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

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

ORDER BY внутри оконной спецификации определяет, какие строки считать предыдущими, следующими или расположенными в заданной последовательности при вычислении функции. Внешний ORDER BY определяет порядок строк, возвращаемых клиенту. Это два независимых уровня сортировки.

SELECT
    sale_id,
    sale_date,
    amount,
    amount - LAG(amount) OVER (
        ORDER BY sale_date, sale_id
    ) AS change_from_previous
FROM sales
ORDER BY sale_date, sale_id;

В примере внутренний ORDER BY нужен для правильного выбора предыдущей продажи, а внешний — для гарантированного порядка результата. Удаление внешней сортировки не обязательно изменит вычисленные значения, но лишит запрос гарантии относительно расположения строк.

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

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

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

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

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

Рассматривались два варианта. Можно было полагаться на индекс по дате, но это не гарантирует порядок результата и связывает корректность отчёта с планом выполнения. Можно было отсортировать результат внешним ORDER BY по клиенту, дате и уникальному идентификатору; этот вариант явно фиксирует контракт выдачи.

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

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

Достаточно ли ORDER BY внутри окна для детерминированного ROW_NUMBER?

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

Гарантирует ли внешний ORDER BY правильное вычисление оконной функции?

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

Можно ли считать порядок строк гарантированным, если запрос несколько раз подряд возвращает их одинаково?

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