CTE (Common Table Expressions): WITH-запросы и рекурсивные CTE

Введение

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

Концепция

Обычный (не рекурсивный) CTE — именованный, временный результат SELECT-запроса, доступный для использования в основном запросе (или других CTE того же WITH-блока) по имени, аналогично временной, «одноразовой» представлению (статья 406), существующей только в рамках одного запроса. Рекурсивный CTE определяется через объединение (UNION/UNION ALL) «базового» случая (начальной точки рекурсии) и «рекурсивного» случая, ссылающегося на сам CTE — это позволяет естественно выражать запросы по иерархическим данным (организационная структура с подчинёнными, дерево категорий товаров) без заранее известной глубины вложенности.

Пример кода: CTE (Common Table Expressions)

-- Обычный CTE — улучшение читаемости многошагового вычисления
WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT customer_id, total_spent
    FROM customer_totals
    WHERE total_spent > 10000
)
SELECT c.name, hvc.total_spent
FROM high_value_customers hvc
JOIN customers c ON c.id = hvc.customer_id
ORDER BY hvc.total_spent DESC;

-- Рекурсивный CTE — обход иерархии подчинённых сотрудников
WITH RECURSIVE subordinates AS (
    -- Базовый случай: начинаем с конкретного руководителя
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 5 -- id интересующего нас "корневого" руководителя

    UNION ALL

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

-- Рекурсивный CTE с защитой от бесконечной рекурсии (для данных с потенциальными циклами)
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, ARRAY[id] AS path, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.path || c.id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE NOT c.id = ANY(ct.path) -- защита от цикла: не повторять уже посещённый узел
      AND ct.depth < 100           -- дополнительная защита по максимальной глубине
)
SELECT * FROM category_tree ORDER BY path;

Пояснения к коду

Два последовательных CTE (customer_totals, затем high_value_customers, ссылающийся на первый) показывают, как сложное, многошаговое вычисление разбивается на отдельные, поимённые, легко читаемые этапы — каждый CTE решает одну логическую подзадачу, и итоговый запрос читается как естественная последовательность шагов, а не как один большой, плотно вложенный запрос. Рекурсивный CTE для subordinates явно демонстрирует структуру «базовый случай UNION ALL рекурсивный случай» — базовый случай находит начальную точку (конкретного руководителя), а рекурсивный случай join’ится сам с собой (subordinates s), находя сотрудников, подчинённых уже найденным на предыдущей итерации, и это построение продолжается, пока рекурсивный случай продолжает находить новые строки. Защита от цикла через ARRAY[id]/path || c.id/NOT c.id = ANY(ct.path) — критично важная мера для данных, структура которых теоретически (например, из-за ошибки данных) может содержать цикл (A подчинён B, B подчинён A) — без этой защиты рекурсия в таком случае продолжалась бы бесконечно (или до достижения внутреннего лимита СУБД на глубину рекурсии).

Подводные камни

• Отсутствие защиты от бесконечной рекурсии для иерархических данных, теоретически способных содержать циклы (из-за ошибки данных, не предотвращённой ограничениями схемы) — без явной защиты (как path-массив в примере, или явное ограничение максимальной глубины) рекурсивный CTE над данными с реальным циклом приведёт либо к исчерпанию ресурсов СУБД, либо к достижению встроенного ограничения на глубину рекурсии (если оно настроено), что в обоих случаях проявляется как явная ошибка выполнения запроса, а не просто медленный результат.
• Использование CTE исключительно ради улучшения читаемости без понимания, что в некоторых старых версиях некоторых СУБД CTE могли оптимизироваться менее эффективно, чем эквивалентный подзапрос (CTE «материализовался» как полноценный временный результат, тогда как подзапрос мог быть «встроен» оптимизатором в общий план более эффективно) — это историческая особенность некоторых конкретных версий конкретных СУБД (например, PostgreSQL до версии 12 по умолчанию материализовал CTE), и для критичных по производительности запросов стоит проверить актуальное поведение конкретной используемой версии СУБД через EXPLAIN (статья 416), а не предполагать одинаковую производительность CTE и эквивалентного подзапроса без проверки.
• Чрезмерное усложнение единственного запроса через большое количество последовательных CTE, каждый из которых выполняет относительно простой шаг, вместо разделения логики на уровне приложения (несколько отдельных, более простых запросов) там, где это было бы более уместно архитектурно — хотя CTE значительно улучшают читаемость сложных, многошаговых запросов по сравнению с глубоко вложенными подзапросами, не каждая сложная задача обработки данных обязательно должна решаться единственным, монолитным SQL-запросом, и иногда разделение на несколько более простых запросов с промежуточной обработкой на уровне приложения архитектурно понятнее.
• Игнорирование различий синтаксиса рекурсивных CTE между разными СУБД — хотя базовый принцип (WITH RECURSIVE, базовый случай UNION ALL рекурсивный случай) широко стандартизирован, конкретные детали (поддержка операций с массивами для защиты от циклов, как ARRAY[]/ANY() в примере, специфичных для PostgreSQL) могут отличаться между СУБД, и код, разработанный для одной конкретной СУБД, может требовать адаптации при переносе на другую — эта тема подробнее разбирается в статье 427.