Как написать рекурсивный CTE

Зачем это знать

Иерархии (manager → employee → employee), графы (referral-цепочки), последовательности дат — всё это в плоском SQL без рекурсии писать больно. Рекурсивный CTE решает задачи, которые иначе требуют кода в Python.

На middle-собесах часто дают: «построй иерархию подчинённых от CEO». Без WITH RECURSIVE решить либо невозможно, либо очень громоздко.

Синтаксис

WITH RECURSIVE t AS (
    -- 1. Base case (anchor)
    SELECT ...
    UNION ALL
    -- 2. Recursive case
    SELECT ... FROM t WHERE ...
)
SELECT * FROM t;

Две части через UNION ALL. Первая — starting point. Вторая — как получить next iteration из предыдущей.

Пример 1: иерархия сотрудников

WITH RECURSIVE subordinates AS (
    -- Base: CEO (нет manager_id)
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: сотрудники, чей менеджер уже в CTE
    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;

Результат — дерево подчинённых с уровнем.

Пример 2: все даты в диапазоне

WITH RECURSIVE dates AS (
    SELECT '2026-01-01'::DATE AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < '2026-04-30'
)
SELECT * FROM dates;

Генерирует daily series (если нет generate_series).

Пример 3: referral-цепочка

WITH RECURSIVE referrals AS (
    -- Base: конкретный пользователь
    SELECT id, name, referred_by, 0 AS depth
    FROM users
    WHERE id = 123

    UNION ALL

    -- Вверх по цепочке
    SELECT u.id, u.name, u.referred_by, r.depth + 1
    FROM users u
    JOIN referrals r ON u.id = r.referred_by
)
SELECT * FROM referrals;

Восстанавливает всю цепочку приглашений.

Пример 4: Fibonacci

Олд-скул, но показывает механизм:

WITH RECURSIVE fib(a, b, n) AS (
    SELECT 0, 1, 1
    UNION ALL
    SELECT b, a + b, n + 1 FROM fib WHERE n < 10
)
SELECT a FROM fib;

Частые ошибки

Бесконечная рекурсия

Без условия остановки — infinite loop. Большинство СУБД ставят лимит итераций (100-1000).

Циклы в данных

Если в referral-графе есть cycle (A → B → A) — рекурсия не остановится.

Защита:

WITH RECURSIVE t AS (
    SELECT id, name, ARRAY[id] AS path FROM users WHERE ...
    UNION ALL
    SELECT u.id, u.name, path || u.id
    FROM users u JOIN t ON u.referred_by = t.id
    WHERE u.id != ALL(path)  -- break cycle
)
SELECT * FROM t;

Храним path, не идём по уже посещённым узлам.

Забыли UNION ALL

UNION (без ALL) убирает дубликаты → может замедлить. Обычно UNION ALL.

Рекурсия в аггрегации

В recursive части нельзя GROUP BY, LIMIT, агрегатные функции. Выносите в финальный SELECT.

Оптимизация

Depth limit

WHERE level < 5

Ограничивает глубину → ускоряет и защищает от циклов.

Индексы на JOIN

employees(manager_id) — индекс ускоряет recursive step.

В ClickHouse

До недавнего не было рекурсивного CTE. В новых версиях — WITH RECURSIVE есть.

На собесе

«Как посчитать всех подчинённых manager_id = 10 на всех уровнях?».

Идеальный ответ: WITH RECURSIVE с base = manager_id = 10, recursive = JOIN по parent-child. Упомянуть защиту от циклов и depth limit.

Связанные темы

• 50 вопросов по SQL
• Задачи SQL на собесе
• Оконные функции

FAQ

Работает везде?

Postgres, MSSQL, Oracle, SQLite, MySQL 8+. ClickHouse — в свежих версиях.

Нужен ли RECURSIVE keyword?

Postgres — да. MSSQL — не нужно (просто WITH).

Медленнее обычного CTE?

Да, каждая итерация = JOIN. Но для иерархий — правильный инструмент.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.