Клауза WITH

ClickHouse поддерживает общие табличные выражения (CTE, Common Table Expressions), общие скалярные выражения и рекурсивные запросы.

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

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

Каждое обращение к общему табличному выражению в SELECT-запросах всегда заменяется подзапросом из его определения, если CTE явно не определено как материализованное (см. Материализованные общие табличные выражения). Рекурсия предотвращается за счёт скрытия текущего CTE в процессе разрешения идентификаторов.

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

Синтаксис

WITH <identifier> AS [MATERIALIZED] <subquery expression>

Пример

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

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

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

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

Материализованные общие табличные выражения

По умолчанию ClickHouse подставляет подзапрос CTE в каждое место, где на него есть ссылка, и каждый раз выполняет его заново. Добавление ключевого слова MATERIALIZED указывает ClickHouse выполнить подзапрос CTE ровно один раз, сохранить результат во временной таблице и использовать эту таблицу для всех ссылок. Это особенно полезно, когда один и тот же CTE используется в запросе несколько раз (например, в самосоединениях или нескольких подзапросах IN), поскольку лежащее в основе вычисление выполняется только один раз.

Примечание

Материализованные CTE — экспериментальная функция. Для их использования должны быть включены analyzer и SETTING enable_materialized_cte.

Синтаксис

WITH <identifier> AS MATERIALIZED (<subquery>)
SELECT ...

Когда использовать

Материализованные CTE наиболее полезны в следующих случаях:

Один и тот же CTE используется в запросе более одного раза. Без MATERIALIZED каждая ссылка заново и независимо выполняет подзапрос.
CTE содержит недетерминированные функции, такие как generateRandom. Материализация гарантирует, что все ссылки будут видеть одни и те же данные.
CTE включает ресурсоёмкие вычисления (агрегации, joins, большие сканирования), которые не следует повторять.

Совет

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

Примеры

Пример 1: Самосоединение по материализованному CTE

Без MATERIALIZED обе стороны join будут выполнять подзапрос независимо. С MATERIALIZED таблица сканируется один раз, и обе стороны join читают из одной и той же временной таблицы.

SET enable_materialized_cte = 1;

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE = Memory;
INSERT INTO users VALUES (1231, 'John', 33), (6666, 'Ksenia', 48), (8888, 'Alice', 50);

WITH
    a AS MATERIALIZED (SELECT * FROM users WHERE name = 'Alice')
SELECT count() FROM a AS l JOIN a AS r ON l.uid = r.uid;

┌─count()─┐
│       1 │
└─────────┘

Пример 2: Детерминированные результаты с недетерминированными функциями

Обычные CTE с generateRandom выдают разные результаты при каждом обращении. Материализация CTE обеспечивает согласованность:

SET enable_materialized_cte = 1;

WITH cte_numbers AS MATERIALIZED
(
    SELECT num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers);

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

Пример 3: Цепочка материализованных CTE

Материализованные CTE могут ссылаться на другие материализованные CTE. ClickHouse определяет зависимости и материализует их в правильном порядке:

SET enable_materialized_cte = 1;

WITH
    a AS MATERIALIZED (SELECT uid, name FROM users),
    b AS MATERIALIZED (SELECT uid FROM a)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

Порядок определений CTE не имеет значения — допускаются ссылки на определения, идущие ниже:

SET enable_materialized_cte = 1;

WITH
    b AS MATERIALIZED (SELECT uid FROM a),
    a AS MATERIALIZED (SELECT uid FROM users)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

Ограничения

Требуется экспериментальная SETTING: enable_materialized_cte должна быть включена.
Требуется analyzer: материализованные CTE работают только при включенном analyzer (enable_analyzer = 1).
Не поддерживается с RECURSIVE: использование ключевых слов MATERIALIZED и RECURSIVE вместе не допускается и приводит к исключению UNSUPPORTED_METHOD.
Коррелированные CTE запрещены: материализованный CTE не может ссылаться на столбцы из внешних областей видимости запроса.

Общие скалярные выражения

ClickHouse позволяет объявлять псевдонимы для произвольных скалярных выражений в предложении WITH. Общие скалярные выражения могут использоваться в любой части запроса.

Примечание

Если общее скалярное выражение ссылается на что‑то отличное от константного литерала, выражение может привести к появлению свободных переменных. ClickHouse разрешает любой идентификатор в ближайшей возможной области видимости, поэтому свободные переменные могут ссылаться на неожиданные сущности в случае конфликтов имён или привести к коррелированному подзапросу. Рекомендуется определять CSE как лямбда‑функцию (возможно только при включённом analyzer), связывающую все используемые идентификаторы, чтобы добиться более предсказуемого поведения при разрешении идентификаторов в выражениях.

Синтаксис

WITH <expression> AS <identifier>

Примеры

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

WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

Пример 2: Использование функций высшего порядка для ограничения идентификаторов

WITH
    '.txt' as extension,
    (id, extension) -> concat(lower(id), extension) AS gen_name
SELECT gen_name('test', '.sql') as file_name;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

Пример 3: Использование функций высшего порядка со свободными переменными

Следующие примеры запросов показывают, что несвязанные (unbound) идентификаторы разрешаются в сущности из ближайшей области видимости. Здесь идентификатор extension не привязан в теле лямбда-функции gen_name. Хотя extension определён как '.txt' в виде общего скалярного выражения в области определения и использования generated_names, он разрешается в столбец таблицы extension_list, потому что этот столбец доступен в подзапросе generated_names.

CREATE TABLE extension_list
(
    extension String
)
ORDER BY extension
AS SELECT '.sql';

WITH
    '.txt' as extension,
    generated_names as (
        WITH
            (id) -> concat(lower(id), extension) AS gen_name
        SELECT gen_name('test') as file_name FROM extension_list
    )
SELECT file_name FROM generated_names;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

Пример 4: Удаление результата выражения sum(bytes) из списка столбцов в предложении SELECT

WITH sum(bytes) AS s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

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

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

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

WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

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

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

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

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table WHERE number < 100
)
SELECT sum(number) FROM test_table;

┌─sum(number)─┐
│        5050 │
└─────────────┘

Примечание

Рекурсивные 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. Заменить содержимое рабочей таблицы содержимым промежуточной таблицы, затем очистить промежуточную таблицу.

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

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

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

DROP TABLE IF EXISTS tree;
CREATE TABLE tree
(
    id UInt64,
    parent_id Nullable(UInt64),
    data String
) ENGINE = MergeTree ORDER BY id;

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;

┌─id─┬─parent_id─┬─data──────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │
│  1 │         0 │ Child_1   │
│  2 │         0 │ Child_2   │
│  3 │         1 │ Child_1_1 │
└────┴───────────┴───────────┘

Порядок обхода

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

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

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

┌─id─┬─parent_id─┬─data──────┬─path────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │ [0]     │
│  1 │         0 │ Child_1   │ [0,1]   │
│  3 │         1 │ Child_1_1 │ [0,1,3] │
│  2 │         0 │ Child_2   │ [0,2]   │
└────┴───────────┴───────────┴─────────┘

Чтобы создать порядок обхода в ширину, стандартный подход — добавить столбец, который хранит глубину поиска:

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

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path, toUInt64(0) AS depth
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id]), depth + 1
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY depth;

┌─id─┬─link─┬─data──────┬─path────┬─depth─┐
│  0 │ ᴺᵁᴸᴸ │ ROOT      │ [0]     │     0 │
│  1 │    0 │ Child_1   │ [0,1]   │     1 │
│  2 │    0 │ Child_2   │ [0,2]   │     1 │
│  3 │    1 │ Child_1_1 │ [0,1,3] │     2 │
└────┴──────┴───────────┴─────────┴───────┘

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

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

DROP TABLE IF EXISTS graph;
CREATE TABLE graph
(
    from UInt64,
    to UInt64,
    label String
) ENGINE = MergeTree ORDER BY (from, to);

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 from, to, label FROM graph g
    UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

┌─from─┬─to─┬─label──┐
│    1 │  4 │ 1 -> 4 │
│    1 │  2 │ 1 -> 2 │
│    1 │  3 │ 1 -> 3 │
│    2 │  3 │ 2 -> 3 │
│    4 │  5 │ 4 -> 5 │
└──────┴────┴────────┘

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

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

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

Code: 306. DB::Exception: Received from localhost:9000. DB::Exception: Maximum recursive CTE evaluation depth (1000) exceeded, during evaluation of search_graph AS (SELECT from, to, label FROM graph AS g UNION ALL SELECT g.from, g.to, g.label FROM graph AS g, search_graph AS sg WHERE g.from = sg.to). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION)

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

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

WITH RECURSIVE search_graph AS (
    SELECT from, to, label, false AS is_cycle, [tuple(g.from, g.to)] AS path FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label, has(path, tuple(g.from, g.to)), arrayConcat(sg.path, [tuple(g.from, g.to)])
    FROM graph g, search_graph sg
    WHERE g.from = sg.to AND NOT is_cycle
)
SELECT * FROM search_graph WHERE is_cycle ORDER BY from;

┌─from─┬─to─┬─label──┬─is_cycle─┬─path──────────────────────┐
│    1 │  4 │ 1 -> 4 │ true     │ [(1,4),(4,5),(5,1),(1,4)] │
│    4 │  5 │ 4 -> 5 │ true     │ [(4,5),(5,1),(1,4),(4,5)] │
│    5 │  1 │ 5 -> 1 │ true     │ [(5,1),(1,4),(4,5),(5,1)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

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

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

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

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table
)
SELECT sum(number) FROM (SELECT number FROM test_table LIMIT 100);

┌─sum(number)─┐
│        5050 │
└─────────────┘

Общие табличные выражения​

Синтаксис​

Пример​

Материализованные общие табличные выражения​

Синтаксис​

Когда использовать​

Примеры​

Ограничения​

Общие скалярные выражения​

Синтаксис​

Примеры​

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

Порядок обхода​

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

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

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

Синтаксис

Пример

Материализованные общие табличные выражения

Синтаксис

Когда использовать

Примеры

Ограничения

Общие скалярные выражения

Синтаксис

Примеры

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

Порядок обхода

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

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