WITH 子句

ClickHouse 支持公用表表达式（CTE）、公用标量表达式以及递归查询。

公用表表达式

公用表表达式是具名子查询。在 SELECT 查询中，只要允许使用表表达式的地方，都可以通过名称引用它们。具名子查询可以在当前查询的作用域或其子查询的作用域中通过名称进行引用。

如果 CTE 未被显式定义为物化 (参见物化公用表表达式) ，则 SELECT 查询中对公用表表达式的每一次引用，都会被其定义中的子查询替换。通过在标识符解析过程中隐藏当前 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 和设置 enable_materialized_cte。

语法

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

何时使用

在以下情况下，物化 CTE 的收益最明显：

在一个查询中，同一个 CTE 被多次引用。如果不使用 MATERIALIZED，每次引用都会各自重新执行该子查询。
CTE 包含 generateRandom 这类非确定性函数。物化可确保所有引用看到的是同一份数据。
CTE 涉及高开销计算 (聚合、连接、大范围扫描) ，不应重复执行。

提示

如果物化 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： 使用非确定性函数获得确定性结果

使用 generateRandom 的普通 CTE 在每次引用时都会产生不同的结果。将 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 │
└─────────┘

限制

需要启用实验性设置：必须启用设置 enable_materialized_cte。
需要启用 analyzer：物化 CTE 仅在启用 analyzer 时才有效（enable_analyzer = 1）。
不支持与 RECURSIVE 一起使用：不允许同时使用 MATERIALIZED 和 RECURSIVE 关键字，否则会导致 UNSUPPORTED_METHOD 异常。
禁止使用相关 CTE：物化 CTE 不能引用外层查询作用域中的列。

通用标量表达式

ClickHouse 允许你在 WITH 子句中为任意标量表达式声明别名。通用标量表达式可以在查询中的任意位置被引用。

注意

如果通用标量表达式引用了非常量字面值以外的内容，该表达式可能会导致出现自由变量。 ClickHouse 会在尽可能接近的作用域中解析任何标识符，这意味着在名称冲突的情况下，自由变量可能会引用到意外的实体，或者导致生成相关子查询。建议将 CSE 定义为lambda 函数（仅在启用 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： 在含有自由变量的场景中使用高阶函数

下面的示例查询展示了未绑定标识符会解析为最近作用域中的实体。这里，extension 并没有在 gen_name lambda 函数体中绑定。尽管 extension 在 generated_names 的定义和使用所在作用域中被定义为值为 '.txt' 的普通标量表达式，但它最终会被解析为表 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： 从 SELECT 子句的列列表中移除 sum(bytes) 表达式的结果

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 │
└────┴──────┴───────────┴─────────┴───────┘

循环检测

首先，我们来创建一个图表（graph table）：

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)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

无限查询

如果在外层查询中使用 LIMIT，也可以使用无限递归 CTE 查询：

示例： 无限递归 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 │
└─────────────┘

公用表表达式​

语法​

示例​

物化公用表表达式​

语法​

何时使用​

示例​

限制​

通用标量表达式​

语法​

示例​

递归查询​

搜索顺序​

循环检测​

无限查询​