如何在ClickHouse中查询节点间的所有可能路径？

由于ClickHouse不支持标准递归CTE，我们可以通过多轮JOIN迭代、数组函数模拟或预计算存储的方式实现需求，以下是具体可行方案：

方案一：多轮JOIN迭代拼接路径 #

适合路径层级有限的场景，通过逐层JOIN拼接路径：

步骤1：获取初始路径（长度为1的边） #

SELECT
    concat(from, ',', to) AS paths,
    toString(id) AS ids
FROM nodes

步骤2：迭代拼接深层路径 #

每一轮JOIN都以上一轮路径的终点作为起点，拼接下一层节点：

-- 二级路径（a→b→d）
SELECT
    concat(n1.paths, ',', n2.to) AS paths,
    concat(n1.ids, ',', toString(n2.id)) AS ids
FROM (
    SELECT concat(from, ',', to) AS paths, to AS current_node, toString(id) AS ids FROM nodes
) n1
JOIN nodes n2 ON n1.current_node = n2.from

-- 三级路径（a→b→d→e）
SELECT
    concat(n1.paths, ',', n2.to) AS paths,
    concat(n1.ids, ',', toString(n2.id)) AS ids
FROM (
    SELECT concat(n1.paths, ',', n2.to) AS paths, n2.to AS current_node, concat(n1.ids, ',', toString(n2.id)) AS ids
    FROM (SELECT concat(from, ',', to) AS paths, to AS current_node, toString(id) AS ids FROM nodes) n1
    JOIN nodes n2 ON n1.current_node = n2.from
) n1
JOIN nodes n2 ON n1.current_node = n2.from

步骤3：合并所有层级结果 #

用UNION ALL将各层级路径合并，得到最终结果：

-- 初始路径
SELECT concat(from, ',', to) AS paths, toString(id) AS ids FROM nodes

UNION ALL

-- 二级路径
SELECT concat(n1.paths, ',', n2.to) AS paths, concat(n1.ids, ',', toString(n2.id)) AS ids
FROM (SELECT concat(from, ',', to) AS paths, to AS current_node, toString(id) AS ids FROM nodes) n1
JOIN nodes n2 ON n1.current_node = n2.from

UNION ALL

-- 三级路径
SELECT concat(n1.paths, ',', n2.to) AS paths, concat(n1.ids, ',', toString(n2.id)) AS ids
FROM (
    SELECT concat(n1.paths, ',', n2.to) AS paths, n2.to AS current_node, concat(n1.ids, ',', toString(n2.id)) AS ids
    FROM (SELECT concat(from, ',', to) AS paths, to AS current_node, toString(id) AS ids FROM nodes) n1
    JOIN nodes n2 ON n1.current_node = n2.from
) n1
JOIN nodes n2 ON n1.current_node = n2.from

方案二：数组函数模拟路径扩展 #

用数组存储路径节点和ID，结合数组函数实现灵活的路径扩展：

WITH
    -- 初始路径数组
    initial AS (SELECT [from, to] AS path_nodes, [id] AS path_ids FROM nodes),
    -- 扩展二级路径
    level1 AS (
        SELECT
            arrayConcat(n.path_nodes, [m.to]) AS path_nodes,
            arrayConcat(n.path_ids, [m.id]) AS path_ids
        FROM initial n
        JOIN nodes m ON n.path_nodes[-1] = m.from
    ),
    -- 扩展三级路径
    level2 AS (
        SELECT
            arrayConcat(n.path_nodes, [m.to]) AS path_nodes,
            arrayConcat(n.path_ids, [m.id]) AS path_ids
        FROM level1 n
        JOIN nodes m ON n.path_nodes[-1] = m.from
    )

-- 合并所有层级并格式化输出
SELECT
    arrayStringConcat(path_nodes, ',') AS paths,
    arrayStringConcat(arrayMap(x -> toString(x), path_ids), ',') AS ids
FROM initial

UNION ALL

SELECT
    arrayStringConcat(path_nodes, ',') AS paths,
    arrayStringConcat(arrayMap(x -> toString(x), path_ids), ',') AS ids
FROM level1

UNION ALL

SELECT
    arrayStringConcat(path_nodes, ',') AS paths,
    arrayStringConcat(arrayMap(x -> toString(x), path_ids), ',') AS ids
FROM level2

方案三：预计算路径存储（适合静态数据） #

如果节点数据更新不频繁，可以通过定时任务预先计算所有路径，将结果写入一张专用表：

-- 创建结果表
CREATE TABLE IF NOT EXISTS node_paths (
    paths String,
    ids String
) ENGINE = MergeTree()
PRIMARY KEY paths;

-- 定期执行路径计算并写入（可通过ClickHouse任务或外部脚本）
INSERT INTO node_paths
-- 此处填入方案一或方案二的完整查询语句

查询时直接读取node_paths表即可，性能最优。

注意事项 #

• 若存在循环路径（如a→b→a），需在逻辑中加入循环检测（比如用arrayExists判断当前节点是否已在路径中），避免无限迭代。
• 对于层级极深的场景，可借助Python UDF实现递归逻辑，或用外部工具（如Spark）预处理路径后导入ClickHouse。

内容的提问来源于stack exchange，提问作者gs8282