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

2021-12-28 00:00:00 用户 专区 代码 好友 任意

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

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

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

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

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

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


这就是我们想要的任意两个用户之间的共同好友数了。

原文链接:https://mp.weixin.qq.com/s/hbHJ15yIWqeFHZuwkDLrVA


相关文章