首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >如何利用SQL求取微信的共同好友数?

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

作者头像
张俊红
发布于 2022-03-03 10:36:40
发布于 2022-03-03 10:36:40
85000
代码可运行
举报
文章被收录于专栏:张俊红张俊红
运行总次数:0
代码可运行

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

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

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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进行自连接,实现代码如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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进行限制,代码如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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即可,实现代码如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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

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

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

对比Excel系列图书累积销量达15w册,让你轻松掌握数据分析技能,感兴趣的同学可以点击下方链接了解:

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-12-28,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 俊红的数据分析之路 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档