Что такое обобщённое табличное выражение (CTE)?

Обобщённое табличное выражение (CTE, Common Table Expression) — это временный именованный результирующий набор, который определяется в рамках выполнения одного SQL-запроса с помощью предложения WITH. CTE существует только во время выполнения этого запроса и упрощает структурирование сложных запросов, делая их более читаемыми и поддерживаемыми.

Основные преимущества и случаи использования:

• Улучшение читаемости: Разбиение сложного запроса на логические блоки.
• Рекурсивные запросы: CTE может ссылаться на саму себя, что идеально для работы с иерархическими данными (деревьями, графами).
• Замена представления (VIEW): Когда нужно временное представление для одного сложного запроса.
• Многократное использование: CTE можно сослаться несколько раз в основном запросе, избегая дублирования подзапросов.

Пример 1: Простой CTE для структурирования запроса

WITH HighValueOrders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE total_amount > 1000
),
ActiveCustomers AS (
    SELECT customer_id, customer_name
    FROM customers
    WHERE status = 'active'
)
-- Основной запрос, использующий оба CTE
SELECT ac.customer_name, hvo.order_id, hvo.total_amount
FROM ActiveCustomers ac
JOIN HighValueOrders hvo ON ac.customer_id = hvo.customer_id
ORDER BY hvo.total_amount DESC;

Пример 2: Рекурсивный CTE для обхода иерархии (дерева сотрудников)

-- Таблица сотрудников с указанием менеджера
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- Рекурсивный CTE для получения всей цепочки подчинённых для менеджера с id = 1
WITH RECURSIVE SubordinateTree AS (
    -- Якорь рекурсии: начальная точка (менеджер)
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE id = 1

    UNION ALL

    -- Рекурсивный член: находим подчинённых предыдущего уровня
    SELECT e.id, e.name, e.manager_id, st.level + 1
    FROM employees e
    INNER JOIN SubordinateTree st ON e.manager_id = st.id
)
SELECT * FROM SubordinateTree ORDER BY level, id;

CTE выполняются до основного запроса и материализуются во временное пространство, что может влиять на производительность. Для очень больших или часто используемых наборов данных иногда предпочтительнее использовать временные таблицы.