SQL进阶技巧：如何分析共同好友问题？

目录

0 需求

1 数据准备

2 数据分析

3 小结

---

0 需求

给定每个用户的好友列表，好友关系是互相对称的，返回任意两个用户的共同好友列表

1 数据准备

 with common_friend as(
select 'A' as id , 'B,C,D' as friends  union all
select 'B' as id , 'A,C,E' as friends  union all
select 'C' as id , 'A,B,D,E,F' as friends  union all
select 'D' as id , 'A,C,F' as friends  union all
select 'E' as id , 'B,C' as friends  union all
select 'F' as id , 'C,D' as friends  
 )

2 数据分析

第一步：创建临时表，将好友关系分解为最细粒度

 with common_friend as(
select 'A' as id , 'B,C,D' as friends  union all
select 'B' as id , 'A,C,E' as friends  union all
select 'C' as id , 'A,B,D,E,F'