Что такое рекурсивные CTE (Common Table Expressions) и для чего они используются?

Question

Что такое рекурсивные CTE (Common Table Expressions) и для чего они используются?

Тип вопроса: Базы данных Вероятность: 10% Категория: Golang Разработчик

Ответ

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

По сути, это CTE, которое ссылается само на себя. Оно состоит из двух обязательных частей:

Базовая (якорная) часть (Anchor Member): Начальный запрос, который выполняется один раз и формирует стартовый набор данных. Это условие выхода из рекурсии.
Рекурсивная часть (Recursive Member): Запрос, который ссылается на CTE и выполняется многократно, на каждой итерации добавляя новые строки к результату предыдущей, пока не перестанет возвращать новые данные.

Эти две части объединяются с помощью UNION ALL.

Основное применение — работа с иерархиями: организационные структуры, деревья категорий, связи в социальных сетях, маршруты на карте.

Пример: Получение дерева категорий

WITH RECURSIVE CategoryTree AS (
    -- 1. Базовая часть: находим корневые категории (у которых нет родителя)
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 2. Рекурсивная часть: присоединяем дочерние категории к уже найденным
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT * FROM CategoryTree;

Ключевые моменты:

В PostgreSQL и некоторых других СУБД обязательно ключевое слово RECURSIVE.
Использование UNION ALL предпочтительнее UNION, так как UNION удаляет дубликаты на каждом шаге, что сильно снижает производительность.
Важно спроектировать запрос так, чтобы рекурсия гарантированно завершилась, иначе можно получить бесконечный цикл.
СУБД имеют встроенную защиту от бесконечной рекурсии (например, max_recursion_depth в PostgreSQL).

Ответ 18+ 🔞

Давай разберем эту вашу рекурсию в SQL, а то звучит как какая-то магия, а на деле — проще пареной репы, если, конечно, не тупить.

Представь, что тебе нужно обойти всю иерархию, например, начальников и подчинённых. В лоб это нихуя не сделать обычным SELECT'ом. Вот тут-то наш усатый друг, рекурсивный CTE, и вылезает из-под пола.

Работает он по принципу ёбаного матрёшки:

Якорь (Anchor Member). Это как первый, самый большой матрёшку достал. Берёшь корневые элементы — те, у кого нет родителя. Это старт.
Рекурсия (Recursive Member). А потом начинаешь эту матрёшку открывать. Берёшь результат предыдущего шага (уже найденных начальников) и ищешь всех, кто на них завязан (их подчинённых). И так по кругу, пока внутри матрёшки не окажется пусто — новых записей не найдётся.

Склеивается это всё через UNION ALL. UNION не юзай — он будет на каждом шагу дубликаты вычищать, а это, блядь, просто ад для производительности.

Вот живой пример, чтобы въехать. Допустим, есть таблица categories с древом категорий:

WITH RECURSIVE CategoryTree AS (
    -- 1. Якорь: хватаем корни, у которых parent_id — NULL
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 2. Сама рекурсия: цепляем детей к найденным родителям
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN CategoryTree ct ON c.parent_id = ct.id -- Вот тут магия! CTE ссылается само на себя.
)
SELECT * FROM CategoryTree ORDER BY level, id;

Смотри, что происходит: запрос сначала берёт все корни (уровень 0). Потом ищет всех, чей parent_id совпадает с id из CategoryTree (т.е. детей этих корней) — это уровень 1. Потом ищет детей для уровня 1, получая уровень 2. И так далее, пока не упрётся в листья дерева, у которых своих детей уже нет.

Важные нюансы, чтоб не обосраться:

В PostgreSQL слово RECURSIVE обязательно. В SQL Server или Oracle можно просто WITH CategoryTree AS (...), они и так поймут.
Следи за завершением! Если в данных есть циклическая связь (например, категория ссылается сама на себя или на своего ребёнка), запрос уйдёт в бесконечный цикл. Хорошие СУБД имеют защиту от долбоёбов — лимит на глубину рекурсии (типа MAXRECURSION). Превысил — получил ошибку вместо вечного запроса.
Производительность. Для больших деревьев это может быть тяжело. Индексы на parent_id — твои лучшие друзья.

Короче, инструмент — охуенный. Для деревьев, графов, всяких путей — то, что доктор прописал. Главное — применять с мозгом, а не тыкать его куда попало.