Урок 022 минуты чтения
Что такое PARTITION BY
На этой странице
Кратко
PARTITION BY внутри OVER (...) делит строки на независимые группы-разделы, и оконная функция вычисляется отдельно в каждом: нумерация начинается заново, накопительная сумма сбрасывается, lag не «перепрыгивает» в соседнюю группу. В отличие от GROUP BY, строки при этом не схлопываются. Без PARTITION BY вся выборка — один раздел. Можно разбивать по нескольким столбцам и выражениям (PARTITION BY user_id, date_trunc('month', created_at)). Для эффективности полезен индекс, начинающийся со столбцов PARTITION BY и продолжающийся столбцами ORDER BY окна.
Вопрос
Что делает PARTITION BY в оконных функциях?
Пример
SELECT
id, category_id, title, price,
avg(price) OVER (PARTITION BY category_id) AS avg_in_category,
price - avg(price) OVER (PARTITION BY category_id) AS diff_from_avg,
row_number() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_in_category
FROM products;id | category | price | avg_in_category | rank_in_category
1 | phones | 900 | 650 | 1
2 | phones | 400 | 650 | 2
3 | laptops | 1500 | 1500 | 1 ← нумерация зановоС разделами и без
| Окно | Результат row_number() |
|---|---|
OVER (ORDER BY price DESC) | Сквозная нумерация по всем товарам |
OVER (PARTITION BY category_id ORDER BY price DESC) | Нумерация внутри каждой категории |
Несколько столбцов и выражения
-- номер заказа пользователя внутри месяца
SELECT id, user_id, created_at,
row_number() OVER (
PARTITION BY user_id, date_trunc('month', created_at)
ORDER BY created_at
) AS nth_in_month
FROM orders;PARTITION BY и GROUP BY
GROUP BY | PARTITION BY | |
|---|---|---|
| Строк в результате | По одной на группу | Все исходные |
| Доступ к столбцам строки | Только ключи и агрегаты | Все столбцы |
| Где пишется | Отдельная часть запроса | Внутри OVER |
Индекс
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
-- поддерживает OVER (PARTITION BY user_id ORDER BY created_at) без отдельной сортировкиЧастые ошибки
- Забыть
PARTITION BY, иlagберёт значение из соседнего пользователя. - Ожидать сокращения числа строк, как с
GROUP BY. - Разбивать по слишком детальному ключу (каждая группа из одной строки — окно бесполезно).
Что важно помнить
PARTITION BY= «для каждой группы отдельно».- Строки не схлопываются.
- Индекс
(partition cols, order cols)убирает сортировку.
Мини-шпаргалка
OVER (PARTITION BY a).OVER (PARTITION BY a, b ORDER BY c).- Без
PARTITION BY— один раздел.
Документация
Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.