Оконные функции: основы

Владелец Brew сидит над выгрузкой по постоянным клиентам и хмурится. Маркетолог принёс ему отчёт «сколько всего потратил каждый клиент»: Алиса — 1270, Борис — 730, Карина — 780. Цифры верные, но владельца они не устраивают. «Я не хочу знать только итог. Я хочу видеть, КАК он рос. Алиса потратила 1270 за три захода или за тридцать? После какой покупки она перешагнула тысячу? Когда мне стоило прислать ей купон?» — он тычет в отчёт пальцем. А в отчёте — одна строка на клиента. Сами покупки исчезли.

Это не вина маркетолога. Это то, что делает GROUP BY: он берёт группу строк и схлопывает её в одну. Чтобы получить итог по клиенту, мы пожертвовали самими покупками. Владельцу нужно и то, и другое сразу — каждая покупка на месте И итог рядом с ней. Ровно для этого и придуманы оконные функции.

Агрегат схлопывает, окно — нет

Оконная функция — это тот же агрегат, что вы уже знаете: sum, avg, count. Разница в одной приписке — OVER (...). И эта приписка меняет всё.

Агрегат с GROUP BY работает так: собрал строки в группы, по каждой группе посчитал, вернул по одной строке на группу. Семь покупок Brew превращаются в три строки — по одной на клиента. Исходные строки уничтожены, к ним уже не вернуться.

Оконная функция (sum(cents) OVER (...)) делает наоборот. Она тоже считает по набору строк — этот набор называется «окном», — но НЕ схлопывает его. Каждая из семи покупок остаётся на своём месте, а результат подсчёта доклеивается к ней отдельной колонкой. Семь строк на входе — семь строк на выходе, просто с новой колонкой. Именно этого и хотел владелец: покупки видны, итог рядом.

PARTITION BY режет таблицу на окна

Раз окно — это набор строк, для каждой строки нужно решить, какие соседи в него попадают. Это делает PARTITION BY.

sum(cents) OVER (PARTITION BY customer) говорит: для каждой строки окно — это все строки с тем же customer. Для любой покупки Алисы сумма считается по всем покупкам Алисы и равна 1270; в каждой её строке стоит одно и то же число. У Бориса своё окно — 730, у Карины своё — 780. PARTITION BY нарезал таблицу на непересекающиеся окна по клиенту, и в каждом окне sum посчиталась независимо.

А если PARTITION BY убрать вовсе? Тогда OVER () — пустые скобки — означает «одно окно на всю таблицу». sum(cents) OVER () складывает все семь покупок и даёт общий итог сети — 2780 — в каждой строке. Это удобно держать рядом: маркетолог сразу видит, какую долю общей выручки делает конкретный клиент, не выполняя второй запрос.

ORDER BY внутри окна превращает sum в running total

До сих пор сумма по клиенту была одинаковой во всех его строках — 1270 у Алисы и там, и там, и там. Это статичный итог. Чтобы увидеть, КАК он рос (вопрос владельца), добавим внутрь окна ORDER BY.

sum(cents) OVER (PARTITION BY customer ORDER BY day, id) читается так: внутри окна клиента упорядочь строки по дню, и для каждой строки сложи cents от начала окна до текущей строки включительно. Это и есть running total — накопленный итог. Для Алисы он растёт 300 → 750 → 1270: видно, что тысячу она перешагнула на третьей покупке. PARTITION BY customer обнуляет накопление на границе клиента — Борис начинает свои 250 с нуля, не подхватывая 1270 Алисы.

Зачем в ORDER BY второй ключ — id? Это детерминированный tie-break. Если у клиента две покупки в один день, ORDER BY day сам по себе не решает, какая раньше, — и накопление на этих двух строках может разложиться как угодно. id (он же GENERATED ALWAYS AS IDENTITY, то есть порядок вставки) разводит ничью однозначно, и вывод воспроизводится дословно при любом прогоне.

Та же разница картинкой — GROUP BY схлопывает, окно сохраняет:

plaintext

GROUP BY customer                  OVER (PARTITION BY customer)
7 покупок → 3 строки               7 покупок → 7 строк + колонка итога
 
Алиса  300 ┐                       Алиса  300 ┐
Алиса  450 ├→ Алиса  1270          Алиса  450 ├ итог 1270 в каждой строке Алисы
Алиса  520 ┘                       Алиса  520 ┘
Борис  250 ┐→ Борис   730          Борис  250 ┐ итог 730
Борис  480 ┘                       Борис  480 ┘
Карина 480 ┐→ Карина  780          Карина 480 ┐ итог 780
Карина 300 ┘                       Карина 300 ┘
                                   строки на месте, итог доклеен колонкой

Слева исходные покупки уничтожены, остались три итога; справа все семь строк целы, а итог приписан рядом. Само окно задаётся припиской OVER (...), и у неё три формы:

Форма	Окно для каждой строки	Что в колонке
`OVER ()`	вся таблица	общий итог сети (2780) рядом с каждой строкой
`OVER (PARTITION BY customer)`	строки того же клиента	статичный итог клиента (у Алисы 1270 во всех её строках)
`OVER (PARTITION BY customer ORDER BY day, id)`	от начала окна до текущей строки	running total (у Алисы 300 → 750 → 1270)

Что показывает наш код

Центр урока — query.sql. Три запроса показывают одну идею под тремя углами. CustomerTotals — обычный агрегат для контраста (он схлопывает). WindowTotals — та же sum, но как оконная: итог по клиенту и общий итог рядом с каждой покупкой. RunningTotal — добавляем ORDER BY внутрь окна и получаем накопление.

sql

-- name: WindowTotals :many
-- Та же sum, но как ОКОННАЯ функция: каждая из 7 покупок остаётся на месте.
SELECT customer,
       day::text AS day,
       cents,
       (sum(cents) OVER (PARTITION BY customer))::bigint AS customer_total,
       (sum(cents) OVER ())::bigint                      AS grand_total
FROM purchases_lab
ORDER BY customer, day, id;
 
-- name: RunningTotal :many
-- ORDER BY ВНУТРИ окна → накопленный итог от начала окна до текущей строки.
SELECT customer,
       day::text AS day,
       cents,
       (sum(cents) OVER (PARTITION BY customer ORDER BY day, id))::bigint AS running
FROM purchases_lab
ORDER BY customer, day, id;

Обратите внимание: ORDER BY в конце запроса (после FROM) и ORDER BY внутри OVER (...) — это два РАЗНЫХ упорядочивания. Первое сортирует итоговую выдачу для читабельности; второе задаёт порядок накопления внутри окна. Они независимы.

cmd/demo/main.go — тонкая обвязка: поднимает пул через pg.NewPool, зовёт три типизированных метода из сгенерированного internal/db/ и печатает результат через tabwriter. Вся SQL-логика — в query.sql, Go лишь раскладывает строки по колонкам.

Запуск

docker compose up -d
make lecture L=08-analytical-and-lateral/08-01-window-basics-partition-order T=db-reset
make lecture L=08-analytical-and-lateral/08-01-window-basics-partition-order

T=run — значение по умолчанию, поэтому второй команде цель можно не указывать. Изнутри каталога юнита это просто make db-reset и make run.

plaintext

1) Агрегат GROUP BY — покупки схлопнуты в одну строку на клиента:
КЛИЕНТ  покупок  сумма
Алиса   3        1270
Борис   2        730
Карина  2        780
 
2) Оконная sum OVER (...) — строки на месте, итоги доклеены колонкой:
КЛИЕНТ  день        сумма  итог клиента  общий итог
Алиса   2025-02-01  300    1270          2780
Алиса   2025-02-03  450    1270          2780
Алиса   2025-02-05  520    1270          2780
Борис   2025-02-02  250    730           2780
Борис   2025-02-04  480    730           2780
Карина  2025-02-01  480    780           2780
Карина  2025-02-06  300    780           2780
 
3) sum OVER (PARTITION BY customer ORDER BY day) — running total на клиента:
КЛИЕНТ  день        сумма  накоплено
Алиса   2025-02-01  300    300
Алиса   2025-02-03  450    750
Алиса   2025-02-05  520    1270
Борис   2025-02-02  250    250
Борис   2025-02-04  480    730
Карина  2025-02-01  480    480
Карина  2025-02-06  300    780

Проверьте арифметику по клиенту: Алиса 300+450+520=1270, Борис 250+480=730, Карина 480+300=780, общий итог 2780. В блоке 1 это итоги после схлопывания, в блоке 3 — последняя строка каждого окна. Числа сходятся — окно посчитало то же, что и GROUP BY, но не потеряло сами покупки.

Заборчик

Оконная функция считается на очень поздней стадии запроса — ПОСЛЕ WHERE, GROUP BY и HAVING. Практическое следствие неприятное: отфильтровать строки по значению оконной функции прямо в WHERE нельзя — в момент проверки WHERE окно ещё не посчитано. «Оставь только покупки, где накопление перевалило за 1000» в один уровень не записать; для таких top-N задач результат окна заворачивают в CTE (WITH ...) и фильтруют снаружи — этим займётся следующий юнит.
Для running total порядок ORDER BY внутри окна обязан быть ПОЛНЫМ. Оставишь только ORDER BY day, а у клиента две покупки в один день — накопление на этих ничьих ляжет недетерминированно и будет скакать между прогонами. Мы закрыли это вторым ключом id; в проде годится любая колонка с гарантированно уникальным порядком (первичный ключ или временная метка достаточной точности).
Когда в окне есть ORDER BY, но нет явного фрейма, Postgres подставляет умолчание RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Для нашего ряда с уникальным порядком оно даёт ровно то, что мы ждём, но на ничьих RANGE ведёт себя иначе, чем ROWS. Это уже территория фреймов — разберём её отдельно.
На больших данных окно нередко требует сортировки, а сортировка, не влезающая в память, проливается во временный файл на диск (Sort Method: external merge в плане, модуль 06). Сколько памяти выделить под сортировку (work_mem) и стоит ли поддержать окно индексом — забота вашего DBA, не строки SQL.

Что забрать с собой

Оконная функция — тот же агрегат (sum/avg/count), но с OVER (...); в отличие от GROUP BY она НЕ схлопывает строки, а доклеивает результат колонкой.
PARTITION BY режет таблицу на окна (здесь — по клиенту); OVER () без partition — одно окно на все строки, то есть общий итог.
ORDER BY ВНУТРИ окна превращает sum в running total — накопление от начала окна до текущей строки; tie-break (id) должен быть полным, иначе на ничьих накопление недетерминированно.
Фильтровать по значению оконной функции прямо в WHERE нельзя — окно считается позже; для этого результат окна заворачивают в CTE.

Это последнее ограничение — мостик в следующий юнит. Окно умеет посчитать сумму, но не умеет само отобрать «топ-3 клиента по выручке» или «первую покупку каждого клиента»: для этого нужно сначала проставить номер строки и ранг, а потом отфильтровать по нему снаружи. В юните 08-02 мы возьмём row_number, rank и dense_rank, завернём их в CTE и наконец решим ту самую задачу top-N, об которую здесь споткнулись.