SQL中基于IP与Agent_ID生成唯一用户分组标识的方法

我们有一张包含IP和Agent_ID两列的表，需要按照以下规则生成唯一分组标识：

• 同一Agent_ID关联的所有IP必须归为同一组
• 同一IP对应的不同Agent_ID也必须归为同一组
  简言之，所有通过IP或Agent_ID存在直接/间接关联的记录，都需要被划到同一个分组中。

示例输入数据 #

期望输出结果 #

解决方案（适用于支持递归CTE的数据库） #

这个问题本质是寻找连通分量：IP与Agent_ID构成关联关系，所有通过IP或Agent_ID相连的记录属于同一个分量。可以用递归CTE遍历所有关联关系，为每个分量分配唯一标识：

WITH RECURSIVE cte AS (
    -- 初始步骤：提取所有唯一的IP-Agent_ID组合，标记初始节点
    SELECT 
        IP, 
        Agent_ID,
        CONCAT(IP, '-', Agent_ID) AS node,
        CONCAT(IP, '-', Agent_ID) AS root
    FROM (SELECT DISTINCT IP, Agent_ID FROM your_table) t

    UNION ALL

    -- 递归步骤：关联所有共享IP或Agent_ID的节点，将root更新为关联节点中最小的标识
    SELECT 
        c.IP, 
        c.Agent_ID,
        c.node,
        LEAST(c.root, t.root) AS root
    FROM cte c
    JOIN (
        SELECT 
            IP, 
            Agent_ID,
            CONCAT(IP, '-', Agent_ID) AS node,
            CONCAT(IP, '-', Agent_ID) AS root
        FROM (SELECT DISTINCT IP, Agent_ID FROM your_table) t
    ) t 
        ON c.IP = t.IP OR c.Agent_ID = t.Agent_ID
    WHERE c.root > t.root
),
-- 为每个唯一root分配连续的组ID
group_ids AS (
    SELECT DISTINCT root, DENSE_RANK() OVER (ORDER BY root) AS group_id
    FROM cte
)
-- 关联原表与分组ID，输出最终结果
SELECT 
    t.IP,
    t.Agent_ID,
    g.group_id AS `Group`
FROM your_table t
JOIN (SELECT DISTINCT IP, Agent_ID, root FROM cte) c 
    ON t.IP = c.IP AND t.Agent_ID = c.Agent_ID
JOIN group_ids g ON c.root = g.root
ORDER BY t.IP, t.Agent_ID;

逻辑说明 #

1. 递归CTE cte 先获取所有唯一的IP-Agent_ID组合作为初始节点，再递归关联所有有相同IP或Agent_ID的节点，确保同一连通分量的节点共享同一个最小的root标识。
2. group_ids 利用DENSE_RANK()为每个root生成连续的组ID。
3. 最后将原表数据与分组ID关联，得到符合要求的分组结果。

如果使用不支持递归CTE的数据库（如MySQL 5.x），可通过变量或多层自连接实现，但逻辑会更繁琐，递归CTE是最简洁的方案。

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