欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

怎么用SQL计算可能认识的人

程序员文章站 2023-12-31 18:54:22
...

怎么用SQL计算可能认识的人

用户关系数据可以有很多应用,比如说好友动态PUSH,朋友圈兴趣发现,好友推荐等等。推荐可能认识的人,属于好友推荐的一种。平台应该把沉淀、培养用户关系作为平台的目标之一。当用户在平台上积累了足够的关系网,用户离开平台的沉没成本就变得很大,所以你看现在人们已经离不开微信。
怎么用SQL计算可能认识的人
可能认识的人,主要是基于用户的好友关系计算。假设有用户A,B,C其中AB是朋友,AC也是朋友,那么B和C很大可能也是认识的朋友。这时候向B推荐C或者向C推荐B,他们互相加好友的概率相对就大一些。需要注意一下,这里需要双向关注的强用户关系,单向关注并不在考虑范围之内(计算互相关注的SQL应该怎么写)。

这是一个用SQL就能解决的好问题。假设朋友关系表的名字叫做table_friends,里面有两列user_1user_2,含义是user_1向user_2提出了好友申请,并且申请通过。

先把最终的SQL摆出来:

select 
	a.base_user,
	a.possible_user,
	sum(1) as common_friends_count                                  -- 5、sum
from 
(
	select 
		comm_user,
		base_user,
		possible_user
	from 
	(
		select 
			comm_user,
			collect_set(userid) as possible_user_set                -- 2、collect_set
		from 
		(
			select 
				user_1 as comm_user,
				user_2 as userid
			from table_friends

			union all                                               -- 1、union_all
			select 
				user_2 as comm_user,
				user_1 as userid
			from table_friends
		)a
		group by comm_user
	)a
	lateral view explode(possible_user_set) t1 as base_user         -- 3、explode
	lateral view explode(possible_user_set) t2 as possible_user
)a left join (
	select 
		user_1,
		user_2
	from 
	(
		select 
			user_1,
			user_2
		from table_friends

		union all
		select 
			user_2 as user_1,
			user_1 as user_2
		from table_friends
	)a
	group by user_1, user_2
)b on a.base_user = b.user_1 and a.possible_user = b.user_2
where a.base_user <> a.possible_user and b.user_1 is null           -- 4、filter
group by base_user, possible_user

先说明一下SQL的主要思想:
假设我们有了好友关系(A, B), (A, C),那么
A、第一步先得到数组(B, C)
B、然后展开数组得到 (B, (B, C)), (C, (B, C))
C、然后再展开一次数组得到(B,B), (B,C), (C, B), (C, C)
D、过滤掉相同的项(B,B),(C,C),剩下的就是我们需要的结果,并且统计出现的次数,就是共同好友的个数。这里给B推荐C,有一个共同好友;给C推荐B,有一个共同好友

然后重点介绍几个关键的注释点:

1、union_all

好友关系申请表一般只会记录申请关系,即A向B申请了好友,那么表中只有一条(A, B),而不会存在(B, A)。而我们需要A的好友群,也需要B的好友群,所以这里复制了一份反向的申请关系。当然,如果好友关系表里面本来就是用两条记录来表示好友关系的,那就不需要做一次union all了

2、collect_set 聚合

这里把每个用户的好友们整理在一个集合中,存在possible_user_set里。换句话说就是,把有同一个共同好友的人,都放在一起,成一个Array

3、explode 展开

两次展开好友集合,相当于好友集合自身做了一次笛卡尔积。假设我有N个朋友,展开之后就是N^2条记录。这样就把他们之间所有可能的链接做了出来。

4、filter 过滤

在所有可能的链接中,我们需要去掉自己对自己的链接关系,以及已经是好友的链接关系

5、sum

根据base_user进行聚合求sum(1),就是共同好友的个数了。如果不放心,也可以用count(distinct comm_user)

优化点

第三步展开时,N^2带来的内存压力还是很大的。微信目前最多可以添加5000个好友,上限就是25000000,用户关系上涨两千五百万倍,随便哪个集群都吃不消。所以这里需要考虑只展开一次,膨胀5000倍还是可以考虑的。只展开一次时,数据的格式是(B, (B, C)),这就要求直接操作array,我们需要进行array_remove_element删除自身,还需要进行一次array_minus把已经是好友的元素剔除。这两个操作使用UDF可以轻松完成,这里不再展开。

相关文章
计算互相关注的SQL应该怎么写

相关标签: 数据仓库

上一篇:

下一篇: