Ответ
CTE (Common Table Expressions) — это именованный временный набор результатов, который существует только в рамках выполнения одного SQL-запроса (SELECT, INSERT, UPDATE или DELETE). CTE определяется с помощью ключевого слова WITH.
Основная цель CTE — улучшить читаемость и структуру сложных запросов, разбивая их на логические блоки.
Ключевые преимущества:
- Читаемость: Длинные и сложные подзапросы можно вынести в отдельные именованные блоки.
- Многократное использование: На один и тот же CTE можно ссылаться несколько раз в основном запросе.
- Рекурсия: CTE позволяют выполнять рекурсивные запросы, что идеально подходит для работы с иерархическими данными (например, деревья категорий, организационные структуры).
Пример простого (нерекурсивного) CTE:
Предположим, нам нужно найти заказы пользователей, которые зарегистрировались в 2023 году.
WITH active_users AS (
-- Шаг 1: Выбираем ID пользователей, зарегистрированных в 2023
SELECT id
FROM users
WHERE EXTRACT(YEAR FROM registration_date) = 2023
)
-- Шаг 2: Используем результат для фильтрации заказов
SELECT *
FROM orders
WHERE user_id IN (SELECT id FROM active_users);Пример рекурсивного CTE:
Рекурсивные CTE используются для обхода древовидных или графовых структур. Например, для получения всех дочерних узлов для заданного родителя.
-- Найти всех сотрудников в подчинении у менеджера с id = 1
WITH RECURSIVE subordinates AS (
-- Начальная часть (anchor member): сам менеджер
SELECT id, name, manager_id
FROM employees
WHERE id = 1
UNION ALL
-- Рекурсивная часть (recursive member): присоединяем прямых подчиненных
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;