Пошук уроків, статей та іншого контенту
Об’єднайте підзапити, CTE, віконні функції та операції над наборами для розв’язання практичних задач.
У складних аналітичних задачах одного SELECT часто недостатньо. Потрібно:
підготувати проміжні результати;
обчислити агрегати на кількох рівнях;
порівняти рядки між собою;
знайти найкращі або найгірші записи в кожній групі;
об’єднати результати кількох запитів;
відфільтрувати дані після застосування віконної функції.
Для цього PostgreSQL надає підзапити, CTE, віконні функції та операції над наборами.
У прикладах використаємо спрощену модель інтернет-магазину.
Нижче наведено повністю виконуваний SQL-скрипт. Він створює тимчасові таблиці, тому не змінює постійну структуру бази даних.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TEMP TABLE customers (
customer_id integer PRIMARY KEY,
full_name text NOT NULL,
city text NOT NULL,
registered_at date NOT NULL
);
CREATE TEMP TABLE products (
product_id integer PRIMARY KEY,
product_name text NOT NULL,
category text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price > 0)
);
CREATE TEMP TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(customer_id),
ordered_at date NOT NULL,
status text NOT NULL CHECK (status IN ('paid', 'cancelled'))
);
CREATE TEMP TABLE order_items (
order_id integer NOT NULL REFERENCES orders(order_id),
product_id integer NOT NULL REFERENCES products(product_id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10, 2) NOT NULL CHECK (unit_price > 0),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers (customer_id, full_name, city, registered_at)
VALUES
(1, 'Анна Коваль', 'Київ', '2024-01-10'),
(2, 'Богдан Мельник', 'Львів', '2024-02-15'),
(3, 'Вікторія Шевченко', 'Київ', '2024-03-20'),
(4, 'Ганна Бондар', 'Одеса', '2024-04-05'),
(5, 'Дмитро Ткаченко', 'Харків', '2024-05-18');
INSERT INTO products (product_id, product_name, category, price)
VALUES
(1, 'PostgreSQL Advanced', 'books', 950.00),
(2, 'SQL Patterns', 'books', 700.00),
(3, 'Механічна клавіатура', 'hardware', 3200.00),
(4, 'Монітор 27"', 'hardware', 8500.00),
(5, 'Курс з аналітики', 'courses', 5000.00),
(6, 'Курс з PostgreSQL', 'courses', 6200.00);
INSERT INTO orders (order_id, customer_id, ordered_at, status)
VALUES
(101, 1, '2024-06-01', 'paid'),
(102, 1, '2024-06-15', 'paid'),
(103, 2, '2024-06-03', 'paid'),
(104, 2, '2024-06-20', 'cancelled'),
(105, 3, '2024-06-05', 'paid'),
(106, 3, '2024-07-02', 'paid'),
(107, 4, '2024-07-10', 'paid'),
(108, 5, '2024-07-12', 'paid'),
(109, 5, '2024-07-20', 'paid');
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(101, 1, 1, 950.00),
(101, 3, 1, 3200.00),
(102, 5, 1, 5000.00),
(103, 2, 2, 700.00),
(104, 4, 1, 8500.00),
(105, 4, 1, 8500.00),
(106, 6, 1, 6200.00),
(107, 1, 1, 950.00),
(107, 2, 1, 700.00),
(108, 3, 1, 3200.00),
(108, 5, 1, 5000.00),
(109, 4, 1, 8500.00);Сума позиції замовлення обчислюється так:
quantity * unit_priceВажливо використовувати unit_price із замовлення, а не поточну ціну з таблиці products. Ціна товару могла змінитися після оформлення замовлення.
Підзапит — це запит, вкладений в інший запит. Він може повертати:
одне значення;
один стовпець;
кілька рядків;
тимчасову таблицю для зовнішнього запиту.
Знайдемо клієнтів, сума оплачених замовлень яких більша за середню суму замовлення.
Спочатку сформуємо суму кожного замовлення, а потім використаємо результат як підзапит:
SELECT
o.order_id,
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id, o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > (
SELECT AVG(order_total)
FROM (
SELECT
o2.order_id,
SUM(oi2.quantity * oi2.unit_price) AS order_total
FROM orders AS o2
JOIN order_items AS oi2 ON oi2.order_id = o2.order_id
WHERE o2.status = 'paid'
GROUP BY o2.order_id
) AS order_totals
)
ORDER BY order_total DESC;Внутрішній запит повертає середню суму оплаченого замовлення. Зовнішній запит залишає лише замовлення, які перевищують це значення.
EXISTSEXISTS перевіряє, чи повертає підзапит хоча б один рядок. Він зручний, коли потрібно перевірити наявність пов’язаного запису, але не потрібно отримувати його значення.
Знайдемо клієнтів, які купували товари з категорії courses:
SELECT
c.customer_id,
c.full_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
AND p.category = 'courses'
)
ORDER BY c.customer_id;Підзапит пов’язаний із зовнішнім запитом через умову:
o.customer_id = c.customer_idТакий підзапит називається корельованим: його умови використовують значення поточного рядка зовнішнього запиту.
NOT EXISTSЗнайдемо клієнтів, які ще не робили оплачених замовлень:
SELECT
c.customer_id,
c.full_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
);NOT EXISTS часто безпечніший за NOT IN, якщо підзапит може повертати NULL. Логіка NULL у SQL може призвести до неочікуваного результату для NOT IN.
CTE, або Common Table Expression, оголошується через WITH:
WITH назва_cte AS (
SELECT ...
)
SELECT ...
FROM назва_cte;CTE допомагає:
розділити великий запит на логічні етапи;
повторно використовувати проміжний результат;
зробити запит зрозумілішим;
поступово перевіряти кожен етап.
Порахуємо дохід за кожним оплаченим замовленням:
WITH order_totals AS (
SELECT
o.order_id,
o.customer_id,
o.ordered_at,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY
o.order_id,
o.customer_id,
o.ordered_at
)
SELECT
ot.order_id,
c.full_name,
ot.ordered_at,
ot.order_total
FROM order_totals AS ot
JOIN customers AS c ON c.customer_id = ot.customer_id
ORDER BY ot.order_total DESC;На відміну від повторення підзапиту, проміжний набір має зрозуміле ім’я — order_totals.
Тепер обчислимо:
суму кожного замовлення;
загальний дохід кожного клієнта;
кількість замовлень кожного клієнта;
середню суму замовлення клієнта.
WITH order_totals AS (
SELECT
o.order_id,
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id, o.customer_id
),
customer_stats AS (
SELECT
customer_id,
COUNT(*) AS orders_count,
SUM(order_total) AS revenue,
AVG(order_total) AS average_order_total
FROM order_totals
GROUP BY customer_id
)
SELECT
c.full_name,
cs.orders_count,
cs.revenue,
ROUND(cs.average_order_total, 2) AS average_order_total
FROM customer_stats AS cs
JOIN customers AS c ON c.customer_id = cs.customer_id
ORDER BY cs.revenue DESC;Другий CTE використовує результат першого. Це дозволяє не дублювати складну формулу обчислення суми замовлення.
Знайдемо клієнтів, чий дохід перевищує середній дохід серед усіх клієнтів із хоча б одним оплаченим замовленням:
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.customer_id
),
revenue_threshold AS (
SELECT AVG(revenue) AS average_revenue
FROM customer_revenue
)
SELECT
c.full_name,
cr.revenue,
ROUND(rt.average_revenue, 2) AS average_revenue
FROM customer_revenue AS cr
JOIN customers AS c ON c.customer_id = cr.customer_id
CROSS JOIN revenue_threshold AS rt
WHERE cr.revenue > rt.average_revenue
ORDER BY cr.revenue DESC;CROSS JOIN тут доречний, оскільки revenue_threshold повертає одне значення, яке потрібно додати до кожного рядка.
Віконна функція виконує обчислення для набору рядків, але не згортає їх в один рядок, як GROUP BY.
Загальний синтаксис:
функція(...) OVER (
PARTITION BY ...
ORDER BY ...
)PARTITION BY ділить рядки на незалежні групи;
ORDER BY визначає порядок рядків усередині групи;
віконна функція зберігає початкові рядки.
Порахуємо накопичувальний дохід за датами:
WITH daily_revenue AS (
SELECT
o.ordered_at,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.ordered_at
)
SELECT
ordered_at,
revenue,
SUM(revenue) OVER (
ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM daily_revenue
ORDER BY ordered_at;SUM(revenue) OVER (...) не об’єднує всі дати в один рядок. Для кожної дати він показує суму від першої дати до поточної.
Знайдемо найприбутковіші товари в кожній категорії:
WITH product_revenue AS (
SELECT
p.product_id,
p.product_name,
p.category,
COALESCE(
SUM(
CASE
WHEN o.status = 'paid'
THEN oi.quantity * oi.unit_price
ELSE 0
END
),
0
) AS revenue
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
LEFT JOIN orders AS o ON o.order_id = oi.order_id
GROUP BY
p.product_id,
p.product_name,
p.category
),
ranked_products AS (
SELECT
product_id,
product_name,
category,
revenue,
RANK() OVER (
PARTITION BY category
ORDER BY revenue DESC
) AS category_rank
FROM product_revenue
)
SELECT
product_name,
category,
revenue,
category_rank
FROM ranked_products
WHERE category_rank <= 2
ORDER BY category, category_rank, product_name;Фільтрація виконується у зовнішньому запиті, тому що віконна функція обчислюється після WHERE поточного рівня запиту.
Без CTE спроба написати таку умову була б некоректною:
-- Некоректний підхід:
-- WHERE RANK() OVER (...) <= 2Щоб відфільтрувати результат віконної функції, спочатку потрібно обчислити його в підзапиті або CTE.
ROW_NUMBER, RANK і DENSE_RANKЦі функції схожі, але поводяться по-різному, якщо кілька рядків мають однакове значення.
ROW_NUMBER() завжди призначає унікальний номер.
RANK() залишає пропуски після однакових позицій.
DENSE_RANK() не залишає пропусків.
Наприклад, для значень 100, 100, 80 результати будуть такими:
ROW_NUMBER: 1, 2, 3;
RANK: 1, 1, 3;
DENSE_RANK: 1, 1, 2.
Якщо потрібно повернути рівно одного переможця, використовуйте ROW_NUMBER() із додатковим критерієм сортування:
WITH product_revenue AS (
SELECT
p.product_id,
p.product_name,
p.category,
COALESCE(
SUM(
CASE
WHEN o.status = 'paid'
THEN oi.quantity * oi.unit_price
ELSE 0
END
),
0
) AS revenue
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
LEFT JOIN orders AS o ON o.order_id = oi.order_id
GROUP BY p.product_id, p.product_name, p.category
),
ranked_products AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY revenue DESC, product_id
) AS row_number_in_category
FROM product_revenue
)
SELECT
product_name,
category,
revenue
FROM ranked_products
WHERE row_number_in_category = 1
ORDER BY category;product_id у ORDER BY робить вибір детермінованим, якщо два товари мають однаковий дохід.
LAG повертає значення з попереднього рядка, а LEAD — із наступного.
Порівняємо дохід кожного клієнта з доходом попереднього клієнта в рейтингу:
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.customer_id
),
ranked_customers AS (
SELECT
customer_id,
revenue,
RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue
)
SELECT
c.full_name,
rc.revenue,
rc.revenue_rank,
LAG(rc.revenue) OVER (
ORDER BY rc.revenue_rank, c.customer_id
) AS previous_revenue,
rc.revenue
- COALESCE(
LAG(rc.revenue) OVER (
ORDER BY rc.revenue_rank, c.customer_id
),
0
) AS difference_from_previous
FROM ranked_customers AS rc
JOIN customers AS c ON c.customer_id = rc.customer_id
ORDER BY rc.revenue_rank, c.customer_id;У складних запитах повторення однакової віконної функції допустиме, але часто зручніше винести результат у ще один CTE.
Спочатку потрібно визначити рівень агрегації, а вже потім застосовувати віконну функцію.
Наприклад, для місячного звіту:
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', o.ordered_at)::date AS month_start,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY DATE_TRUNC('month', o.ordered_at)
)
SELECT
month_start,
revenue,
ROUND(
100 * revenue / NULLIF(SUM(revenue) OVER (), 0),
2
) AS percentage_of_total,
SUM(revenue) OVER (
ORDER BY month_start
) AS cumulative_revenue
FROM monthly_revenue
ORDER BY month_start;Тут відбуваються два різні етапи:
monthly_revenue згортає рядки замовлень до рівня місяця.
Віконні функції порівнюють уже готові місячні підсумки.
NULLIF(..., 0) захищає від ділення на нуль.
Операції над наборами поєднують результати кількох SELECT.
Основні операції:
UNION — об’єднання з видаленням дублікатів;
UNION ALL — об’єднання зі збереженням дублікатів;
INTERSECT — рядки, спільні для обох результатів;
EXCEPT — рядки з першого результату, яких немає в другому.
Запити по обидва боки операції повинні мати однакову кількість стовпців із сумісними типами.
UNION ALLЗнайдемо клієнтів, які купували книги або курси, і додамо до результату тип категорії:
SELECT
o.customer_id,
'books' AS interest
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'books'
UNION ALL
SELECT
o.customer_id,
'courses' AS interest
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'courses'
ORDER BY customer_id, interest;Якщо клієнт купував і книги, і курси, він з’явиться двічі — по одному разу для кожного інтересу.
UNIONЯкщо потрібен лише список унікальних клієнтів, використовуйте UNION:
SELECT o.customer_id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'books'
UNION
SELECT o.customer_id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'courses'
ORDER BY customer_id;UNION видаляє дублікати після об’єднання результатів.
INTERSECTЗнайдемо клієнтів, які купували і книги, і курси:
SELECT o.customer_id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'books'
INTERSECT
SELECT o.customer_id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'courses'
ORDER BY customer_id;EXCEPTЗнайдемо клієнтів, які купували книги, але не купували курси:
SELECT o.customer_id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'books'
EXCEPT
SELECT o.customer_id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
AND p.category = 'courses'
ORDER BY customer_id;У практичних задачах INTERSECT і EXCEPT часто роблять запит коротшим, ніж кілька рівнів EXISTS або NOT EXISTS.
Сформуємо рейтинг клієнтів за такими правилами:
врахувати лише оплачені замовлення;
порахувати загальний дохід клієнта;
порахувати кількість замовлень;
визначити середню суму замовлення;
знайти найприбутковішу категорію кожного клієнта;
визначити місце клієнта в загальному рейтингу;
показати лише клієнтів із рейтингом не нижче третього місця.
WITH order_totals AS (
SELECT
o.order_id,
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id, o.customer_id
),
customer_revenue AS (
SELECT
customer_id,
COUNT(*) AS orders_count,
SUM(order_total) AS revenue,
AVG(order_total) AS average_order_total
FROM order_totals
GROUP BY customer_id
),
customer_category_revenue AS (
SELECT
o.customer_id,
p.category,
SUM(oi.quantity * oi.unit_price) AS category_revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status = 'paid'
GROUP BY o.customer_id, p.category
),
customer_top_category AS (
SELECT
customer_id,
category,
category_revenue,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY category_revenue DESC, category
) AS category_number
FROM customer_category_revenue
),
ranked_customers AS (
SELECT
cr.customer_id,
cr.orders_count,
cr.revenue,
cr.average_order_total,
RANK() OVER (
ORDER BY cr.revenue DESC
) AS revenue_rank
FROM customer_revenue AS cr
)
SELECT
c.full_name,
rc.orders_count,
rc.revenue,
ROUND(rc.average_order_total, 2) AS average_order_total,
rct.category AS top_category,
rct.category_revenue AS top_category_revenue,
rc.revenue_rank
FROM ranked_customers AS rc
JOIN customers AS c
ON c.customer_id = rc.customer_id
JOIN customer_top_category AS rct
ON rct.customer_id = rc.customer_id
AND rct.category_number = 1
WHERE rc.revenue_rank <= 3
ORDER BY rc.revenue_rank, c.full_name;Цей запит поділено на незалежні етапи. Кожен CTE має одну відповідальність, тому запит легше тестувати й змінювати.
Перед написанням SQL сформулюйте рівень кожного проміжного результату:
order_totals — один рядок на замовлення.
customer_revenue — один рядок на клієнта.
customer_category_revenue — один рядок на пару «клієнт — категорія».
customer_top_category — ті самі пари, але з рейтингом усередині клієнта.
ranked_customers — один рядок на клієнта з глобальним рейтингом.
фінальний SELECT — поєднання готових результатів.
Це допомагає уникати помилки, коли в одному запиті одночасно змішуються:
окремі товари;
позиції замовлення;
цілі замовлення;
клієнти;
агрегати різного рівня.
WHEREНекоректно:
SELECT
product_id,
RANK() OVER (ORDER BY revenue DESC) AS product_rank
FROM product_revenue
WHERE product_rank <= 3;Аліас product_rank ще недоступний у WHERE, а віконні функції обчислюються пізніше.
Правильно:
SELECT *
FROM (
SELECT
product_id,
revenue,
RANK() OVER (ORDER BY revenue DESC) AS product_rank
FROM product_revenue
) AS ranked
WHERE product_rank <= 3;INNER JOINЯкщо потрібно показати також товари або клієнтів без продажів, використовуйте LEFT JOIN.
INNER JOIN залишить лише записи, для яких існує відповідний рядок у пов’язаній таблиці.
LEFT JOINУмова в WHERE може перетворити LEFT JOIN на фактичний INNER JOIN.
Наприклад:
-- Товари без оплачених замовлень будуть виключені
FROM products AS p
LEFT JOIN orders AS o ON ...
WHERE o.status = 'paid'Якщо потрібно зберегти товари без замовлень, умову краще розмістити в ON або використати умовну агрегацію.
Якщо приєднати таблиці з різною кількістю рядків і агрегувати після цього, сума може бути завищена. Спочатку визначте, що саме має представляти один рядок проміжного результату.
Часто безпечніше спочатку створити CTE із сумою замовлення, а вже потім агрегувати його до рівня клієнта.
Для історичних розрахунків потрібно використовувати:
oi.quantity * oi.unit_priceа не:
oi.quantity * p.pricep.price — поточна ціна товару, яка може не відповідати ціні під час оформлення замовлення.
Якщо в ROW_NUMBER() сортувати лише за доходом, два товари з однаковим доходом можуть отримати номери в непередбачуваному порядку.
Додавайте стабільний додатковий критерій:
ORDER BY revenue DESC, product_idUNIONНекоректно поєднувати результати з різною кількістю стовпців:
SELECT customer_id
UNION
SELECT customer_id, full_name;Усі частини операції над наборами повинні повертати сумісну структуру.
UNION замість UNION ALLUNION видаляє дублікати, що потребує додаткової обробки. Якщо дублікати мають зберігатися або результати гарантовано не перетинаються, використовуйте UNION ALL.
Підзапити дають змогу використовувати результат одного запиту в іншому.
EXISTS і NOT EXISTS зручні для перевірки наявності пов’язаних рядків.
CTE через WITH розбивають складну задачу на зрозумілі етапи.
Віконні функції виконують розрахунки між рядками, не згортаючи їх у групи.
ROW_NUMBER, RANK і DENSE_RANK використовуються для побудови рейтингів.
Після віконної функції фільтрацію потрібно виконувати в зовнішньому запиті.
UNION, UNION ALL, INTERSECT та EXCEPT працюють із наборами рядків.
Перед написанням складного запиту визначайте рівень агрегації кожного проміжного результату.
Комбінація CTE, агрегатів, віконних функцій і операцій над наборами дає змогу будувати складні аналітичні звіти без дублювання логіки.