Рекурсивные CTE

Рекурсивные CTE (Common Table Expressions) позволяют обходить иерархические и графовые структуры в SQL: организационные деревья, категории с вложенностью, BOM (Bill of Materials), файловые системы. В ClickHouse рекурсивные CTE доступны через синтаксис WITH RECURSIVE и требуют New Analyzer (включён по умолчанию с версии 24.3).

Синтаксис WITH RECURSIVE

Рекурсивный CTE состоит из двух частей, соединённых через UNION ALL:

WITH RECURSIVE cte_name AS (
    -- Base case (якорный запрос): начальные строки
    SELECT id, name, parent_id, 0 AS depth
    FROM table
    WHERE parent_id = 0

    UNION ALL

    -- Recursive case: ссылка на cte_name (сам CTE)
    SELECT t.id, t.name, t.parent_id, c.depth + 1
    FROM table t
    INNER JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

Как это работает:

1. Выполняется base case — возвращает начальные строки (iteration 0)
2. Результат iteration 0 подставляется в recursive case — возвращает iteration 1
3. Результат iteration 1 подставляется в recursive case — возвращает iteration 2
4. Процесс продолжается, пока recursive case возвращает строки
5. Все итерации объединяются через UNION ALL

Требования и ограничения

New Analyzer обязателен

-- ClickHouse 26.3 LTS: New Analyzer включён по умолчанию
-- Проверка текущей настройки:
SELECT getSetting('enable_analyzer');
-- Результат: 1

-- Если по какой-то причине отключён:
SET enable_analyzer = 1;

Ограничения

1. Только UNION ALL. UNION (с дедупликацией) не поддерживается. Используйте только UNION ALL.

2. max_recursive_cte_depth = 1000. По умолчанию максимальная глубина рекурсии — 1000 итераций. При превышении — ошибка. Изменить:

   SET max_recursive_cte_depth = 5000;

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

4. Работает на Distributed-таблицах. Начиная с PR #63939 рекурсивные CTE корректно работают с Distributed-таблицами.

Примеры

Организационная иерархия

CREATE TABLE employees (
    id UInt32,
    name String,
    manager_id UInt32,
    title LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO employees VALUES
    (1, 'Alice', 0, 'CEO'),
    (2, 'Bob', 1, 'VP Engineering'),
    (3, 'Carol', 1, 'VP Sales'),
    (4, 'Dave', 2, 'Director'),
    (5, 'Eve', 2, 'Director'),
    (6, 'Frank', 4, 'Manager'),
    (7, 'Grace', 4, 'Manager');

-- Обход дерева сверху вниз
WITH RECURSIVE org AS (
    -- Base: CEO (manager_id = 0)
    SELECT id, name, manager_id, title, 0 AS depth
    FROM employees
    WHERE manager_id = 0

    UNION ALL

    -- Recursive: подчинённые
    SELECT e.id, e.name, e.manager_id, e.title, org.depth + 1
    FROM employees e
    INNER JOIN org ON e.manager_id = org.id
)
SELECT
    repeat('  ', depth) || name AS hierarchy,
    title,
    depth
FROM org
ORDER BY depth, name;

Результат:

Категории товаров (иерархия)

CREATE TABLE categories (
    id UInt32,
    name String,
    parent_id UInt32
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO categories VALUES
    (1, 'Electronics', 0),
    (2, 'Phones', 1),
    (3, 'Laptops', 1),
    (4, 'Smartphones', 2),
    (5, 'Feature Phones', 2),
    (6, 'Gaming Laptops', 3);

-- Получить все подкатегории Electronics (id=1)
WITH RECURSIVE subcats AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE id = 1

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           subcats.path || ' > ' || c.name AS path
    FROM categories c
    INNER JOIN subcats ON c.parent_id = subcats.id
)
SELECT id, name, path FROM subcats;

BOM (Bill of Materials) — рекурсивная развёртка

CREATE TABLE bom (
    part_id UInt32,
    part_name String,
    parent_part_id UInt32,
    quantity UInt32
) ENGINE = MergeTree()
ORDER BY part_id;

INSERT INTO bom VALUES
    (1, 'Bicycle', 0, 1),
    (2, 'Frame', 1, 1),
    (3, 'Wheel', 1, 2),
    (4, 'Spoke', 3, 36),
    (5, 'Tire', 3, 1),
    (6, 'Hub', 3, 1);

-- Развёртка BOM с подсчётом общего количества
WITH RECURSIVE exploded AS (
    SELECT part_id, part_name, parent_part_id,
           quantity, quantity AS total_qty, 0 AS level
    FROM bom
    WHERE parent_part_id = 0

    UNION ALL

    SELECT b.part_id, b.part_name, b.parent_part_id,
           b.quantity,
           b.quantity * e.total_qty AS total_qty,
           e.level + 1
    FROM bom b
    INNER JOIN exploded e ON b.parent_part_id = e.part_id
)
SELECT
    repeat('  ', level) || part_name AS component,
    quantity AS qty_per_parent,
    total_qty
FROM exploded
ORDER BY level, part_name;

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

UNION вместо UNION ALL

-- ОШИБКА: UNION не поддерживается в рекурсивных CTE
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION          -- Ошибка!
    SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

-- ПРАВИЛЬНО: только UNION ALL
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

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

Если recursive case не сужает множество строк, рекурсия будет продолжаться до max_recursive_cte_depth:

-- Потенциально опасно: циклические ссылки в данных
-- (manager_id ссылается на подчинённого)
-- Защита: max_recursive_cte_depth = 1000 (по умолчанию)

Для защиты от циклов можно добавить проверку глубины:

WITH RECURSIVE tree AS (
    SELECT id, parent_id, 0 AS depth FROM nodes WHERE parent_id = 0
    UNION ALL
    SELECT n.id, n.parent_id, t.depth + 1
    FROM nodes n
    INNER JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 50  -- явное ограничение глубины
)
SELECT * FROM tree;

Ключевые выводы

1. WITH RECURSIVE = base case UNION ALL recursive case. Рекурсия продолжается, пока recursive case возвращает строки.
2. New Analyzer обязателен (enable_analyzer=1, включён по умолчанию с 24.3+). Без него — ошибка.
3. Только UNION ALL — UNION (с дедупликацией) не поддерживается.
4. max_recursive_cte_depth = 1000 по умолчанию. Для глубоких иерархий можно увеличить через SET.
5. Применения: организационные иерархии, категории, BOM-развёртка, граф-обходы. Для очень глубоких деревьев (тысячи уровней) рассмотрите материализацию путей.