Пошук уроків, статей та іншого контенту
Створюйте іменовані тимчасові результати через WITH, щоб структурувати та повторно використовувати частини запиту.
CTE (Common Table Expression) — це іменований результат запиту, оголошений у блоці WITH. Він існує лише під час виконання одного SQL-запиту.
Базовий синтаксис:
WITH назва_cte AS (
SELECT ...
)
SELECT ...
FROM назва_cte;CTE допомагає:
розділити складний запит на логічні частини;
дати проміжному результату зрозуміле ім’я;
повторно використати один результат у головному запиті;
послідовно обробити дані в кілька етапів.
CTE не створює постійну таблицю і не зберігається після завершення запиту.
Припустімо, потрібно знайти активних клієнтів, сума оплачених замовлень яких перевищує 1000.
Без CTE такий запит може містити вкладений підзапит:
SELECT
c.name,
totals.total_amount
FROM customers AS c
JOIN (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS totals ON totals.customer_id = c.id
WHERE c.active = true
AND totals.total_amount > 1000;За допомогою CTE проміжний результат отримує власне ім’я:
WITH paid_order_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
)
SELECT
c.name,
p.total_amount
FROM customers AS c
JOIN paid_order_totals AS p ON p.customer_id = c.id
WHERE c.active = true
AND p.total_amount > 1000;paid_order_totals — це не таблиця в базі даних, а тимчасово доступний результат першого SELECT.
CTE розділяються комами. Наступний CTE може використовувати попередні:
WITH paid_order_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
),
large_customers AS (
SELECT
customer_id,
total_amount
FROM paid_order_totals
WHERE total_amount >= 1000
)
SELECT
c.id,
c.name,
lc.total_amount
FROM customers AS c
JOIN large_customers AS lc ON lc.customer_id = c.id
WHERE c.active = true;Порядок важливий:
paid_order_totals обчислює суму оплачених замовлень;
large_customers використовує цей результат;
головний SELECT об’єднує його з клієнтами.
CTE не може посилатися на CTE, який оголошений нижче.
Один CTE можна використати кілька разів у головному запиті або в інших CTE. Наприклад, можна порівняти кожного клієнта із середньою сумою замовлень:
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
),
average_total AS (
SELECT AVG(total_amount) AS value
FROM customer_totals
)
SELECT
c.name,
ct.total_amount,
ROUND(at.value, 2) AS average_amount,
ct.total_amount > at.value AS above_average
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
CROSS JOIN average_total AS at
ORDER BY ct.total_amount DESC;У цьому прикладі:
customer_totals використовується в average_total;
customer_totals також використовується в головному запиті;
average_total додає одне значення до кожного рядка через CROSS JOIN.
Нижче наведено самодостатній запит PostgreSQL. Дані створюються через VALUES, тому для його виконання не потрібні додаткові таблиці:
WITH customers(id, name, active) AS (
VALUES
(1, 'Анна', true),
(2, 'Богдан', true),
(3, 'Олена', false),
(4, 'Дмитро', true)
),
orders(id, customer_id, amount, status) AS (
VALUES
(101, 1, 700.00, 'paid'),
(102, 1, 500.00, 'paid'),
(103, 1, 100.00, 'cancelled'),
(104, 2, 300.00, 'paid'),
(105, 2, 250.00, 'paid'),
(106, 3, 2000.00, 'paid'),
(107, 4, 1500.00, 'paid')
),
paid_order_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
),
active_large_customers AS (
SELECT
c.id,
c.name,
p.total_amount
FROM customers AS c
JOIN paid_order_totals AS p ON p.customer_id = c.id
WHERE c.active = true
AND p.total_amount >= 1000
)
SELECT
id,
name,
total_amount
FROM active_large_customers
ORDER BY total_amount DESC;Результат:
id | name | total_amount
----+--------+--------------
1 | Анна | 1200.00
4 | Дмитро | 1500.00Олена не потрапляє до результату, тому що вона неактивна. Богдан не потрапляє до результату, тому що сума його оплачених замовлень менша за 1000.
Якщо стовпці CTE отримують зрозумілі імена з SELECT, додатково їх оголошувати не потрібно:
WITH totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT *
FROM totals;Імена стовпців можна також вказати після назви CTE:
WITH totals(customer_id, total_amount) AS (
SELECT
customer_id,
SUM(amount)
FROM orders
GROUP BY customer_id
)
SELECT *
FROM totals;Кількість імен повинна відповідати кількості стовпців, які повертає запит CTE.
ORDER BYORDER BY усередині CTE зазвичай не визначає порядок рядків у фінальному результаті. Порядок потрібно вказувати в головному запиті:
WITH totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT *
FROM totals
ORDER BY total_amount DESC;Сортування має значення лише там, де воно впливає на операцію, наприклад разом із LIMIT:
WITH top_orders AS (
SELECT
id,
customer_id,
amount
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC
LIMIT 10
)
SELECT *
FROM top_orders
ORDER BY amount DESC;Тут ORDER BY разом із LIMIT визначає, які саме 10 замовлень буде вибрано.
CTE може містити не лише SELECT, а й INSERT, UPDATE або DELETE. Щоб передати результат такої операції далі, використовується RETURNING.
Наприклад, спочатку створимо записи, а потім одразу отримаємо їх:
WITH created_orders AS (
INSERT INTO orders(customer_id, amount, status)
VALUES
(1, 250.00, 'pending'),
(2, 400.00, 'pending')
RETURNING id, customer_id, amount, status
)
SELECT *
FROM created_orders
ORDER BY id;RETURNING повертає рядки, змінені операцією. Уся конструкція залишається одним SQL-запитом.
CTE не слід автоматично сприймати як фізичну тимчасову таблицю.
У сучасних версіях PostgreSQL оптимізатор може:
вбудувати простий CTE в основний запит;
обчислити CTE окремо та зберігати його результат для повторного використання.
Для явного керування цим можна використовувати:
WITH totals AS MATERIALIZED (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT *
FROM totals
WHERE total_amount > 1000;MATERIALIZED вимагає обчислити CTE окремо.
WITH totals AS NOT MATERIALIZED (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT *
FROM totals
WHERE total_amount > 1000;NOT MATERIALIZED дозволяє оптимізатору вбудувати CTE в запит, якщо це вигідно.
У більшості запитів достатньо звичайного WITH. Явне використання MATERIALIZED або NOT MATERIALIZED варто додавати лише після перевірки плану виконання та вимірювання продуктивності.
Для обробки ієрархій PostgreSQL підтримує WITH RECURSIVE. Рекурсивний CTE складається з:
початкового запиту;
UNION ALL;
рекурсивного запиту, який посилається на сам CTE.
Наприклад, цей запит будує числа від 1 до 5:
WITH RECURSIVE numbers(value) AS (
SELECT 1
UNION ALL
SELECT value + 1
FROM numbers
WHERE value < 5
)
SELECT value
FROM numbers
ORDER BY value;Результат:
value
-------
1
2
3
4
5Рекурсивні CTE часто використовують для дерева категорій, структури підрозділів або залежностей. Важливо, щоб рекурсія мала умову завершення, інакше запит може виконуватися нескінченно.
WITH report AS (
SELECT ...
)
SELECT * FROM report;
SELECT * FROM report;Другий запит не спрацює, тому що report існує лише в межах першого SQL-запиту.
WITH second_step AS (
SELECT *
FROM first_step
),
first_step AS (
SELECT 1 AS value
)
SELECT *
FROM second_step;Такий порядок неправильний. Спочатку потрібно оголосити first_step:
WITH first_step AS (
SELECT 1 AS value
),
second_step AS (
SELECT *
FROM first_step
)
SELECT *
FROM second_step;Якщо CTE агрегує всі замовлення, а статус фільтрується лише зовні, він може обробити більше рядків, ніж потрібно:
WITH totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT *
FROM totals
WHERE total_amount > 1000;Цей запит коректний, але якщо потрібні лише оплачені замовлення, умову краще додати до CTE:
WITH paid_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
)
SELECT *
FROM paid_totals
WHERE total_amount > 1000;Так логіка стає зрозумілішою, а обсяг оброблюваних даних може зменшитися.
CTE не гарантує порядок результатів. Для фінального порядку завжди використовуйте ORDER BY у зовнішньому запиті.
CTE мають робити запит зрозумілішим. Якщо кожен простий вираз винесено в окремий CTE, запит може стати довшим і складнішим для читання. Виносьте в CTE логічно завершені етапи обробки.
CTE оголошується через WITH і має власне ім’я.
CTE існує лише протягом одного SQL-запиту.
Кілька CTE розділяються комами.
Наступний CTE може використовувати попередній.
Один CTE можна використовувати повторно.
CTE допомагає розділити складний запит на послідовні логічні етапи.
CTE може виконувати SELECT, INSERT, UPDATE або DELETE разом із RETURNING.
Для ієрархічних даних використовується WITH RECURSIVE.
ORDER BY у фінальному запиті потрібен для гарантованого порядку результатів.
CTE не є постійною таблицею і не повинна автоматично сприйматися як фізично матеріалізований результат.