Пошук уроків, статей та іншого контенту
Виконуйте обчислення по групах рядків, не втрачаючи деталізації результату, використовуючи OVER та PARTITION BY.
Віконна функція виконує обчислення для набору пов’язаних рядків, який називається вікном, але не об’єднує ці рядки в один результат.
Для порівняння, GROUP BY повертає один рядок на кожну групу:
SELECT department, SUM(salary)
FROM employees
GROUP BY department;У результаті окремі працівники зникають, а залишається лише підсумок для кожного відділу.
Віконна функція дає змогу одночасно отримати:
дані окремого рядка;
підсумок або середнє для його групи;
позицію рядка в групі;
накопичувальне значення;
інші обчислення над пов’язаними рядками.
Загальний синтаксис:
window_function(...) OVER (
PARTITION BY column_name
ORDER BY column_name
)Ключове слово OVER перетворює агрегатну або спеціальну функцію на віконну.
PARTITION BYPARTITION BY ділить результат на незалежні групи — партиції. Обчислення виконується окремо в кожній партиції, але всі початкові рядки залишаються в результаті.
Розглянемо таблицю замовлень:
CREATE TEMP TABLE orders (
order_id integer,
customer_id integer,
order_date date,
amount numeric(10, 2)
);
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES
(1, 101, '2026-01-05', 120.00),
(2, 101, '2026-01-12', 80.00),
(3, 102, '2026-01-07', 250.00),
(4, 102, '2026-01-18', 100.00),
(5, 103, '2026-01-10', 75.00);Тепер обчислимо загальну суму замовлень кожного клієнта:
SELECT
order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders
ORDER BY customer_id, order_date;Результат матиме приблизно такий вигляд:
order_id | customer_id | order_date | amount | customer_total
----------+-------------+------------+--------+----------------
1 | 101 | 2026-01-05 | 120.00 | 200.00
2 | 101 | 2026-01-12 | 80.00 | 200.00
3 | 102 | 2026-01-07 | 250.00 | 350.00
4 | 102 | 2026-01-18 | 100.00 | 350.00
5 | 103 | 2026-01-10 | 75.00 | 75.00Для кожного рядка PostgreSQL знаходить усі замовлення з таким самим customer_id і рахує їхню суму. При цьому кожне замовлення залишається окремим рядком.
У віконному режимі можна використовувати знайомі агрегатні функції:
SUM() — сума;
AVG() — середнє значення;
COUNT() — кількість;
MIN() — мінімальне значення;
MAX() — максимальне значення.
Наприклад, можна одночасно вивести суму, середню вартість і кількість замовлень клієнта:
SELECT
order_id,
customer_id,
amount,
COUNT(*) OVER (
PARTITION BY customer_id
) AS customer_orders_count,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total,
AVG(amount) OVER (
PARTITION BY customer_id
) AS customer_average
FROM orders
ORDER BY customer_id, order_id;На відміну від звичайного GROUP BY, цей запит не потребує додаткового JOIN, щоб повернути властивості окремого замовлення разом з інформацією про клієнта.
GROUP BY і PARTITION BYGROUP BY змінює рівень деталізації результату:
SELECT
customer_id,
SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id;Результат містить один рядок на клієнта.
PARTITION BY не змінює кількість рядків:
SELECT
order_id,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders;Результат містить один рядок на замовлення, але кожен рядок додатково має суму замовлень свого клієнта.
Отже:
GROUP BY використовують, коли потрібен підсумок на рівні групи;
PARTITION BY використовують, коли потрібні дані окремих рядків і обчислення на рівні групи.
ORDER BY у вікніPARTITION BY визначає, які рядки належать до однієї групи. ORDER BY визначає порядок рядків усередині кожної групи.
Це особливо важливо для:
накопичувальних сум;
нумерації рядків;
ранжування;
функцій, результат яких залежить від порядку.
Порахуємо накопичувальну суму замовлень кожного клієнта:
SELECT
order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date, order_id;Для клієнта 101 результат буде таким:
order_id | order_date | amount | customer_running_total
----------+------------+--------+------------------------
1 | 2026-01-05 | 120.00 | 120.00
2 | 2026-01-12 | 80.00 | 200.00Для клієнта 102 накопичення починається знову:
order_id | order_date | amount | customer_running_total
----------+------------+--------+------------------------
3 | 2026-01-07 | 250.00 | 250.00
4 | 2026-01-18 | 100.00 | 350.00PARTITION BY customer_id не дає замовленням різних клієнтів впливати одне на одного.
ORDER BYЯкщо дві події можуть мати однакову дату, бажано додати унікальний або однозначний стовпець:
ORDER BY order_date, order_idorder_id визначає порядок замовлень з однаковою датою і робить накопичення передбачуваним.
Віконні функції можуть не лише агрегувати значення, а й нумерувати або ранжувати рядки.
Наприклад, визначимо місце кожного замовлення за сумою серед замовлень його клієнта:
SELECT
order_id,
customer_id,
amount,
RANK() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS amount_rank
FROM orders
ORDER BY customer_id, amount_rank;RANK() присвоює однакове місце однаковим значенням. Якщо кілька рядків мають однаковий результат, наступний ранг може мати пропуск.
Для послідовної нумерації кожного рядка використовується ROW_NUMBER():
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, order_id
) AS row_number
FROM orders
ORDER BY customer_id, row_number;ROW_NUMBER() завжди створює послідовність 1, 2, 3 тощо. Тому додатковий order_id допомагає визначити порядок рядків, якщо суми однакові.
Віконні функції зручно використовувати для розрахунку частки окремого рядка від загального значення групи:
SELECT
order_id,
customer_id,
amount,
ROUND(
amount / SUM(amount) OVER (
PARTITION BY customer_id
) * 100,
2
) AS percent_of_customer_total
FROM orders
ORDER BY customer_id, order_id;Для замовлення на 120.00 клієнта 101 частка дорівнює 60.00, тому що:
120 / (120 + 80) * 100 = 60Типове застосування такого підходу:
частка продажу товару в категорії;
частка витрат у бюджеті;
частка замовлення в обороті клієнта;
частка події в загальній кількості подій групи.
У запиті можна використовувати кілька функцій з однаковою або різною структурою вікна:
SELECT
order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total,
AVG(amount) OVER (
PARTITION BY customer_id
) AS customer_average,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS running_total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS order_number
FROM orders
ORDER BY customer_id, order_date, order_id;Тут:
customer_total — сума всіх замовлень клієнта;
customer_average — середня сума замовлення клієнта;
running_total — накопичувальна сума за датою;
order_number — порядковий номер замовлення клієнта.
Віконну функцію не можна безпосередньо використовувати в WHERE того самого рівня запиту:
-- Такий запит некоректний:
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS order_rank
FROM orders
WHERE order_rank = 1;Причина в тому, що WHERE обробляється до обчислення віконних функцій. Спочатку потрібно обчислити ранг у підзапиті або CTE, а потім відфільтрувати результат:
WITH ranked_orders AS (
SELECT
order_id,
customer_id,
order_date,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, order_id
) AS order_number
FROM orders
)
SELECT
order_id,
customer_id,
order_date,
amount
FROM ranked_orders
WHERE order_number = 1
ORDER BY customer_id;Цей запит повертає найдорожче замовлення кожного клієнта.
Якщо у вікні є ORDER BY, PostgreSQL використовує рамку рядків для обчислення. Для накопичувальних обчислень можна явно вказати, що рамка має починатися з першого рядка і закінчуватися поточним:
SELECT
order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY customer_id, order_date, order_id;Тут:
UNBOUNDED PRECEDING — перший рядок поточної партиції;
CURRENT ROW — поточний рядок;
ROWS BETWEEN ... AND ... — явне визначення діапазону рядків.
Для простих випадків запис без ROWS часто дає очікуваний результат, але явна рамка допомагає уникати неоднозначності, особливо коли значення в ORDER BY повторюються.
PARTITION BY зменшить кількість рядківPARTITION BY лише визначає групи для обчислення. Він не об’єднує рядки і не видаляє дублікати.
Якщо потрібен один рядок на групу, використовуйте GROUP BY.
GROUP BY замість віконної функціїЯкщо потрібно показати і деталі замовлення, і суму його клієнта, GROUP BY сам по собі не підходить. Використовуйте:
SUM(amount) OVER (PARTITION BY customer_id)PARTITION BY у вікні та PARTITION BY у таблиціУ виразі:
SUM(amount) OVER (PARTITION BY customer_id)PARTITION BY ділить рядки лише для поточного обчислення. Це не створює фізичних секцій таблиці та не змінює структуру даних.
ORDER BY для накопичувального обчисленняТакий вираз рахує загальну суму партиції, а не накопичувальну:
SUM(amount) OVER (PARTITION BY customer_id)Для накопичення потрібен порядок:
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
)Якщо сортування виконується лише за датою, кілька замовлень в один день можуть мати невизначений порядок. Додайте другий критерій, наприклад order_id.
WHEREПсевдонім на кшталт order_number стає доступним лише після обчислення виразу. Для фільтрації використовуйте підзапит або CTE.
Віконні функції виконують обчислення над групами рядків, не втрачаючи деталізації.
OVER визначає вікно, у межах якого виконується функція.
PARTITION BY ділить рядки на незалежні групи.
SUM(), AVG(), COUNT(), MIN() і MAX() можна використовувати як віконні функції.
ORDER BY у вікні визначає порядок обчислень і потрібен для накопичувальних значень.
ROW_NUMBER() і RANK() дають змогу нумерувати та ранжувати рядки всередині груп.
Щоб відфільтрувати результат віконної функції, спочатку обчисліть його в CTE або підзапиті.
GROUP BY зменшує кількість рядків, а PARTITION BY зберігає всі рядки результату.