Hive面试-求互为好友的用户明细

已知用户好友表user_friend_table

user_id	friend_list
A	B,C,D
B	A,B,D
....	.....

求互为共同好友一共有多少对。

思路解析：

1、首先将好友列表拆开，得到如下形式的数据

uid          friend

A          B

A          C

2、然后分别比较uid和friend_id，拼接uid和friend，得出判断标签new_tag。

3、按照new_tag聚合，取count(1) = 2即可

代码实现：

with 
cte_a as (
    select uid, friend_id
    from user_friend_tables 
    lateral  view  explode(split(friend_list,','))  b AS friend_id
),
cte_b as (
  select 
  *, 
  if(uid > friend_id, concat(uid, friend_id), concat(friend_id, uid)) as new_tag
  from cte_a
)
select count(1)
from (
select new_tag
from cte_b 
group by new_tag
having  count(1) = 2
)  a