Клаузула 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 запрос