⭐️⭐️⭐️⭐️⭐️互相关注&可能认识的人

需求七:互相关注&可能认识的人

需求一：互相关注的人

• 用户好友关系是一个产品的核心数据，只允许互相关注的用户之间发消息称为强关系型产品，比如微信；反之，不互相关注也能看到动态，比如微博，就是弱关系型产品
• 因为微信的存在，现在基本能做大的都是社区型的，弱关系型的产品了。所以互联网公司就很容易碰到，从单向关注数据中计算是否双向关注这种需求。
• 假设现在有一张表，叫table_relation里面只有两个字段，from_user，to_user, 代表关注关系从from指向to，即from_user关注了to_user。
• 求互相关注

解决

• 方式一：自关联
• 缺点：当用户量到了亿级别，关注关系到了百亿级别，join起来的效率就会很低。

select 
  a.from_user,
  a.to_user,
  if(b.from_user is not null, 1, 0) as is_friend
from table_relation a 
left join table_relation b
on a.from_user = b.to_user
	 and a.to_user = b.from_user

• 方式二：找特征相同
• 假设按照字典顺序做一次排序，那么排序后的结果都是(A, B), (A, B)
• 这样把特征相同的数据分到一组，计算组里面的数据条数，为1则是单向关注，为2则是双向关注。
• 这里没有考虑数据重复的情况，假设有两条(A,B)(A,B)，那结果就错了，不过这种数据存在说明了数仓建设的失败。如果真有，那就先去重一次即可。
• 这里也没有考虑用户id是非string数据类型的情况，不过一般都能转成string。
• 最后，不一定非要排序做字符串，能计算出共同特点就行。比如用hash函数也没问题。

select 
  a.from_user,
  a.to_user,
  if( sum(1) over (partition by feature) > 1, 1, 0) as is_friend
from 
(
  select 
    a.from_user,
    a.to_user,
    if(from_user > to_user, concat(to_user, from_user), concat(from_user, to_user)) as feature
  from table_relation 
)a

需求二：可能认识的人

• 可能认识的人，主要是基于用户的好友关系计算。
• 假设有用户A,B,C其中AB是朋友，AC也是朋友，那么B和C很大可能也是认识的朋友。
• 这时候向B推荐C或者向C推荐B，他们互相加好友的概率相对就大一些。
• 假设朋友关系表的名字叫做table_friends，里面有两列user_1，user_2，含义是user_1向user_2提出了好友申请，并且申请通过。
• 思路
  • 假设我们有了好友关系(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，有一个共同好友

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

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

• 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可以轻松完成，这里不再展开。