为具有多个重复项的多列创建组 [英] Creating Group for multiple columns with multiple duplicates

问题描述

下表包含会员及其政策的详细信息.如果他们至少有一个共同的政策，我们需要组成一个由 2 个或更多成员组成的小组.

The below table contains details of members and their policies. We need to form a group of 2 or more members if they have at least one policy in common.

Member_ID   Policy
101         X1
103         Y2
104         Z1
101         Y1
102         X1
101         X3
103         Z1
101         X2
102         Y3
105         Y1

要求的结果:

GROUP   Member_ID   Policy
1       101         X1
1       101         X2
1       101         X3
1       101         Y1
1       102         X1
1       102         Y3
1       105         Y1
2       103         Y2
2       103         Z1
2       104         Z1

推荐答案

您所描述的是一种图形行走算法.这很复杂，因为您可能需要遍历多个级别.例如:

What you describe is a graph-walking algorithm. This is complicated because you may need to traverse multiple levels. For instance:

101    X1
102    Y1
102    Y2
103    Y2
103    Z1
104    Z1

这些101和104都在同一个组里，没有什么共同点.

These are all in the same group by 101 and 104 have nothing in common.

递归 CTE 可以处理这个问题.对于这个问题，它看起来像:

Recursive CTEs can handle this. For this problem, it looks like:

with edges as (
      select distinct t.memberid as m1, t2.memberid as m2
      from t join
           t t2
           on t2.policy = t.policy -- and t2.memberid <> t.memberid
     ),
     cte as (
      select m1, m2, convert(varchar(max), concat(',', m1, ',', m2, ',')) as members, 1 as lev
      from edges
      union all
      select cte.m1, e.m2, concat(members, e.m2, ','), lev + 1
      from cte join
           edges e
           on cte.m2 = e.m1
      where members not like concat('%,', e.m2, ',%') 
     )
select m1, dense_rank() over (order by min(m2)) as grouping
from cte
group by m1;

这里是db<>小提琴.

Here is a db<>fiddle.

这篇关于为具有多个重复项的多列创建组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！