Использование PARTITION BY для разделения данных на группы

Представьте себе, что вы работаете официантом (или баристой, если вы любите кофе) в большом ресторане. Каждый день вы подводите итог по заработанным чаевым. Но есть нюанс: ресторан разделён на зоны, и вас интересует, сколько чаевых заработано в каждой зоне отдельно. PARTITION BY — это то, что SQL использует для "разделения ресторана на зоны".

Более формально, PARTITION BY используется в оконных функциях для разделения всех строк таблицы на отдельные группы (или "разделы"). Внутри каждой группы оконная функция выполняется заново. Это как если бы вы применяли функцию отдельно в каждом "разделе".

Пример: как это работает

Допустим, у нас есть таблица sales с данными о продажах:

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

Синтаксис PARTITION BY

Синтаксис довольно прост:

оконная_функция() OVER (PARTITION BY столбец_или_столбцы)

• оконная_функция() — это, например, SUM(), AVG(), ROW_NUMBER() и так далее.
• PARTITION BY столбец — указывает, по какому столбцу нужно разделить строки.
• OVER() — это оператор, который говорит SQL: "Выполни что-то в рамках указанного окна".

Пример: сумма по группам

Давайте вычислим сумму продаж для каждого региона:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales;

Результат будет таким:

Что происходит? SQL разбивает строки на группы по значению столбца region (North и South), затем применяет функцию SUM() отдельно для каждой группы. Как результат, строки внутри группы "North" получают одно и то же значение суммы, а строки внутри группы "South" — другое.

Примеры использования PARTITION BY

Давайте рассмотрим, как PARTITION BY может быть полезен в задачах реального мира.

Пример 1: Ранжирование внутри группы

Предположим, мы хотим ранжировать продавцов внутри каждого региона по количеству продаж. Для этого можно использовать комбинацию PARTITION BY и функции RANK():

SELECT
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales;

Результат:

Функция RANK() присваивает ранг внутри каждой группы region, начиная с 1. Заметьте, что для каждой группы ранги начинаются с единицы.

Пример 2: Сравнение каждого значения со средним в группе

Допустим, мы хотим посмотреть, сколько каждый продавец заработал относительно среднего по своему региону. Используем AVG():

SELECT
    region,
    salesperson,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_sales_by_region,
    amount - AVG(amount) OVER (PARTITION BY region) AS diff_from_avg
FROM sales;

Результат:

Сначала SQL разделяет строки на группы по region. Затем вычисляет среднее значение AVG(amount) для каждой группы. Наконец, для каждой строки вычисляет разницу между её значением и средним.

Пример 3: Нумерация строк внутри группы

Скажем, вы хотите пронумеровать все транзакции внутри каждой группы региона. Используем ROW_NUMBER():

SELECT
    region,
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_number
FROM sales;

Результат:

Сравнение с GROUP BY

Нередко возникает путаница между PARTITION BY и GROUP BY. Давайте сравним их:

GROUP BY

GROUP BY изменяет структуру выходного результата — он преобразует строки таблицы в агрегаты. Например:

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Результат:

Здесь мы теряем информацию о продавцах, так как данные агрегируются.

PARTITION BY

PARTITION BY, наоборот, не изменяет структуру. Мы всё ещё видим каждую строку, но у нас есть дополнительные значения, рассчитанные по группам. То есть, PARTITION BY позволяет агрегировать без потери деталей.

Частые ошибки при использовании PARTITION BY

Ошибка 1: Забыли PARTITION BY

Иногда вы хотите группировать данные, но забываете использовать PARTITION BY. Например:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_sales
FROM sales;

Результат:

Здесь SUM(amount) рассчитана для всей таблицы, а не отдельно для каждого региона. Если хотите учитывать регионы, не забудьте указать PARTITION BY region.

Ошибка 2: Неправильный порядок в ORDER BY

Порядок строк в рамках окна важен для функций, таких как RANK() или ROW_NUMBER(). Будьте внимательны, когда используете ORDER BY внутри OVER().