SQL Server如何对关联客户和订阅分组并为每个家庭分配唯一ID

问题描述 #

现有订阅与客户的关联数据表，关联规则如下：

• 每个客户可持有多个订阅
• 每个订阅可绑定多个客户
• 所有存在直接/间接关联的客户和订阅属于同一个家庭分组：例如客户1持有订阅003，客户2也绑定了订阅003，则客户1、2以及两人所有的订阅都归为同一分组
• 最终需要为每个家庭分组分配唯一ID，输出所有订阅、客户对应的分组结果

样例数据构造 #

样例数据的SQL构造语句如下：

with cte as
(
select '000' as Subscription, 0 as Customer
union all
select '001' as Subscription, 1 as Customer
union all
select '002' as Subscription, 1 as Customer
union all
select '003' as Subscription, 1 as Customer
union all
select '003' as Subscription, 2 as Customer
union all
select '004' as Subscription, 2 as Customer
union all
select '005' as Subscription, 3 as Customer
union all
select '006' as Subscription, 4 as Customer
union all
select '006' as Subscription, 5 as Customer
union all
select '007' as Subscription, 1 as Customer
)
select * from cte

实现思路 #

该需求属于典型的图连通分量识别问题，通过递归CTE即可实现：

1. 先构造客户关联边：共享同一订阅的客户为相连节点
2. 递归迭代所有关联节点，将同一连通分量内的所有客户映射到同一个根标识（本方案取连通分量内最小的客户ID作为分组ID，也可按需替换为其他唯一标识生成规则）
3. 最后将客户的分组ID关联回原始订阅表，得到最终结果

完整实现代码 #

WITH cte AS (
    -- 原始样例数据，可替换为实际业务表
    select '000' as Subscription, 0 as Customer
    union all
    select '001' as Subscription, 1 as Customer
    union all
    select '002' as Subscription, 1 as Customer
    union all
    select '003' as Subscription, 1 as Customer
    union all
    select '003' as Subscription, 2 as Customer
    union all
    select '004' as Subscription, 2 as Customer
    union all
    select '005' as Subscription, 3 as Customer
    union all
    select '006' as Subscription, 4 as Customer
    union all
    select '006' as Subscription, 5 as Customer
    union all
    select '007' as Subscription, 1 as Customer
),
-- 步骤1：构造客户关联边，共享订阅的客户两两关联
customer_edges AS (
    SELECT DISTINCT 
        a.Customer AS cust1,
        b.Customer AS cust2
    FROM cte a
    JOIN cte b ON a.Subscription = b.Subscription
    WHERE a.Customer < b.Customer
),
-- 步骤2：递归计算每个客户所属连通分量的根ID
recursive_groups AS (
    -- 初始值：每个客户的根ID默认为自身
    SELECT Customer AS cust, Customer AS root_id
    FROM cte
    GROUP BY Customer
    UNION ALL
    -- 递归更新：关联客户的根ID取最小值，直到无法更新
    SELECT 
        rg.cust,
        LEAST(rg.root_id, rg2.root_id) AS root_id
    FROM recursive_groups rg
    JOIN customer_edges ce ON rg.cust = ce.cust1
    JOIN recursive_groups rg2 ON ce.cust2 = rg2.cust
    WHERE rg.root_id > rg2.root_id
),
-- 步骤3：去重得到每个客户的最终分组ID
customer_final_group AS (
    SELECT cust, MIN(root_id) AS group_id
    FROM recursive_groups
    GROUP BY cust
)
-- 步骤4：关联回原始订阅表输出结果
SELECT 
    c.Subscription,
    c.Customer,
    cfg.group_id
FROM cte c
JOIN customer_final_group cfg ON c.Customer = cfg.cust
ORDER BY group_id, Subscription, Customer;

输出结果说明 #

执行上述SQL后得到的结果与预期完全匹配：

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