PARTITION BY и ORDER BY

PARTITION BY: независимые окна бок о бок

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

SELECT region, salesperson, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM sales;

Каждая строка West видит только строки West; каждая строка East — только East. region_total для продажи West — это сумма всех сумм West и ничего больше. Это ровно та роль, которую GROUP BY region играет в группированном запросе — но здесь строки никогда не схлопываются. Опусти PARTITION BY, и весь результат становится одной гигантской партицией (общий итог по всем строкам).

ORDER BY внутри OVER: порядок внутри партиции

ORDER BY внутри клаузы OVER (...) — временное измерение: он задаёт порядок строк внутри каждой партиции. Это полностью отдельно от внешнего ORDER BY запроса, который лишь сортирует финальный вывод. Внутренний ORDER BY окна — это то, что делает возможными «накопительные» и «относительно строки» вычисления: он даёт строкам последовательность, чтобы функция могла сказать «всё до сюда», «предыдущая строка», «ранг на текущий момент».

SELECT region, sale_date, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date) AS nth_sale,
       SUM(amount)  OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

ROW_NUMBER() нужен порядок, чтобы нумеровать 1, 2, 3 внутри каждого региона по дате. SUM с тем же окном теперь выдаёт накопительный итог — и вот это сюрприз.

Ловушка фрейма по умолчанию

Вот правило, которое кусает. Когда у OVER нет ORDER BY, фрейм окна — срез строк, которые функция реально агрегирует — по умолчанию равен всей партиции. Поэтому SUM(amount) OVER (PARTITION BY region) суммирует каждую строку региона: одно плоское число, повторённое.

В тот миг, когда ты добавляешь ORDER BY внутрь OVER, фрейм по умолчанию тихо меняется на RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — «от первой строки партиции до текущей строки». Это накопительный агрегат, а не агрегат на всю партицию. Тот же SUM, тот же PARTITION BY, но добавление внутреннего ORDER BY переворачивает плоский итог в накопительный.

-- Плоский итог региона (без внутреннего ORDER BY): то же значение на каждой строке West
SUM(amount) OVER (PARTITION BY region)

-- Накопительный итог (внутренний ORDER BY): растёт вниз по партиции
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date)

Это и есть баг из Хука. Автор хотел плоский итог региона, но добавил ORDER BY sale_date (возможно, скопировав у соседнего ROW_NUMBER), и фрейм тихо стал накопительным. Фикс — убрать внутренний ORDER BY для плоского итога, или — если одним колонкам порядок нужен, а другим нет — назвать окна явно, чтобы каждое получило нужный фрейм.