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

Обобщённое табличное выражение (CTE) позволяет в Apache Doris определить временный результирующий набор внутри оператора SELECT. После однократного объявления с помощью предложения WITH к CTE можно обращаться несколько раз в одном SQL-запросе. CTE обычно используют для упрощения сложных запросов, устранения повторяющихся подзапросов и выражения логики с обращением к собственным результатам, например при обходе иерархий и графов.

Сценарии применения

CTE делают SQL понятнее и упрощают его сопровождение в следующих ситуациях:

• Один и тот же подзапрос используется несколько раз: оформите подзапрос как именованный CTE, чтобы не повторять его в основном запросе.
• Подзапросы с большой глубиной вложенности трудно читать: разбейте логику на несколько CTE и дайте имя каждому этапу, чтобы улучшить читаемость.
• Вычисление опирается на результат предыдущего этапа: при использовании вложенных CTE последующий CTE может напрямую обращаться к результату ранее определённого CTE.
• Обход иерархических или древовидных структур: например, организационных иерархий, каталогов категорий или вложенных веток комментариев. Используйте рекурсивный CTE, чтобы обойти все уровни одним запросом.
• Поиск достижимых узлов графа: например, обход рёбер от заданного узла для поиска всех достижимых узлов.

Основы использования

Обзор синтаксиса

Используйте предложение WITH, чтобы определить один или несколько CTE, разделённых запятыми. У каждого CTE есть имя и подзапрос:

WITH
    cte_name1 AS (subquery1),
    cte_name2 AS (subquery2)
SELECT ... FROM cte_name1 JOIN cte_name2 ON ...;

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

Простой пример CTE

В следующем примере cte1 и cte2 определяются в предложении WITH, а внешний оператор SELECT обращается к обоим CTE:

WITH
    cte1 AS (SELECT a, b FROM table1),
    cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Вложенный CTE

К CTE можно обращаться по имени внутри других CTE, что позволяет определять новые CTE на основе ранее определённых:

WITH
    cte1 AS (SELECT a, b FROM table1),
    cte2 AS (SELECT c, d FROM cte1)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

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

Рекурсивный CTE (CTE с ключевым словом RECURSIVE) позволяет выразить запрос с обращением к собственным результатам в одном операторе SQL. Его обычно используют для обхода деревьев и иерархий, обхода графов и иерархической агрегации.

Синтаксис

WITH [RECURSIVE] cte_name [(col1, col2, ...)] AS (
    <anchor_query>     -- Non-recursive part (executed once)
    UNION [ALL]
    <recursive_query>  -- Recursive part that can reference cte_name
)
SELECT ... FROM cte_name;

Основные положения:

• Ключевое слово RECURSIVE позволяет определению CTE ссылаться на себя.
• Начальная и рекурсивная части должны возвращать строго одинаковое количество столбцов с одинаковыми типами.
• В recursive_query можно обращаться к cte_name, обычно через JOIN.

Состав

Рекурсивный CTE состоит из двух частей, обычно соединённых с помощью UNION или UNION ALL:

Компонент	Описание
Начальный запрос	Нерекурсивная часть, которая выполняется один раз и формирует исходный набор строк.
Рекурсивный запрос	Может обращаться к самому CTE и формировать новые строки на основе строк, полученных на предыдущей итерации.

Рекурсия продолжается до тех пор, пока не перестанут появляться новые строки или не будет достигнуто системное ограничение.

Семантика выполнения (итерационная модель)

Обычно рекурсивный CTE выполняется следующим образом:

1. Выполнить anchor_query, записать результат в выходной набор (Output) и использовать его как рабочий набор (WorkSet) для первой итерации.
2. Пока WorkSet не пуст, повторять следующие действия:
   • Передать WorkSet на вход recursive_query, выполнить recursive_query и получить newRows.
   • При использовании UNION ALL: добавить newRows напрямую в Output и использовать newRows как WorkSet для следующей итерации.
   • При использовании UNION (с удалением дубликатов): вычислить разность между newRows и текущим Output (удалить дубликаты) и добавить только ранее не встречавшиеся строки в Output и в WorkSet для следующей итерации.
3. Повторять шаг 2, пока newRows не станет пустым или не будет достигнут заданный системой предел глубины рекурсии.

Переменная сеанса cte_max_recursion_depth задаёт максимальную глубину рекурсии. Значение по умолчанию — 100; при его превышении возникает ошибка.

Сравнение UNION и UNION ALL

Форма	Семантика	Производительность	Сценарии применения
UNION ALL	Сохраняет повторяющиеся строки	Низкие накладные расходы (без удаления дубликатов)	Когда дубликаты допустимы или обрабатываются на уровне приложения
UNION	Неявно удаляет дубликаты	Добавляет затраты на удаление дубликатов с помощью сортировки или хеширования на каждой итерации либо для всего результата; на больших объёмах данных эти затраты значительны	Когда дубликаты необходимо удалять внутри базы данных

Рекомендация: если семантика запроса допускает это и дубликаты можно обработать на уровне приложения, отдавайте предпочтение UNION ALL.

Примеры

Простой обход иерархии

Рекурсивный обход всего дерева, начиная с корневого узла:

CREATE TABLE tree
(
    id int,
    parent_id int,
    data varchar(100)
) DUPLICATE KEY (id)
DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES ('replication_num' = '1');

INSERT INTO tree VALUES (0, NULL, 'ROOT'), (1, 0, 'Child_1'), (2, 0, 'Child_2'), (3, 1, 'Child_1_1');

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY id;

Обход графа

Обход всех достижимых путей в графе с учётом направления рёбер:

CREATE TABLE graph
(
    c_from int,
    c_to int,
    label varchar(100)
) DUPLICATE KEY (c_from) DISTRIBUTED BY HASH(c_from) BUCKETS 1 PROPERTIES ('replication_num' = '1');

INSERT INTO graph VALUES (1, 2, '1 -> 2'), (1, 3, '1 -> 3'), (2, 3, '2 -> 3'), (1, 4, '1 -> 4'), (4, 5, '4 -> 5');

WITH RECURSIVE search_graph AS (
    SELECT c_from, c_to, label FROM graph g
UNION ALL
    SELECT g.c_from, g.c_to, g.label
    FROM graph g, search_graph sg
    WHERE g.c_from = sg.c_to
)
SELECT DISTINCT * FROM search_graph ORDER BY c_from, c_to;

Примечание: в приведённом выше примере для удаления дубликатов в конце используется SELECT DISTINCT. Если использовать UNION для удаления дубликатов внутри рекурсии, они будут удаляться на каждой итерации, что требует больших затрат.

Ограничения рекурсивных CTE

При использовании рекурсивного CTE действуют следующие ограничения:

• Оператором верхнего уровня внутри CTE должен быть UNION или UNION ALL.
• Нерекурсивный подзапрос не может обращаться к самому рекурсивному CTE.
• Рекурсивный подзапрос может обращаться к рекурсивному CTE только один раз.
• Если рекурсивный подзапрос содержит вложенный подзапрос, этот вложенный подзапрос не может обращаться к рекурсивному CTE.
• Типы выходных столбцов рекурсивного CTE определяются нерекурсивной частью. Если типы в рекурсивной и нерекурсивной частях не совпадают, возникает ошибка. В этом случае необходимо добавить явное преобразование CAST, чтобы согласовать типы данных в обеих частях.
• Переменная сеанса cte_max_recursion_depth ограничивает максимальное количество рекурсивных итераций, предотвращая бесконечные циклы. Значение по умолчанию — 100.

Распространённые ошибки и их устранение

Проблема	Возможная причина	Решение
Количество или типы столбцов в начальной и рекурсивной частях не совпадают	Два списка SELECT различаются количеством или типами столбцов	Убедитесь, что количество, порядок и типы столбцов в обеих частях совпадают. При необходимости используйте CAST или явные имена столбцов.
Начальная часть ссылается на себя (недопустимо)	Начальная часть не может обращаться к самому CTE	Обращайтесь к CTE только в рекурсивной части. Проверьте синтаксис или дерево разбора.
Бесконечная рекурсия / превышение максимальной глубины рекурсии	У рекурсии нет условия завершения или оно задано неверно	Добавьте фильтр WHERE или измените общесистемный предел глубины рекурсии. Если логика действительно приводит к бесконечной рекурсии, исправьте логику запроса.

Дополнительные материалы

• Руководство MySQL по рекурсивным CTE: стандартное определение и примеры рекурсивных CTE.