如何利用SQL求取微信的共同好友数？

2021-12-28 14:43:30

「哎呀，我们竟然有共同好友」

「哎呀，没想到你们也认识」

经常在朋友圈评论区能够看到类似的评论，这些评论反映的其实就是共同好友这个概念。那如果你是负责微信的数据分析师，现在业务方想看下微信中任意两个人之间有多少个共同好友，应该怎么看呢？

我们先创建一个模拟数据表，创建代码如下：

create table weixin_friends 
(uid bigint,
tuid bigint);

insert into weixin_friends (uid,tuid) 
values 
(100,200),
(100,300),
(100,400),
(200,100),
(200,300),
(200,400),
(300,100),
(300,200),
(400,100)

通过上述代码就创建了好友关系表weixin_friends，该表的详细(模拟)数据如下：

uid	tuid
100	200
100	300
100	400
200	100
200	300
200	400
300	100
300	200
400	100

uid表示每个用户在微信后台的用户id，tuid表示uid对应的微信好友id，现在我们要通过这张表查询出任意两个人之间的共同好友数，应该怎么做呢？

想一下，如果现在需要我们人工找出上表中任意两个人的共同好友的话，你会怎么做呢？

我会先从tuid着手，看下每一个tuid是哪些人的共同好友，也就是tuid链接了哪些人？

tuid	好友1	好友2
200	100	300
300	100	200
400	100	200
100	200	300
100	200	400
100	300	400

如果我们能整理出上表这样的话，任意两个人的共同好友数就一目了然了，只需要按照好友1、好友2同时group by，然后count(tuid)就可以得到任意两个用户的共同好友数了。

那我们怎么样才可以得到上面这张的表呢？那就是把表weixin_friends通过tuid进行自连接，实现代码如下：

select 
 t1.uid t1_uid
 ,t1.tuid t1_tuid
 ,t2.uid t2_uid
 ,t2.tuid t2_tuid
from 
 (select uid,tuid from weixin_friends) t1 
join 
 (select uid,tuid from weixin_friends) t2 
on t1.tuid = t2.tuid

运行上面代码会得到如下结果：

上面结果存在两个问题，一个是自己和自己是共同好友(红框圈出来的部分)，即t1_uid = t2_uid，另外一个就是AB和BA是两条记录(绿框圈出来的部分)，实际上背后是相同的两个人。为了解决这两个问题，我们就需要对t1_uid和t2_tuid进行限制，代码如下：

select 
 t1.uid t1_uid
 ,t1.tuid t1_tuid
 ,t2.uid t2_uid
 ,t2.tuid t2_tuid
from 
 (select uid,tuid from weixin_friends) t1 
join 
 (select uid,tuid from weixin_friends) t2 
on t1.tuid = t2.tuid and t1.uid < t2.uid

运行上面代码会得到如下结果：

然后再针对这个表中的t1_uid、t2_uid同时进行group by即可，实现代码如下：

select 
 t1_uid
 ,t2_uid
 ,count(t1_tuid) cnt
from 
 (select 
  t1.uid t1_uid
  ,t1.tuid t1_tuid
  ,t2.uid t2_uid
  ,t2.tuid t2_tuid
 from 
  (select uid,tuid from weixin_friends) t1 
 join 
  (select uid,tuid from weixin_friends) t2 
 on t1.tuid = t2.tuid and t1.uid < t2.uid
 )t 
group by 
 t1_uid
 ,t2_uid