Что такое PARTITION BY в SQL? Группы внутри окна для начинающих

PARTITION BY — это часть OVER-выражения в оконных функциях. Она делит строки на группы, и оконная функция применяется внутри каждой группы независимо.

Главное отличие от GROUP BY:

• GROUP BY схлопывает группу в одну строку.
• PARTITION BY сохраняет все строки и просто добавляет колонку с расчётом по группе.

Это огромная разница на практике. С PARTITION BY ты можешь в одном запросе показать «каждый заказ + сумма заказов всего клиента + место заказа в его истории» — три цифры в одной строке.

Зачем нужен PARTITION BY

Сценарии, где GROUP BY не подходит:

• Хочется видеть каждую строку, плюс агрегат по её группе.
• Нужны несколько разных агрегаций (COUNT, SUM, AVG) одновременно.
• Нужны оконные функции (ROW_NUMBER, LAG, LEAD) — они без PARTITION BY бесполезны для группировок.

Без PARTITION BY пришлось бы делать JOIN к подзапросу с GROUP BY. Это длиннее и не всегда читаемо.

Базовый синтаксис

функция(...) OVER (
  PARTITION BY колонка1, колонка2, ...
  ORDER BY колонка3, ...
)

PARTITION BY идёт первым внутри OVER. После — ORDER BY (если функция требует порядка).

PARTITION BY vs GROUP BY на одних данных

Таблица orders:

id	customer_id	amount
1	1	100
2	1	250
3	2	80
4	1	150
5	2	300

GROUP BY — одна строка на клиента:

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

customer_id	total
1	500
2	380

PARTITION BY — все строки + сумма по группе в каждой:

SELECT id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

id	customer_id	amount	customer_total
1	1	100	500
2	1	250	500
3	2	80	380
4	1	150	500
5	2	300	380

5 строк остались, но в каждой видна сумма всего клиента. Это позволяет, например, посчитать долю каждого заказа в общей сумме клиента — amount / customer_total.

Типичные применения

1. Топ-N в каждой группе

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

«Последние 3 заказа каждого клиента». Самый частый use case оконных функций.

2. Доля в группе

SELECT
  customer_id,
  id AS order_id,
  amount,
  ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders;

«Какую долю текущий заказ составляет в общей сумме клиента». Нужно для аналитики, отчётов, рекомендаций.

3. Сравнение строки с её группой

SELECT
  product_id, name, price,
  AVG(price) OVER (PARTITION BY category) AS avg_in_category,
  price - AVG(price) OVER (PARTITION BY category) AS diff_from_avg
FROM products;

Каждый продукт сравнивается со средней ценой в своей категории. «Дешевле/дороже среднего по категории».

4. Несколько агрегатов в одной строке

SELECT
  customer_id, id, amount,
  COUNT(*)        OVER (PARTITION BY customer_id) AS total_orders,
  MAX(amount)     OVER (PARTITION BY customer_id) AS biggest_order,
  MIN(created_at) OVER (PARTITION BY customer_id) AS first_order_at
FROM orders;

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

Несколько колонок в PARTITION BY

ROW_NUMBER() OVER (
  PARTITION BY customer_id, EXTRACT(YEAR FROM created_at)
  ORDER BY amount DESC
)

Группа = пара (customer_id, год). «Топ-1 заказ у каждого клиента в каждом году».

Без PARTITION BY

Если опустить PARTITION BY, окно — это вся таблица (или результат предыдущего FROM/WHERE):

SELECT
  id, amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
  SUM(amount) OVER () AS grand_total
FROM orders;

ROW_NUMBER пронумерует все заказы глобально. SUM(amount) OVER () — общая сумма по всем заказам, в каждой строке.

Важно: SUM с ORDER BY = running total

Это сюрприз для многих:

SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at)

Это НЕ «сумма по группе». Это running total — нарастающий итог: сумма всех заказов клиента ОТ начала ДО текущей строки.

Если нужна именно сумма всей группы — без ORDER BY:

SUM(amount) OVER (PARTITION BY customer_id)

Без ORDER BY Postgres покрывает всё окно, не «до текущей». Это частая ошибка с агрегатами в окне.

Частые ошибки новичков

1. PARTITION BY забыт. Хотел top-3 на клиента, забыл PARTITION BY customer_id — пронумеровал всё глобально. Получил «топ-3 за всю историю», не «топ-3 на клиента».

2. Путают PARTITION BY с GROUP BY. PARTITION BY не схлопывает строки. Если хотел «одна строка на клиента» — нужен GROUP BY или подзапрос с WHERE rn = 1.

3. Running total там, где не нужен. SUM(x) OVER (PARTITION BY y ORDER BY z) — это running total. Если хотел сумму всей группы — убери ORDER BY.

4. Использование оконной функции в WHERE. Нельзя — окно вычисляется после WHERE. Оборачивай в подзапрос:

-- НЕ РАБОТАЕТ
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders WHERE rn = 1;

-- РАБОТАЕТ
WITH ranked AS (...)
SELECT * FROM ranked WHERE rn = 1;

5. NULL в PARTITION BY. NULL'ы попадают в одну партицию (Postgres трактует NULL = NULL для целей PARTITION BY). Если хочешь их игнорировать — WHERE column IS NOT NULL заранее.

6. Множественные разные окна. Если в SELECT три оконные функции с разными OVER — Postgres сделает три сортировки. Если возможно, переиспользуй окно через WINDOW clause:

SELECT
  ROW_NUMBER() OVER cust_window AS rn,
  COUNT(*)    OVER cust_window AS total
FROM orders
WINDOW cust_window AS (PARTITION BY customer_id ORDER BY created_at);

Мини-резюме

• PARTITION BY делит строки на группы для оконной функции, но не схлопывает их.
• Внутри OVER идёт первым: PARTITION BY ... ORDER BY ....
• Несколько колонок — PARTITION BY a, b — группа = пара значений.
• Без PARTITION BY окно = вся таблица.
• SUM/COUNT/AVG OVER (PARTITION BY ... ORDER BY ...) без явного frame — это running total, не сумма группы.
• В WHERE оконные функции напрямую нельзя — оборачивай в CTE/подзапрос.
• NULL'ы в PARTITION BY группируются вместе.