SQL问题修正：计算v1/v2关联用户组的target_variable最大值

原SQL的核心问题是用字符串拼接匹配的方式无法识别跨多步的连通关系，导致user_id4、5所在的连通分支没有和1-3-6合并。要正确处理这种多步关联的连通组，应该用递归CTE来构建完整的连通分量。

以下是修正后的SQL代码：

WITH a1 AS (
    SELECT 1 AS user_id, 'a' AS v1, 'x' AS v2, 1 AS target_variable
    UNION ALL SELECT 2, 'a', 'y', 2
    UNION ALL SELECT 3, 'b', 'y', 3
    UNION ALL SELECT 4, 'b', 'z', 4
    UNION ALL SELECT 5, 'c', 'z', 5
    UNION ALL SELECT 6, 'd', 'x', 6
    UNION ALL SELECT 7, 'f', 'w', 7
),
-- 提取所有唯一的节点（v1和v2的所有值）
nodes AS (
    SELECT v1 AS node FROM a1
    UNION SELECT v2 AS node FROM a1
),
-- 递归找出所有连通分量，用根节点标识每个组
connected_components AS (
    SELECT 
        node AS root_node,
        node AS current_node
    FROM nodes
    UNION ALL
    SELECT 
        cc.root_node,
        CASE WHEN a.v1 = cc.current_node THEN a.v2 ELSE a.v1 END AS current_node
    FROM connected_components cc
    JOIN a1 a ON a.v1 = cc.current_node OR a.v2 = cc.current_node
    WHERE CASE WHEN a.v1 = cc.current_node THEN a.v2 ELSE a.v1 END NOT IN (SELECT current_node FROM connected_components WHERE root_node = cc.root_node)
),
-- 去重每个连通组的节点，保留根节点和对应节点
unique_components AS (
    SELECT DISTINCT root_node, current_node FROM connected_components
),
-- 关联用户到对应的连通组
user_component AS (
    SELECT 
        a1.user_id,
        uc.root_node,
        a1.target_variable
    FROM a1
    JOIN unique_components uc ON a1.v1 = uc.current_node OR a1.v2 = uc.current_node
),
-- 计算每个连通组的最大target值
component_max_target AS (
    SELECT root_node, MAX(target_variable) AS max_target FROM user_component GROUP BY root_node
)
-- 关联用户和对应组的最大target
SELECT 
    uc.user_id,
    cmt.max_target AS target_variable
FROM user_component uc
JOIN component_max_target cmt ON uc.root_node = cmt.root_node
GROUP BY uc.user_id, cmt.max_target
ORDER BY uc.user_id;

代码说明： #

1. nodes：提取所有v1和v2的唯一值作为连通分析的节点。
2. connected_components：递归遍历所有节点，把通过v1/v2关联的节点归到同一个根节点下，完整识别所有跨多步的连通关系。
3. unique_components：去重连通组的节点记录，避免重复计算。
4. user_component：把每个用户和其v1/v2所在的连通组关联起来。
5. component_max_target：计算每个连通组内的target_variable最大值。
6. 最后关联用户和组的最大值，得到每个用户的结果。

运行后得到的正确结果：

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