基于legal_identifier的客户集群分组：MySQL实现方案求助

要实现全连通客户集群的划分，我们可以利用递归CTE遍历所有关联关系，结合集群根节点标记的方式来实现。以下是可直接运行的SQL代码：

WITH RECURSIVE customer_connections AS (
    -- 初始步骤：每个客户自身作为连通起点，记录已访问的客户ID（用逗号包裹避免匹配错误）
    SELECT 
        cm.id AS start_cust_id,
        cm.id AS connected_cust_id,
        CONCAT(',', cm.id, ',') AS visited_ids
    FROM customer_master cm

    UNION ALL

    -- 递归步骤：通过共享的legal_identifier拓展连通范围，跳过已访问的客户避免循环
    SELECT 
        cc.start_cust_id,
        cm.id AS connected_cust_id,
        CONCAT(cc.visited_ids, cm.id, ',') AS visited_ids
    FROM customer_connections cc
    -- 关联当前客户的所有legal标识符
    JOIN customer_details cd1 ON cd1.cust_id = cc.connected_cust_id
    -- 找到共享同一标识符的其他客户
    JOIN customer_details cd2 
        ON cd2.legal_identifier = cd1.legal_identifier 
        AND cd2.cust_id != cc.connected_cust_id
    JOIN customer_master cm ON cm.id = cd2.cust_id
    -- 确保该客户未被遍历过
    WHERE cc.visited_ids NOT LIKE CONCAT('%,', cm.id, ',%')
),
-- 为每个客户标记其所属集群的根节点（取集群中最小的客户ID作为唯一标识）
customer_clusters AS (
    SELECT 
        start_cust_id,
        MIN(connected_cust_id) AS cluster_root
    FROM customer_connections
    GROUP BY start_cust_id
)
-- 按集群根节点聚合，生成去重且有序的客户ID列表
SELECT 
    GROUP_CONCAT(DISTINCT cm.id ORDER BY cm.id) AS cluster
FROM customer_master cm
JOIN customer_clusters cc ON cm.id = cc.start_cust_id
GROUP BY cc.cluster_root
ORDER BY cluster;

代码说明 #

1. 递归遍历关联关系：

   • 初始阶段为每个客户创建一条自连通记录，并用visited_ids字符串跟踪已遍历的客户，避免递归时出现循环。
   • 递归阶段通过customer_details表的legal_identifier关联，不断拓展连通的客户范围，直到没有新的客户可以加入。

2. 标记集群根节点：
   同一集群内的所有客户最终会遍历到彼此，我们取集群中最小的客户ID作为集群的唯一根节点，确保同一集群的客户共享同一个根标识。

3. 聚合集群结果：
   按根节点分组，用GROUP_CONCAT聚合所有属于该集群的客户ID，去重并排序后得到最终的集群列表。

原代码问题分析 #

你之前的递归CTE存在两个核心问题：

• 仅处理了直接关联的客户，没有递归遍历所有间接关联的节点，导致无法形成完整集群。
• 使用cm2.id NOT IN (SELECT GROUP_CONCAT(cte1.connected_id))的方式判断已访问客户是错误的：NOT IN不能识别逗号分隔的字符串，需要用字符串匹配或集合跟踪的方式避免循环。

性能优化建议 #

• 为customer_details表的cust_id和legal_identifier字段建立索引：

  CREATE INDEX idx_cust_details_cust_id ON customer_details(cust_id);
  CREATE INDEX idx_cust_details_legal_id ON customer_details(legal_identifier);
  

• 如果数据量极大，实时递归查询性能不足，可以考虑维护一张独立的customer_cluster表，通过触发器或定时任务在数据增删改时更新集群信息，确保查询效率。

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