В прошлом уроке мы увидели, что SUM(...) OVER () считает агрегат «по всей выборке». Часто этого мало — нам нужно считать агрегат не по всему миру, а по группе: «доля заказа в выручке его дня», «средняя зарплата в его отделе», «общее число платежей этого клиента».
Здесь и появляется PARTITION BY.
Что делает PARTITION BY
PARTITION BY expression — это инструкция «разбей таблицу на независимые куски по этому ключу, и считай окно внутри каждого куска отдельно». Куски не пересекаются и в совокупности покрывают всю выборку.
Это очень похоже на GROUP BY синтаксически — но семантически разное.
Слева: GROUP BY country возвращает одну строку на страну с агрегатом. Справа: PARTITION BY country оставляет все строки клиентов и добавляет агрегат как новую колонку.
Каждой строке таблицы достаётся тот агрегат, который соответствует её партиции. Аня попала в партицию RU — у неё cohort=6 (всего шесть клиентов RU). Виктор в DE — у него cohort=3.
PARTITION BY country — каждому клиенту виден размер его страновой когорты:
Заметь: у Ани (RU) и Бориса (RU) одна и та же cohort_size=6 и одна и та же avg_birth_year. Это потому, что они в одной партиции. У Виктора (DE) — другие значения. PostgreSQL разбил таблицу на партиции по стране и для каждой посчитал свой агрегат.
Сравнение с GROUP BY
Поставим задачу: «выведи каждого клиента, его страну и среднюю дату регистрации по его стране».
С GROUP BY это решить нельзя в одном запросе — потому что GROUP BY коллапсирует. Нужно либо JOIN с агрегированным подзапросом, либо коррелированный подзапрос. Через окно — одна строка.
Та же задача через JOIN-с-агрегатом (старый способ) и через окно (новый). Сравни читаемость:
А вот то же самое через окно — короче и без дополнительного прохода по таблице.
Один проход — одно окно:
PostgreSQL внутри тоже агрегирует — но без явного JOIN, и обычно через один Sort + WindowAgg, что дешевле двух scan’ов с join’ом.
Несколько ключей в PARTITION BY
Можно партиционировать по нескольким колонкам — синтаксис как у GROUP BY.
Партиция по (country, method): средняя сумма платежа для каждой пары:
Каждый платёж получает свои avg_in_segment и segment_size — но это среднее и количество по своей паре (country, method), не по всей таблице. У платежа Ани (RU) карточкой свой контекст; у платежа Виктора (DE) wire’ом — свой.
Разные окна в одном SELECT
Это, пожалуй, самая мощная фишка окон: в одном запросе можно использовать много разных окон на одних и тех же данных. Каждое окно — независимо.
Три окна сразу: по всей таблице, по дню, по методу оплаты — в одном запросе:
Один проход по payments — три параллельных агрегации. PostgreSQL переиспользует sort’ы там, где может, поэтому такие запросы остаются эффективными даже на больших данных.
Когда использовать что: GROUP BY vs PARTITION BY
Сводная таблица в голове:
- GROUP BY — нужна сводка. Одна строка на каждую группу. После GROUP BY конкретные строки исчезли.
- PARTITION BY — нужны детали с контекстом группы. Все строки сохраняются, каждой добавляется агрегат «по своей партиции».
Если запрос звучит как «сколько X в каждой группе» — это GROUP BY. Если «покажи каждое X и сколько его в группе» — это окно.
Иногда задача требует обоих: сначала GROUP BY (например, агрегировать заказы по дням), потом окно поверх агрегированной таблицы (например, running total дневных сумм). Это нормальный паттерн, разберём в уроке 6.
Нюанс: окно без PARTITION BY
OVER () без PARTITION BY — это окно «одна партиция на всю выборку». Технически партиция всё равно есть — просто она одна, размером во всю таблицу.
Это удобно, когда нужна «доля от общего по всем строкам». Сравни с OVER (PARTITION BY ...) — там партиций много, и каждая строка видит только свою.
Производительность: партиция стоит дорого, если не свободна
PostgreSQL вычисляет окно через сортировку или хеш-партиционирование, потом проход по партициям с накоплением агрегата. Если данные уже отсортированы по ключу партиции (например, индекс + ORDER BY) — окно стоит O(N). Если нет — O(N log N) на сортировку.
Самая дорогая ошибка — использовать OVER (PARTITION BY non_indexed_column) на огромной таблице без сортированного скана. На малых данных (как наш курс) это незаметно; на проде с миллиардами строк — узнаваемая проблема.
Чек-лист
PARTITION BY ключразбивает таблицу на независимые окна по этому ключу.- В отличие от
GROUP BY, ни одна строка не пропадает — каждая получает агрегат своей партиции в дополнительной колонке. - Можно партиционировать по нескольким колонкам — синтаксис как у GROUP BY.
- В одном запросе можно использовать много разных окон одновременно — каждое со своим PARTITION BY.
- Эвристика: «сколько в группе» = GROUP BY; «покажи каждое и его место в группе» = PARTITION BY.
- Без
PARTITION BYвсё окно — одна партиция на всю выборку.