re:re:actable
Урок 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 BYPARTITION 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 — один раздел.

Документация

PostgreSQL: Window Function Calls.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам