Використання PARTITION BY для розділення даних на групи

Уяви собі, що ти працюєш офіціантом (або баристою, якщо ти фанат кави) у великому ресторані. Щодня ти підбиваєш підсумок по зароблених чайових. Але є нюанс: ресторан поділений на зони, і тебе цікавить, скільки чайових зароблено в кожній зоні окремо. PARTITION BY — це те, що SQL юзає для "розділення ресторану на зони".

Більш формально, PARTITION BY використовується у window-функціях для розділення всіх рядків таблиці на окремі групи (або "розділи"). Усередині кожної групи window-функція виконується заново. Це як ніби ти застосовуєш функцію окремо в кожному "розділі".

Приклад: як це працює

Допустимо, у нас є таблиця sales з даними про продажі:

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

Синтаксис PARTITION BY

Синтаксис дуже простий:

window_function() OVER (PARTITION BY стовпець_або_стовпці)

• window_function() — це, наприклад, SUM(), AVG(), ROW_NUMBER() і так далі.
• PARTITION BY стовпець — вказує, по якому стовпцю треба розділити рядки.
• OVER() — це оператор, який каже SQL: "Зроби щось в рамках вказаного window".

Приклад: сума по групах

Давай порахуємо суму продажів для кожного регіону:

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

Порядок рядків у window важливий для функцій, таких як RANK() чи ROW_NUMBER(). Будь уважний, коли юзаєш ORDER BY всередині OVER().