Клауза WITH

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

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

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

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

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

Синтаксис

или

Примеры

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

Пример 2: Исключение результата суммы (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 запрос