Использование подзапросов, CTE и lateral JOIN для сложных сценариев
Подзапросы и CTE (Common Table Expressions) внутри JOIN позволяют создавать сложные запросы с предварительной обработкой данных, фильтрацией и агрегацией.
Подзапрос в FROM действует как виртуальная таблица.
SELECT <колонки>
FROM (
SELECT ...
) AS подзапрос
JOIN другая_таблица ON условие;Важно: Обязательно указывайте псевдоним (
AS подзапрос) для подзапроса вFROM.
-- Общая сумма заказов по каждому пользователю
SELECT
u.name,
user_orders.total_amount,
user_orders.order_count
FROM users u
JOIN (
SELECT
user_id,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) AS user_orders ON u.id = user_orders.user_id;Преимущества:
-- Только последние заказы каждого пользователя
SELECT
u.name,
latest_orders.order_id,
latest_orders.amount,
latest_orders.order_date
FROM users u
JOIN (
SELECT DISTINCT ON (user_id)
id AS order_id,
user_id,
amount,
created_at AS order_date
FROM orders
ORDER BY user_id, created_at DESC
) AS latest_orders ON u.id = latest_orders.user_id;CTE определяют именованные временные результаты, которые можно использовать в основном запросе.
WITH cte_name AS (
SELECT ...
)
SELECT ...
FROM cte_name
JOIN ...;WITH user_order_stats AS (
SELECT
user_id,
SUM(amount) AS total_amount,
COUNT(*) AS order_count,
AVG(amount) AS avg_amount
FROM orders
GROUP BY user_id
)
SELECT
u.name,
uos.total_amount,
uos.order_count,
uos.avg_amount
FROM users u
JOIN user_order_stats uos ON u.id = uos.user_id
WHERE uos.order_count > 1;WITH
-- Активные пользователи
active_users AS (
SELECT id, name
FROM users
WHERE last_login_at > NOW() - INTERVAL '30 days'
),
-- Их заказы за последний месяц
recent_orders AS (
SELECT
user_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
),
-- Топ категории
top_categories AS (
SELECT
o.user_id,
c.name AS category_name,
COUNT(*) AS category_orders
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY o.user_id, c.name
)
SELECT
au.name,
ro.order_count,
ro.total_amount,
tc.category_name AS favorite_category
FROM active_users au
JOIN recent_orders ro ON au.id = ro.user_id
JOIN top_categories tc ON au.id = tc.user_id
WHERE ro.order_count = (
SELECT MAX(category_orders)
FROM top_categories tc2
WHERE tc2.user_id = au.id
);Рекурсивные CTE полезны для иерархических данных.
WITH RECURSIVE cte_name AS (
-- Базовый случай (якорь)
SELECT ...
UNION ALL
-- Рекурсивный случай
SELECT ...
FROM cte_name
JOIN ... ON ...
)
SELECT * FROM cte_name;WITH RECURSIVE employee_hierarchy AS (
-- Базовый случай: CEO
SELECT
id,
name,
manager_id,
1 AS level,
name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Рекурсивный случай: подчинённые
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1,
eh.path || ' -> ' || e.name
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
WHERE eh.level < 10 -- Ограничение глубины
)
SELECT * FROM employee_hierarchy
ORDER BY level, name;WITH RECURSIVE category_tree AS (
-- Корневые категории
SELECT
id,
name,
parent_id,
name AS full_path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Дочерние категории
SELECT
c.id,
c.name,
c.parent_id,
ct.full_path || ' > ' || c.name
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY full_path;Коррелированные подзапросы ссылаются на колонки внешней таблицы.
SELECT
u.name,
(
SELECT o.amount
FROM orders o
WHERE o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 1
) AS last_order_amount
FROM users u;SELECT
u.name,
o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount = (
SELECT MAX(o2.amount)
FROM orders o2
WHERE o2.user_id = u.id
);Производительность: Коррелированные подзапросы могут выполняться для каждой строки внешней таблицы. Часто эффективнее использовать
LATERAL JOINили оконные функции.
LATERAL позволяет подзапросу в FROM ссылаться на колонки предыдущих таблиц.
SELECT ...
FROM таблица1
JOIN LATERAL (
SELECT ... WHERE ... = таблица1.колонка
) AS подзапрос ON true;SELECT
u.name,
lo.order_id,
lo.amount,
lo.created_at
FROM users u
JOIN LATERAL (
SELECT
id AS order_id,
amount,
created_at
FROM orders
WHERE user_id = u.id -- Ссылка на u из внешней таблицы!
ORDER BY created_at DESC
LIMIT 3
) AS lo ON true;Без LATERAL подзапрос не мог бы ссылаться на u.id.
SELECT
u.name,
lo.total_amount,
lo.order_count
FROM users u
JOIN LATERAL (
SELECT
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM orders
WHERE user_id = u.id
AND created_at > NOW() - INTERVAL '30 days'
) AS lo ON true
WHERE lo.order_count > 0;SELECT
u.name,
lo.last_order,
lp.last_payment
FROM users u
JOIN LATERAL (
SELECT amount AS last_order
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 1
) AS lo ON true
JOIN LATERAL (
SELECT amount AS last_payment
FROM payments
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 1
) AS lp ON true;Генерация строк на основе данных из другой таблицы.
SELECT
o.id AS order_id,
o.created_at,
d.date AS followup_date
FROM orders o
CROSS JOIN LATERAL (
SELECT generate_series(
o.created_at::date,
o.created_at::date + 7,
'1 day'::interval
)::date AS date
) AS d;CREATE TABLE orders (
id INTEGER,
product_ids INTEGER[] -- Массив ID товаров
);
SELECT
o.id AS order_id,
pid AS product_id
FROM orders o
CROSS JOIN LATERAL unnest(o.product_ids) AS pid;Комбинирование результатов нескольких запросов в подзапросе.
SELECT
u.name,
all_transactions.amount
FROM users u
JOIN (
SELECT user_id, amount, created_at FROM orders
UNION ALL
SELECT user_id, -amount AS refund, created_at FROM refunds
) AS all_transactions ON u.id = all_transactions.user_id;SELECT u.name
FROM users u
JOIN (
SELECT id FROM users WHERE active = true
EXCEPT
SELECT user_id FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
) AS inactive_active ON u.id = inactive_active.id;В PostgreSQL 12+ CTE могут быть "inlined" (встроены) как подзапросы:
-- CTE может быть встроен
WITH stats AS (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
)
SELECT * FROM stats;
-- Эквивалентный подзапрос
SELECT * FROM (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
) AS stats;Для принудительной материализации CTE используйте MATERIALIZED:
WITH stats AS MATERIALIZED (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
)
SELECT * FROM stats;-- Медленно: коррелированный подзапрос для каждой строки
SELECT
u.name,
(SELECT SUM(amount) FROM orders WHERE user_id = u.id) AS total
FROM users u;
-- Быстрее: LATERAL JOIN
SELECT
u.name,
lo.total
FROM users u
JOIN LATERAL (
SELECT SUM(amount) AS total
FROM orders
WHERE user_id = u.id
) AS lo ON true;-- Медленно: подзапрос для последнего заказа
SELECT
u.name,
(SELECT o.amount FROM orders o
WHERE o.user_id = u.id
ORDER BY o.created_at DESC LIMIT 1) AS last_amount
FROM users u;
-- Быстрее: оконная функция
SELECT DISTINCT ON (u.id)
u.name,
o.amount AS last_amount
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;-- ОШИБКА: нет псевдонима
SELECT * FROM (SELECT id, name FROM users) JOIN orders ON id = user_id;
-- Правильно:
SELECT * FROM (SELECT id, name FROM users) AS u JOIN orders ON u.id = user_id;-- ОШИБКА: CTE должен быть определён перед использованием
SELECT * FROM order_stats
JOIN users u ON order_stats.user_id = u.id
WITH order_stats AS (
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
);
-- Правильно:
WITH order_stats AS (
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
)
SELECT * FROM order_stats
JOIN users u ON order_stats.user_id = u.id;-- Опасно: нет ограничения глубины
WITH RECURSIVE hierarchy AS (
SELECT id, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
)
-- Безопасно: ограничение глубины
WITH RECURSIVE hierarchy AS (
SELECT id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, h.level + 1 FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
WHERE h.level < 10
)FROM действуют как виртуальные таблицы; требуют псевдонимWITH) улучшают читаемость и позволяют переиспользовать результатыWITH RECURSIVE) обрабатывают иерархииLATERAL JOIN позволяет подзапросу ссылаться на предыдущие таблицы в FROMLATERAL или оконные функцииCROSS JOIN LATERAL полезен для генерации строк и разворачивания массивовВопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.