Snowflake递归查询陷入死循环，求生成匹配值串联结果的方案

测试数据表 #

create or replace table test_case (
col_a string,
col_b string,
match_flag string);

insert into test_case
(col_a, col_b, match_flag)
values
('a', 'b', 'Y'),
('b', 'c', 'Y'),
('c', 'a', 'Y'),
('a', 'z', 'N');

需求说明 #

当match_flag='Y'时，col_a与col_b视为匹配关系。前3行形成闭环匹配：a匹配b、b匹配c、c匹配a。需要编写查询，输出每个原始行对应的所有匹配值串联结果，期望输出如下：

原查询的问题 #

原递归查询陷入死循环的核心原因是：a→b→c→a形成了闭环，递归过程中没有限制重复访问节点的逻辑，导致查询无限循环；同时路径拼接的判断逻辑无法有效终止递归。

修改后的查询语句 #

通过在递归过程中跟踪已访问的节点，打破闭环循环，同时生成完整的连通分量路径：

WITH RECURSIVE match_cte AS (
    -- 基础情况：初始化每个匹配节点的起始路径和已访问集合
    SELECT 
        col_a, 
        col_b, 
        col_a AS start_col,
        ARRAY[col_a, col_b] AS visited,
        CONCAT(col_a, '-', col_b) AS match_path
    FROM test_case
    WHERE match_flag = 'Y'
    
    UNION ALL
    
    -- 递归情况：仅访问未加入过路径的节点，避免循环
    SELECT 
        t.col_a, 
        t.col_b, 
        m.start_col,
        ARRAY_APPEND(m.visited, t.col_b) AS visited,
        CONCAT(m.match_path, '-', t.col_b) AS match_path
    FROM match_cte m
    JOIN test_case t ON m.col_b = t.col_a
    WHERE t.match_flag = 'Y'
      AND NOT ARRAY_CONTAINS(m.visited, t.col_b) -- 关键：跳过已访问的节点
),
-- 获取每个连通分量的最长完整路径
component_paths AS (
    SELECT 
        start_col,
        MAX(match_path) AS full_path -- 最长路径即为完整连通分量
    FROM match_cte
    GROUP BY start_col
)
-- 关联原表输出结果
SELECT 
    t.col_a,
    t.col_b,
    t.match_flag,
    CASE 
        WHEN t.match_flag = 'Y' THEN CONCAT('\'', cp.full_path, '\'')
        ELSE '\'na\''
    END AS output
FROM test_case t
LEFT JOIN component_paths cp ON t.col_a = cp.start_col;

修改说明 #

1. 加入已访问节点跟踪：新增visited数组字段，记录当前路径中已经包含的节点，递归时通过NOT ARRAY_CONTAINS(m.visited, t.col_b)判断，避免重复访问形成的闭环，彻底终止死循环。
2. 简化路径拼接逻辑：直接基于已有的路径拼接新节点，无需模糊匹配判断，逻辑更清晰。
3. 提取完整连通分量：通过分组取每个起始节点对应的最长路径，确保得到所有匹配节点的完整串联结果。

执行上述查询后，输出结果将完全符合预期。

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