Допустим, аналитический отчёт должен показать 10 лучших товаров вместе с их долей в общей выручке. Если ограничение на 10 строк задано в том же запросе, что определит знаменатель оконной доли?
Ограничение LIMIT или FETCH не уменьшает набор строк, над которым оконная функция уже вычислила долю. Знаменатель будет рассчитан по всем строкам, оставшимся после фильтрации и группировки, но до ограничения итогового вывода. Поэтому десять лучших товаров получат доли от общей выручки, а не от выручки только этих десяти товаров.
Обычная агрегация решает задачу свёртки: несколько строк превращаются в одну строку группы. Для аналитических отчётов этого часто недостаточно — требуется сохранить детализацию и одновременно показать общий итог, долю, ранг или накопительный показатель.
Оконные функции появились как механизм вычисления по связанному набору строк без схлопывания результата. Ограничение количества возвращаемых строк, напротив, относится к финальной выдаче и не должно менять смысл уже вычисленных аналитических показателей.
Если сначала выбрать только десять товаров, а затем вычислить оконную сумму, знаменатель будет равен выручке этих десяти товаров. Это другая метрика: доля внутри топ-10, а не доля товара в общей выручке.
Ошибка особенно опасна тем, что оба результата выглядят правдоподобно. В первом случае сумма долей топ-10 может быть меньше 100%, во втором она будет равна 100%, хотя отчёт должен отражать вклад товаров во всём наборе.
В логической модели обработки запроса сначала применяются источники данных, WHERE, группировка и HAVING, затем вычисляются оконные функции, после чего выполняются сортировка и ограничение результата. Физический план СУБД может отличаться от этой последовательности, но его оптимизации обязаны сохранять тот же результат.
Минимальный пример правильной логики:
Оконная сумма в scored видит все четыре строки. LIMIT 2 оставляет только товары A и B, но их доли равны 500/1000 и 300/1000, а не 500/800 и 300/800.
Если же ограничение поместить во внутренний запрос, а оконную функцию — во внешний, окно увидит только ограниченные строки. Такой вариант корректен лишь тогда, когда требуется доля внутри топ-10. Для доли от общего набора сначала нужно вычислить оконный показатель, затем отфильтровать или ограничить уже готовый результат.
Следует также различать ORDER BY и ограничение результата. Порядок сортировки определяет, какие строки попадут в топ, но сам по себе не меняет набор строк, доступный оконной функции. Если критерий отбора топа отличается от критерия аналитического расчёта, их лучше явно разделить вложенным запросом или CTE.
В интернет-магазине руководитель попросил показать десять самых прибыльных товаров и их долю в выручке за месяц. Команда сначала ограничила агрегированные продажи десятью строками, а затем рассчитала долю оконной суммой. В результате отчёт показывал структуру топ-10, хотя подпись утверждала, что это доля от всей выручки.
Рассматривались два варианта. Первый — вычислять сумму только после отбора топ-10; он проще и подходит для анализа состава топ-группы, но не отвечает на вопрос о доле в общем обороте. Второй — сначала агрегировать продажи всех товаров и вычислить долю оконной функцией, затем отсортировать и ограничить выдачу; он сохраняет требуемую семантику и был выбран.
После исправления сумма долей показанных товаров стала меньше 100%, что ожидаемо: остальные товары остаются за пределами отчёта. Отдельно в пояснении отчёта указали, что доля считается от всей месячной выручки, а не от выручки топ-10.
Изменит ли ORDER BY внутри оконного определения знаменатель общей доли?
Нет, если оконная функция использует тот же набор строк и сортировка нужна только для порядка вычисления. Для общей суммы выражение вроде SUM(revenue) OVER () не имеет оконного порядка и возвращает один и тот же общий знаменатель для каждой строки. Если добавить ORDER BY в окно, семантика может измениться на накопительное вычисление, поэтому это уже не просто сортировка результата.
Что произойдёт, если перед оконной функцией применяется WHERE?
Оконная функция увидит только строки, прошедшие WHERE. Поэтому доля будет рассчитана относительно отфильтрованного набора, например относительно продаж конкретного месяца или региона. Если нужна доля от общего набора, фильтр для отображаемых строк иногда приходится выносить во внешний запрос после вычисления оконного показателя.
Почему для отбора топ-10 по оконному рангу обычно нужен внешний запрос?
Ранг вычисляется оконной функцией, а обычная фильтрация WHERE логически выполняется раньше неё. Поэтому фильтр по рассчитанному рангу нельзя корректно применить на том же уровне запроса; сначала ранг вычисляют во внутреннем запросе или CTE, затем фильтруют во внешнем. При этом внешний фильтр удаляет строки из результата, но не меняет уже рассчитанный ранг или знаменатель, если аналитика была вычислена внутри.