Оператор WITH

ClickHouse поддерживает Общие Табличные Выражения (CTE) и заменяет код, определенный в операторе WITH, во всех местах его использования в остальной части запроса SELECT. Именованные подзапросы могут быть включены в контекст текущего и дочернего запроса в местах, где разрешены объекты таблиц. Рекурсия предотвращается путем скрытия текущих уровней CTE из выражения WITH.

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

Пример такого поведения приведен ниже

Если бы CTE возвращали точные результаты, а не просто фрагмент кода, вы всегда видели бы 1000000

Однако, из-за того, что мы ссылаемся на cte_numbers дважды, случайные числа генерируются каждый раз, и, соответственно, мы видим разные случайные результаты, такие как 280501, 392454, 261636, 196227 и так далее...

Синтаксис

или

Примеры

Пример 1: Использование постоянного выражения в качестве "переменной"

Пример 2: Исключение результата выражения sum(bytes) из списка колонок оператора SELECT

Пример 3: Использование результатов скалярного подзапроса

Пример 4: Повторное использование выражения в подзапросе

Рекурсивные запросы

Необязательный модификатор RECURSIVE позволяет запросу WITH ссылаться на свои собственные результаты. Пример:

Пример: Сумма целых чисел с 1 по 100

примечание

Рекурсивные CTE основаны на новом анализаторе запросов, введенном в версии 24.3. Если вы используете версию 24.3+ и сталкиваетесь с исключением (UNKNOWN_TABLE) или (UNSUPPORTED_METHOD), это означает, что новый анализатор отключен в вашем экземпляре, роли или профиле. Чтобы активировать анализатор, включите настройку allow_experimental_analyzer или обновите настройку compatibility до более поздней версии. Начиная с версии 24.8, новый анализатор был полностью переведен в промышленную эксплуатацию, и настройка allow_experimental_analyzer была переименована в enable_analyzer.

Общая форма рекурсивного запроса WITH всегда состоит из нерекурсивного термина, затем UNION ALL, затем рекурсивного термина, где только рекурсивный термин может содержать ссылку на результаты самого запроса. Запрос рекурсивного CTE выполняется следующим образом:

1. Оцените нерекурсивный термин. Поместите результат нерекурсивного термина в временную рабочую таблицу.
2. Пока рабочая таблица не пуста, повторяйте эти шаги:
   1. Оцените рекурсивный термин, подставляя текущие содержимое рабочей таблицы для рекурсивной самоссылки. Поместите результат рекурсивного термина в временную промежуточную таблицу.
   2. Замените содержимое рабочей таблицы содержимым промежуточной таблицы, затем опустошите промежуточную таблицу.

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

Пример: Обход дерева

Сначала создадим таблицу дерева:

Мы можем обходить это дерево с помощью такого запроса:

Пример: Обход дерева

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

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

Пример: Обход дерева в порядке глубины

Для создания порядка обхода в ширину стандартный подход заключается в добавлении колонки, которая отслеживает глубину поиска:

Пример: Обход дерева в порядке ширины

Обнаружение циклов

Сначала давайте создадим таблицу графа:

Мы можем пройти по этому графу с помощью такого запроса:

Пример: Обход графа без обнаружения циклов

Но если мы добавим цикл в этот граф, предыдущий запрос завершится с ошибкой Maximum recursive CTE evaluation depth:

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

Пример: Обход графа с обнаружением циклов

Бесконечные запросы

Также возможно использовать бесконечные рекурсивные запросы CTE, если в внешнем запросе используется LIMIT:

Пример: Бесконечный рекурсивный запрос CTE