Общие табличные выражения (CTE)

Елена Дворядкина

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

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

Синтаксис CTE

CTE создается с помощью выражения WITH перед основным запросом:

WITH <cte_name> AS (<cte_subquery>)
<primary_query>;

где:

  • <cte_name> — имя CTE, по которому на него можно ссылаться в других частях основного SQL-запроса;

  • <cte_subquery> — SQL-запрос, результирующий набор данных которого заполняет CTE;

  • <primary_query> — основной SQL-запрос, использующий CTE.

Синтаксис CTE для использования результатов скалярного подзапроса немного отличается:

WITH (<cte_scalar_subquery>) AS <cte_name>
<primary_query>;

В одном выражении WITH можно определить несколько CTE, разделяя их запятыми:

WITH
    <cte1>,
    <cte2>,
    <cte3>
<primary_query>;

Каждое CTE может ссылаться на любое предыдущее CTE в рамках блока WITH.

Примеры

В этом разделе приведены примеры использования CTE в запросах для анализа данных о премиях, выданных сотрудникам сети магазинов в двух городах за сентябрь, которые содержатся в таблице employee_bonus_sept_24:

CREATE TABLE employee_bonus_sept_24 (
    empl_id UInt16,
    full_name String,
    position Enum('store manager' = 1, 'cashier' = 2, 'sales assistant' = 3),
    store_id UInt8,
    city Enum('New York' = 1, 'Chicago' = 2),
    bonus UInt32)
ENGINE = MergeTree
ORDER BY empl_id;
INSERT INTO employee_bonus_sept_24 VALUES
    (1, 'James Wilson', 1, 101, 1, 1000),
    (2, 'Mary Burton', 2, 101, 1, 450),
    (3, 'Patricia Robinson', 2, 101, 1, 500),
    (4, 'Robert Gray', 1, 102, 1, 1200),
    (5, 'Elizabeth Tucker', 2, 102, 1, 470),
    (6, 'Joseph Lewis', 3, 102, 1, 700),
    (7, 'William Ferguson', 1, 201, 2, 970),
    (8, 'Linda Black', 2, 201, 2, 350),
    (9, 'David Green', 2, 201, 2, 370),
    (10, 'Daniel Gray', 3, 201, 2, 770),
    (11, 'Mark Armstrong', 1, 202, 2, 1200),
    (12, 'Donald Carter', 2, 202, 2, 420),
    (13, 'Elizabeth Collins', 2, 202, 2, 400),
    (14, 'Paul Brown', 3, 202, 2, 660),
    (15, 'Andrew Clarke', 3, 202, 2, 680);
    ┌─empl_id─┬─full_name─────────┬─position────────┬─store_id─┬─city─────┬─bonus─┐
 1. │       1 │ James Wilson      │ store manager   │      101 │ New York │  1000 │
 2. │       2 │ Mary Burton       │ cashier         │      101 │ New York │   450 │
 3. │       3 │ Patricia Robinson │ cashier         │      101 │ New York │   500 │
 4. │       4 │ Robert Gray       │ store manager   │      102 │ New York │  1200 │
 5. │       5 │ Elizabeth Tucker  │ cashier         │      102 │ New York │   470 │
 6. │       6 │ Joseph Lewis      │ sales assistant │      102 │ New York │   700 │
 7. │       7 │ William Ferguson  │ store manager   │      201 │ Chicago  │   970 │
 8. │       8 │ Linda Black       │ cashier         │      201 │ Chicago  │   350 │
 9. │       9 │ David Green       │ cashier         │      201 │ Chicago  │   370 │
10. │      10 │ Daniel Gray       │ sales assistant │      201 │ Chicago  │   770 │
11. │      11 │ Mark Armstrong    │ store manager   │      202 │ Chicago  │  1200 │
12. │      12 │ Donald Carter     │ cashier         │      202 │ Chicago  │   420 │
13. │      13 │ Elizabeth Collins │ cashier         │      202 │ Chicago  │   400 │
14. │      14 │ Paul Brown        │ sales assistant │      202 │ Chicago  │   660 │
15. │      15 │ Andrew Clarke     │ sales assistant │      202 │ Chicago  │   680 │
    └─────────┴───────────────────┴─────────────────┴──────────┴──────────┴───────┘

Запрос с CTE

В приведенном ниже запросе:

  • с помощью CTE (avg_bonus) вычисляется средний размер премии для каждой должности по всем магазинам в данном месяце;

  • выводятся только те сотрудники, которые получили премию выше средней.

WITH avg_bonus AS
    (
        SELECT position, round(avg(bonus)) AS position_avg_bonus
        FROM employee_bonus_sept_24
        GROUP BY position
    )
SELECT
    b.full_name AS employee,
    b.position,
    b.store_id AS store,
    b.bonus,
    ab.position_avg_bonus
FROM employee_bonus_sept_24 AS b
INNER JOIN avg_bonus AS ab ON b.position = ab.position
WHERE b.bonus > ab.position_avg_bonus
ORDER BY b.position, b.bonus DESC;
   ┌─employee──────────┬─position────────┬─store─┬─bonus─┬─position_avg_bonus─┐
1. │ Robert Gray       │ store manager   │   102 │  1200 │               1092 │
2. │ Mark Armstrong    │ store manager   │   202 │  1200 │               1092 │
3. │ Patricia Robinson │ cashier         │   101 │   500 │                423 │
4. │ Elizabeth Tucker  │ cashier         │   102 │   470 │                423 │
5. │ Mary Burton       │ cashier         │   101 │   450 │                423 │
6. │ Daniel Gray       │ sales assistant │   201 │   770 │                702 │
   └───────────────────┴─────────────────┴───────┴───────┴────────────────────┘

Оконные функции в CTE

Если в запросе нужно отфильтровать данные по значениям оконных функций, вычисление этих значений необходимо выполнять в рамках CTE (или подзапроса).

Например, в следующем запросе сначала в CTE bonus_rank_by_city премии сотрудников ранжируются с помощью оконной функции dense_rank() с окном по городу, затем в выражении WHERE все строки фильтруются по вычисленному рангу, чтобы вывести 3 самых высоких премии сотрудников в каждом городе:

WITH bonus_rank_by_city AS
    (
        SELECT *, dense_rank() OVER (PARTITION BY city ORDER BY bonus DESC) AS rnk
        FROM employee_bonus_sept_24
        ORDER BY city, rnk
    )
SELECT *
FROM bonus_rank_by_city
WHERE rnk BETWEEN 1 AND 3;
   ┌─empl_id─┬─full_name────────┬─position────────┬─store_id─┬─city─────┬─bonus─┬─rnk─┐
1. │       4 │ Robert Gray      │ store manager   │      102 │ New York │  1200 │   1 │
2. │       1 │ James Wilson     │ store manager   │      101 │ New York │  1000 │   2 │
3. │       6 │ Joseph Lewis     │ sales assistant │      102 │ New York │   700 │   3 │
4. │      11 │ Mark Armstrong   │ store manager   │      202 │ Chicago  │  1200 │   1 │
5. │       7 │ William Ferguson │ store manager   │      201 │ Chicago  │   970 │   2 │
6. │      10 │ Daniel Gray      │ sales assistant │      201 │ Chicago  │   770 │   3 │
   └─────────┴──────────────────┴─────────────────┴──────────┴──────────┴───────┴─────┘

Несколько CTE в запросе

В следующем запросе используются два CTE:

  • avg_bonus_position — средний размер месячной премии по позиции;

  • avg_bonus_city — средний размер месячной премии по городу.

WITH
    avg_bonus_position AS
    (
        SELECT position, round(avg(bonus)) AS position_avg_bonus
        FROM employee_bonus_sept_24
        GROUP BY position
    ),
    avg_bonus_city AS
    (
        SELECT city, round(avg(bonus)) AS city_avg_bonus
        FROM employee_bonus_sept_24
        GROUP BY city
    )
SELECT
    b.full_name AS employee,
    b.position AS position,
    b.store_id AS store,
    b.city AS city,
    b.bonus,
    abp.position_avg_bonus,
    abc.city_avg_bonus
FROM employee_bonus_sept_24 AS b
INNER JOIN avg_bonus_position AS abp ON b.position = abp.position
INNER JOIN avg_bonus_city AS abc ON b.city = abc.city
ORDER BY b.position, b.bonus DESC;
    ┌─employee──────────┬─position────────┬─store─┬─city─────┬─bonus─┬─position_avg_bonus─┬─city_avg_bonus─┐
 1. │ Robert Gray       │ store manager   │   102 │ New York │  1200 │               1092 │            720 │
 2. │ Mark Armstrong    │ store manager   │   202 │ Chicago  │  1200 │               1092 │            647 │
 3. │ James Wilson      │ store manager   │   101 │ New York │  1000 │               1092 │            720 │
 4. │ William Ferguson  │ store manager   │   201 │ Chicago  │   970 │               1092 │            647 │
 5. │ Patricia Robinson │ cashier         │   101 │ New York │   500 │                423 │            720 │
 6. │ Elizabeth Tucker  │ cashier         │   102 │ New York │   470 │                423 │            720 │
 7. │ Mary Burton       │ cashier         │   101 │ New York │   450 │                423 │            720 │
 8. │ Donald Carter     │ cashier         │   202 │ Chicago  │   420 │                423 │            647 │
 9. │ Elizabeth Collins │ cashier         │   202 │ Chicago  │   400 │                423 │            647 │
10. │ David Green       │ cashier         │   201 │ Chicago  │   370 │                423 │            647 │
11. │ Linda Black       │ cashier         │   201 │ Chicago  │   350 │                423 │            647 │
12. │ Daniel Gray       │ sales assistant │   201 │ Chicago  │   770 │                702 │            647 │
13. │ Joseph Lewis      │ sales assistant │   102 │ New York │   700 │                702 │            720 │
14. │ Andrew Clarke     │ sales assistant │   202 │ Chicago  │   680 │                702 │            647 │
15. │ Paul Brown        │ sales assistant │   202 │ Chicago  │   660 │                702 │            647 │
    └───────────────────┴─────────────────┴───────┴──────────┴───────┴────────────────────┴────────────────┘

Вложенные CTE

Следующий запрос позволяет оценить объем средств, выделенных на премирование сотрудников в разных магазинах — посмотреть, какой процент сумма премий, выплаченных сотрудникам в каждом магазине, занимает от общей суммы расходов на премии сотрудникам всей сети. Для этого в запросе используются вложенные CTE:

  • bonus_all_stores — общая сумма расходов на премии сотрудникам всех магазинов;

  • store_bonus_totals_and_rates — сумма премий, выплаченных сотрудникам в каждом магазине, и ее доля относительно общих расходов на премирование сотрудников в рассматриваемом месяце, которая вычисляется с использованием выходных данных CTE bonus_all_stores.

WITH
    (SELECT sum(bonus) AS all_stores_bonus FROM employee_bonus_sept_24) AS bonus_all_stores,
    store_bonus_totals_and_rates AS
    (
        SELECT
            store_id,
            count() AS employee_count,
            sum(bonus) AS store_bonus_total,
            store_bonus_total / bonus_all_stores AS store_bonus_rate
        FROM employee_bonus_sept_24
        GROUP BY store_id
    )
SELECT
    store_id,
    employee_count,
    store_bonus_total AS total_bonus,
    round(store_bonus_rate * 100, 2) AS bonus_percentage
FROM store_bonus_totals_and_rates;
   ┌─store_id─┬─employee_count─┬─total_bonus─┬─bonus_percentage─┐
1. │      101 │              3 │        1950 │            19.23 │
2. │      102 │              3 │        2370 │            23.37 │
3. │      201 │              4 │        2460 │            24.26 │
4. │      202 │              5 │        3360 │            33.14 │
   └──────────┴────────────────┴─────────────┴──────────────────┘

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

Обзор

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

Для создания рекурсивного CTE используйте модификатор RECURSIVE после WITH. Синтаксис рекурсивного CTE в общем виде:

WITH RECURSIVE <cte_name> AS (
    <cte_non_recursive_term>
    UNION ALL
    <cte_recursive_term>
)
SELECT <list_of_columns> FROM <cte_name>;

где:

  • <cte_non_recursive_term> — нерекурсивная часть запроса CTE, которая выполняется в первую очередь. Полученные данные записываются в результирующую выборку CTE, а также используются как входные значения для первого вызова рекурсивной части.

  • <cte_recursive_term> — рекурсивная часть запроса CTE, которая обращается к результату своего выполнения на предыдущей итерации (доступен под именем CTE — <cte_name>) и выполняется до тех пор, пока она возвращает какие-либо строки. Результат выполнения рекурсивной части, полученный на каждой итерации, также записывается в результирующую выборку CTE, которая будет доступна в основном запросе под тем же именем <cte_name> после того, как отработает весь запрос CTE.

 
Например, создайте таблицу, в которой будет храниться информация о позициях в компании:

CREATE TABLE positions (
    id UInt32,
    position String,
    manager_id Nullable(UInt32)
) ENGINE = Memory;

Заполните таблицу тестовыми данными:

INSERT INTO positions VALUES
    (1, 'CEO', NULL),
    (2, 'CTO', 1),
    (3, 'Backend Team Lead', 2),
    (4, 'Frontend Team Lead', 2),
    (5, 'Backend Developer Senior', 3),
    (6, 'Backend Developer Middle', 3),
    (7, 'Frontend Developer Senior', 4),
    (8, 'Frontend Developer Middle', 4),
    (9, 'Frontend Developer Junior', 4),
    (10, 'UX/UI Designer', 2);

Выполните запрос с использованием рекурсивного CTE, чтобы посмотреть, кому подчиняются сотрудники на различных позициях в организационной структуре компании:

WITH RECURSIVE employee_hierarchy AS (
    -- Нерекурсивная часть: начать с CEO
    SELECT id, position, manager_id, toUInt16(0) AS level, [position] AS path
    FROM positions
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивная часть: добавить подчиненных
    SELECT p.id, p.position, p.manager_id, eh.level + 1, arrayConcat(eh.path, [p.position]) AS path
    FROM positions p
    JOIN employee_hierarchy eh ON p.manager_id = eh.id
)
SELECT position, level, arrayStringConcat(path, ' -> ') AS full_path
FROM employee_hierarchy;

Этот запрос выполняется в следующей последовательности:

  1. Нерекурсивная часть CTE возвращает стартовые данные из таблицы positions — позицию, для которой не указан менеджер (по условию manager_id IS NULL). В данном примере это CEO — верхняя позиция в иерархии компании, которой назначается уровень 0.

  2. Полученные в результате выполнения нерекурсивной части данные используются для вычисления первой итерации рекурсивной части CTE — осуществляется поиск позиций, которые находятся в подчинении CEO, путем выполнения JOIN для таблицы positions и CTE employee_hierarchy по ключу соединения positions.manager_id = employee_hierarchy.id. Таким образом, первая итерация рекурсивной части CTE возвращает позицию CTO (для которой значение manager_id равно идентификатору позиции CEO) и присваивает ей уровень 1 в иерархии компании. Результат этой итерации добавляется в результирующую выборку CTE и будет использоваться в качестве входных значений для следующей итерации.

  3. Выполняются еще две итерации рекурсивной части, которые возвращают позиции следующих двух уровней в иерархии компании — 2 и 3.

  4. Четвертая итерация возвращает пустой результат, так как у позиций уровня 3 в таблице positions нет подчиненных — выполнение CTE на этом завершается.

  5. Основной запрос обращается к CTE, чтобы получить выборку из его результирующего набора данных, который представляет собой объединение результатов выполнения нерекурсивной части и всех итераций рекурсивной части.

   ┌─position─┬─level─┬─full_path─┐
1. │ CEO      │     0 │ CEO       │
   └──────────┴───────┴───────────┘
   ┌─position─┬─level─┬─full_path──┐
2. │ CTO      │     1 │ CEO -> CTO │
   └──────────┴───────┴────────────┘
   ┌─position───────────┬─level─┬─full_path────────────────────────┐
3. │ Backend Team Lead  │     2 │ CEO -> CTO -> Backend Team Lead  │
4. │ Frontend Team Lead │     2 │ CEO -> CTO -> Frontend Team Lead │
5. │ UX/UI Designer     │     2 │ CEO -> CTO -> UX/UI Designer     │
   └────────────────────┴───────┴──────────────────────────────────┘
    ┌─position──────────────────┬─level─┬─full_path─────────────────────────────────────────────────────┐
 6. │ Backend Developer Senior  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Senior   │
 7. │ Backend Developer Middle  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Middle   │
 8. │ Frontend Developer Senior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Senior │
 9. │ Frontend Developer Middle │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Middle │
10. │ Frontend Developer Junior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Junior │
    └───────────────────────────┴───────┴───────────────────────────────────────────────────────────────┘

Порядок поиска

Приведенный выше запрос выводит данные в том порядке, в каком они были добавлены в результирующий набор рекурсивного CTE — отсортированными "сначала в ширину" (breadth-first order) по уровню вложенности рекурсивного запроса, который в данном случае соответствует уровню позиции в иерархии компании (level).

Чтобы отсортировать результаты "сначала в глубину" (depth-first order), примените сортировку по полю path, в котором хранится путь до каждой строки в виде массива уже просмотренных строк (в данном примере это последовательность всех вышестоящих позиций в иерархии компании для каждой позиции).

WITH RECURSIVE employee_hierarchy AS (
    -- Нерекурсивная часть: начать с CEO
    SELECT id, position, manager_id, toUInt16(0) AS level, [position] AS path
    FROM positions
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивная часть: добавить подчиненных
    SELECT p.id, p.position, p.manager_id, eh.level + 1, arrayConcat(eh.path, [p.position]) AS path
    FROM positions p
    JOIN employee_hierarchy eh ON p.manager_id = eh.id
)
SELECT repeat('    ', level) || position AS position, level, arrayStringConcat(path, ' -> ') AS full_path
FROM employee_hierarchy
ORDER BY path;
    ┌─position──────────────────────────────┬─level─┬─full_path─────────────────────────────────────────────────────┐
 1. │ CEO                                   │     0 │ CEO                                                           │
 2. │     CTO                               │     1 │ CEO -> CTO                                                    │
 3. │         Backend Team Lead             │     2 │ CEO -> CTO -> Backend Team Lead                               │
 4. │             Backend Developer Middle  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Middle   │
 5. │             Backend Developer Senior  │     3 │ CEO -> CTO -> Backend Team Lead -> Backend Developer Senior   │
 6. │         Frontend Team Lead            │     2 │ CEO -> CTO -> Frontend Team Lead                              │
 7. │             Frontend Developer Junior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Junior │
 8. │             Frontend Developer Middle │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Middle │
 9. │             Frontend Developer Senior │     3 │ CEO -> CTO -> Frontend Team Lead -> Frontend Developer Senior │
10. │         UX/UI Designer                │     2 │ CEO -> CTO -> UX/UI Designer                                  │
    └───────────────────────────────────────┴───────┴───────────────────────────────────────────────────────────────┘

Выявление бесконечных циклов

При работе с рекурсивным CTE важно проверить, не приводит ли рекурсивная часть запроса к бесконечному циклу, и при необходимости установить условия остановки рекурсии. В данном разделе приведены примеры различных механизмов защиты запросов от зацикливания:

 

Отслеживание уже обработанных значений

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

Следующий пример показывает как можно обойти граф с циклом, исключив риск бесконечной рекурсии.

Создайте таблицу graph:

CREATE TABLE graph (
    from_node UInt32,
    to_node UInt32
) ENGINE = MergeTree()
ORDER BY (from_node, to_node);

Вставьте в таблицу тестовые данные, соответствующие графу с циклом 1 → 2 → 4 → 7 → 1:

INSERT INTO graph VALUES
    (1, 2), (1, 3),
    (2, 4), (2, 5),
    (3, 6),
    (4, 7), (4, 8),
    (5, 9),
    (7, 1),
    (6, 10);

Запрос на обход графа без проверки на наличие циклов уйдет в бесконечную рекурсию и завершится ошибкой Maximum recursive CTE evaluation depth (1000) exceeded:

WITH RECURSIVE graph_traversal AS (
    SELECT from_node, to_node,
    FROM graph
    WHERE from_node = 1 -- Начать с узла 1

    UNION ALL

    SELECT g.from_node, g.to_node,
    FROM graph_traversal AS gt
    JOIN graph AS g ON gt.to_node = g.from_node
)
SELECT from_node, to_node,
FROM graph_traversal;

Чтобы избежать зацикливания запроса, сохраняйте пройденные пути до узлов графа в виде массивов уже просмотренных узлов в поле path и используйте их для отслеживания повторного прохождения узлов, записывая признак обнаружения цикла в поле has_cycle:

WITH RECURSIVE graph_traversal AS (
    SELECT from_node, to_node, 1 AS depth, [from_node, to_node] AS path, false AS has_cycle
    FROM graph
    WHERE from_node = 1 -- Начать с узла 1

    UNION ALL

    SELECT g.from_node, g.to_node, gt.depth + 1, arrayPushBack(gt.path, g.to_node), has(gt.path, g.to_node) AS has_cycle
    FROM graph_traversal AS gt
    JOIN graph AS g ON gt.to_node = g.from_node
    WHERE NOT gt.has_cycle -- Остановить рекурсию при обнаружении цикла
)
SELECT from_node, to_node, depth, path, has_cycle, arrayStringConcat(path, ' -> ') AS path_str
FROM graph_traversal;
Результат запроса
   ┌─from_node─┬─to_node─┬─depth─┬─path──┬─has_cycle─┬─path_str─┐
1. │         1 │       2 │     1 │ [1,2] │ false     │ 1 -> 2   │
2. │         1 │       3 │     1 │ [1,3] │ false     │ 1 -> 3   │
   └───────────┴─────────┴───────┴───────┴───────────┴──────────┘
   ┌─from_node─┬─to_node─┬─depth─┬─path────┬─has_cycle─┬─path_str────┐
3. │         2 │       4 │     2 │ [1,2,4] │ false     │ 1 -> 2 -> 4 │
4. │         2 │       5 │     2 │ [1,2,5] │ false     │ 1 -> 2 -> 5 │
5. │         3 │       6 │     2 │ [1,3,6] │ false     │ 1 -> 3 -> 6 │
   └───────────┴─────────┴───────┴─────────┴───────────┴─────────────┘
   ┌─from_node─┬─to_node─┬─depth─┬─path───────┬─has_cycle─┬─path_str──────────┐
6. │         4 │       7 │     3 │ [1,2,4,7]  │ false     │ 1 -> 2 -> 4 -> 7  │
7. │         4 │       8 │     3 │ [1,2,4,8]  │ false     │ 1 -> 2 -> 4 -> 8  │
8. │         5 │       9 │     3 │ [1,2,5,9]  │ false     │ 1 -> 2 -> 5 -> 9  │
9. │         6 │      10 │     3 │ [1,3,6,10] │ false     │ 1 -> 3 -> 6 -> 10 │
   └───────────┴─────────┴───────┴────────────┴───────────┴───────────────────┘
    ┌─from_node─┬─to_node─┬─depth─┬─path────────┬─has_cycle─┬─path_str──────────────┐
10. │         7 │       1 │     4 │ [1,2,4,7,1] │ true      │ 1 -> 2 -> 4 -> 7 -> 1 │
    └───────────┴─────────┴───────┴─────────────┴───────────┴───────────────────────┘

Ограничение глубины рекурсии

При выполнении рекурсивного CTE можно сохранять уровень вложенности рекурсий в отдельном столбце и использовать его для ограничения глубины поиска. В приведенном ниже примере это столбец depth:

WITH RECURSIVE graph_traversal AS (
    SELECT from_node, to_node, 1 AS depth, [from_node, to_node] AS path
    FROM graph
    WHERE from_node = 1 -- Начать с узла 1

    UNION ALL

    SELECT g.from_node, g.to_node, gt.depth + 1, arrayPushBack(gt.path, g.to_node)
    FROM graph_traversal AS gt
    JOIN graph AS g ON gt.to_node = g.from_node
    WHERE gt.depth < 3 -- Ограничение глубины рекурсии
)
SELECT from_node, to_node, depth, path, arrayStringConcat(path, ' -> ') AS path_str
FROM graph_traversal;

Максимальную глубину рекурсивных CTE в ADQM можно также настроить на уровне сессии с помощью параметра max_recursive_cte_evaluation_depth (значение по умолчанию — 1000), например:

SET max_recursive_cte_evaluation_depth = 3;

Использование LIMIT

Еще один способ остановить бесконечную рекурсию CTE — добавить LIMIT в основной запрос. Например, следующий запрос вычисляет факториал для чисел от 1 до 10:

WITH RECURSIVE r AS (
    SELECT 1 AS i, toUInt32(1) AS factorial
    UNION ALL
    SELECT i+1 AS i, factorial*i as factorial
    FROM r
)
SELECT * FROM r LIMIT 10;
Результат запроса
   ┌─i─┬─factorial─┐
1. │ 1 │         1 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
2. │ 2 │         2 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
3. │ 3 │         6 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
4. │ 4 │        24 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
5. │ 5 │       120 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
6. │ 6 │       720 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
7. │ 7 │      5040 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
8. │ 8 │     40320 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
9. │ 9 │    362880 │
   └───┴───────────┘
    ┌──i─┬─factorial─┐
10. │ 10 │   3628800 │
    └────┴───────────┘
ПРИМЕЧАНИЕ

В этом примере начальное значение 1 в поле factorial приводится к типу UInt32, чтобы при вычислении факториала не было переполнения типа данных, так как тип по умолчанию — UInt8.

Ограничение по значению

Условием остановки рекурсии также может быть значение в каком-либо поле. Например:

WITH RECURSIVE r AS (
    SELECT 1 AS i, toUInt32(1) AS factorial
    UNION ALL
    SELECT i+1 AS i, factorial*i as factorial
    FROM r
    WHERE factorial < 5000
)
SELECT * FROM r;
Результат запроса
   ┌─i─┬─factorial─┐
1. │ 1 │         1 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
2. │ 2 │         2 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
3. │ 3 │         6 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
4. │ 4 │        24 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
5. │ 5 │       120 │
   └───┴───────────┘
   ┌─i─┬─factorial─┐
6. │ 6 │       720 │
   └───┴───────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней