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