Рекурсивные CTE в SQL — WITH RECURSIVE

Коротко

Рекурсивный CTE — запрос, который ссылается сам на себя. Используется для обхода иерархий (орг. структура, категории, комментарии), генерации последовательностей (даты, числа) и поиска путей в графах. Синтаксис: WITH RECURSIVE. На собеседованиях аналитиков рекурсивные CTE — продвинутая тема, но она встречается в задачах на иерархии и заполнение пропущенных дат.

Синтаксис

WITH RECURSIVE cte_name AS (
    -- Базовый случай (anchor)
    SELECT ...

    UNION ALL

    -- Рекурсивный случай
    SELECT ...
    FROM cte_name
    WHERE условие_остановки
)
SELECT * FROM cte_name;

Три части:

1. Anchor — начальный набор строк (точка входа)
2. UNION ALL — объединяет результаты итераций
3. Рекурсивная часть — ссылается на CTE, добавляет новые строки

Рекурсия останавливается, когда рекурсивная часть возвращает 0 строк.

Генерация последовательности чисел

WITH RECURSIVE numbers AS (
    SELECT 1 AS n            -- anchor: начинаем с 1

    UNION ALL

    SELECT n + 1             -- рекурсия: +1
    FROM numbers
    WHERE n < 10             -- остановка: до 10
)
SELECT n FROM numbers;
-- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Каждая итерация берёт результат предыдущей и добавляет 1. Когда n = 10, условие n < 10 ложно — рекурсия останавливается.

Генерация диапазона дат

Самый практичный кейс для аналитика — заполнить пропущенные даты в данных:

WITH RECURSIVE date_range AS (
    SELECT DATE '2025-01-01' AS dt

    UNION ALL

    SELECT dt + INTERVAL '1 day'
    FROM date_range
    WHERE dt < DATE '2025-01-31'
)
SELECT dt FROM date_range;
-- 2025-01-01, 2025-01-02, ..., 2025-01-31

Заполнение пропущенных дат в метриках

WITH RECURSIVE dates AS (
    SELECT MIN(order_date) AS dt FROM orders

    UNION ALL

    SELECT dt + INTERVAL '1 day'
    FROM dates
    WHERE dt < (SELECT MAX(order_date) FROM orders)
)
SELECT
    d.dt AS order_date,
    COALESCE(o.revenue, 0) AS revenue,
    COALESCE(o.order_count, 0) AS order_count
FROM dates d
LEFT JOIN (
    SELECT
        order_date,
        SUM(amount) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY order_date
) o ON d.dt = o.order_date
ORDER BY d.dt;

Без рекурсивного CTE дни без заказов пропадут из отчёта. С ним — все дни на месте, пропуски заполнены нулями. Аналог generate_series() в PostgreSQL, но рекурсивный CTE работает в любой СУБД.

Обход иерархии

Организационная структура

-- Таблица employees
-- employee_id | name    | manager_id
-- 1           | CEO     | NULL
-- 2           | VP Sales| 1
-- 3           | VP Tech | 1
-- 4           | Manager | 2
-- 5           | Dev     | 3
-- 6           | Analyst | 4

WITH RECURSIVE org AS (
    -- Anchor: корень дерева (CEO, manager_id = NULL)
    SELECT
        employee_id,
        name,
        manager_id,
        0 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсия: подчинённые
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        o.level + 1,
        o.path || ' → ' || e.name
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
)
SELECT level, path FROM org ORDER BY path;

level	path
0	CEO
1	CEO → VP Sales
2	CEO → VP Sales → Manager
3	CEO → VP Sales → Manager → Analyst
1	CEO → VP Tech
2	CEO → VP Tech → Dev

Все подчинённые конкретного менеджера

WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE employee_id = 2  -- VP Sales

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;
-- VP Sales, Manager, Analyst

Дерево категорий

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        name,
        parent_id,
        name AS full_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.full_path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT full_path FROM category_tree;
-- Электроника
-- Электроника > Ноутбуки
-- Электроника > Ноутбуки > Игровые
-- Электроника > Телефоны

Цепочки рефералов

WITH RECURSIVE referral_chain AS (
    SELECT
        user_id,
        referred_by,
        1 AS depth,
        user_id::TEXT AS chain
    FROM users
    WHERE referred_by IS NULL  -- корневые пользователи

    UNION ALL

    SELECT
        u.user_id,
        u.referred_by,
        rc.depth + 1,
        rc.chain || ' → ' || u.user_id::TEXT
    FROM users u
    JOIN referral_chain rc ON u.referred_by = rc.user_id
)
SELECT * FROM referral_chain
ORDER BY depth DESC
LIMIT 10;

Показывает цепочки привлечения пользователей и их глубину.

Защита от бесконечной рекурсии

-- Лимит итераций (PostgreSQL)
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte
    -- забыли WHERE — бесконечная рекурсия!
)
SELECT * FROM cte
LIMIT 100;  -- LIMIT спасёт

-- Или через условие глубины
WITH RECURSIVE cte AS (
    SELECT 1 AS n, 1 AS depth
    UNION ALL
    SELECT n + 1, depth + 1
    FROM cte
    WHERE depth < 100  -- явное ограничение
)
SELECT * FROM cte;

PostgreSQL по умолчанию не ограничивает глубину рекурсии. Всегда добавляйте условие остановки (WHERE) или LIMIT.

UNION vs UNION ALL

-- UNION ALL — все строки, включая дубликаты (быстрее)
WITH RECURSIVE cte AS (
    ... UNION ALL ...
)

-- UNION — только уникальные (медленнее, дубликаты убираются)
WITH RECURSIVE cte AS (
    ... UNION ...
)

Для иерархий с возможными циклами (графы) используйте UNION — он предотвращает бесконечные циклы, убирая дубликаты. Для деревьев (без циклов) — UNION ALL быстрее.

Типичные ошибки

Забыли условие остановки. Рекурсия без WHERE в рекурсивной части — бесконечный цикл. Всегда добавляйте ограничение.

UNION вместо UNION ALL. UNION убирает дубликаты — это медленнее. Если дубликатов точно нет (дерево), используйте UNION ALL.

Циклы в данных. Если A → B → C → A, рекурсия зациклится. Решение: отслеживать посещённые узлы через массив или столбец path, фильтровать по нему.

Слишком глубокая рекурсия. Для 1000+ уровней вложенности рекурсивный CTE может быть медленным. Для таких случаев — materialized path или nested sets.

Вопросы с собеседований

-- Что такое рекурсивный CTE? -- CTE, который ссылается сам на себя. Состоит из anchor (начальный набор) и рекурсивной части, объединённых через UNION ALL. Используется для обхода иерархий и генерации последовательностей.

-- Как сгенерировать список дат от 1 до 31 января? -- WITH RECURSIVE dates AS (SELECT DATE '2025-01-01' AS dt UNION ALL SELECT dt + 1 FROM dates WHERE dt < '2025-01-31') SELECT dt FROM dates. В PostgreSQL проще: generate_series('2025-01-01', '2025-01-31', '1 day').

-- Как найти всех подчинённых менеджера? -- Рекурсивный CTE: anchor — сам менеджер, рекурсия — JOIN employees ON manager_id = employee_id предыдущего уровня.

-- Как предотвратить бесконечную рекурсию? -- Условие WHERE в рекурсивной части, ограничение глубины, LIMIT, или UNION (вместо UNION ALL) для удаления дубликатов в циклических графах.

---

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

FAQ

Рекурсивные CTE vs generate_series?

generate_series() — встроенная функция PostgreSQL для генерации последовательностей. Проще и быстрее для дат и чисел. Рекурсивные CTE — универсальнее, работают во всех СУБД и решают задачи с иерархиями, которые generate_series не может.

Когда аналитику нужны рекурсивные CTE?

Заполнение пропущенных дат в метриках, обход иерархий (орг. структура, категории товаров, цепочки рефералов), генерация календарных таблиц. Базовые CTE нужны чаще, но рекурсивные — мощный инструмент для специфических задач.

Поддерживаются ли рекурсивные CTE в MySQL?

Да, с MySQL 8.0. Синтаксис аналогичен PostgreSQL: WITH RECURSIVE. В MySQL 5.x рекурсивных CTE нет — нужны хранимые процедуры или приложение.

Как тренироваться

Рекурсивные CTE — продвинутая тема SQL. Задачи на CTE и оконные функции — в тренажёре Карьерник. Больше вопросов — в разделе с примерами.