Hive SQL必刷练习题：向用户推荐朋友收藏的商品(两种思路)

问题需求：

需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。期望结果如下：

1）部分结果展示

user_id（用户id）	sku_id（应向该用户推荐的商品id）
101	2
101	4
101	7
101	9
101	8
101	11
101	1

2）相关表结构

好友关系表

user1_id（用户1 id）	user2_id（用户2 id）
101	1010
101	108
101	106

收藏信息表

user_id(用户id)	sku_id(用户id)	create_date(收藏日期)
101	3	2021-09-23
101	12	2021-09-23
101	6	2021-09-25

思路分析：

​ 首先明确，这个好友关系表，是互为好友，所以也就是说如果想要找到所有用户的每一个人的所有朋友，需要看用户1 id的朋友 + 用户2 id的朋友。这个就需要借助union了，可以(u1,u2) union (u2,u1)

​ 所有人对应的所有朋友这个找到后，还需要找到所有朋友收藏的商品id，这个就直接用上面union后的表第二列和收藏信息表中的id去join一下就行，不过这里需要注意的是，可能同一个用户的几个朋友的收藏商品中会有重复收藏的，这就需要对用户+商品id进行一下去重。而用户自己收藏的商品，就直接用收藏信息表即可。

-- 这是找到了用户朋友收藏的商品id
-- 所以最后两列是用户自己的id,以及他朋友收藏的商品id
select
	distinct
	t1.user_id,
    sku_id
from(
  SELECT
      user1_id user_id,
      user2_id friend_id 
  from friendship_info
  union
  SELECT
      user2_id,
      user1_id 
  from friendship_info
  )t1 
  left join favor_info
  on favor_info.user_id = t1.friend_id

​ 接下来就是关键的，怎么找到朋友收藏，但是自己没有收藏的商品。

​ 这里有两个思路：借助数组，借助join中的****null

​ 数组的思路是说，可以将自己收藏的商品，根据用户id去group by一下，然后使用collect_list，放到一个数组里面，之后和用户-朋友收藏商品id表进行join一下，这样就可以判断一下每个id是不是在自己收藏的商品里面。

​ Join的方法是说，现在有两个表，一个是用户-朋友收藏商品id表，另外一个是用户-自己收藏商品id表，然后我们以第一个表，也就是朋友收藏商品id表为主，进行left join，条件是用户id和商品id一起join，这时候，如果第三列，第四列没有东西，是null，就说明是没有交叉的数据。【比如A left join B的时候，如果左边A表中的元素在B表中没有存在，那么就是null。这时候我们可以通过看哪些元素有null，就知道哪些是A表中存在，但是在B表中不存在的元素】

思路一代码：

select
	user_id,
    sku_id
from(
  select
      distinct
      t1.user_id id,
      sku_id
  from(
    SELECT
        user1_id user_id,
        user2_id friend_id 
    from friendship_info
    union
    SELECT
        user2_id,
        user1_id 
    from friendship_info
  )t1 
  left join favor_info
  on t1.friend_id = favor_info.user_id
)t2  
left join	
(
  select
      user_id,
      collect_list(sku_id) arr
  from favor_info
  group by user_id
)t3
on t2.id = t3.user_id
where !array_contains(arr,sku_id)

思路二代码：

select
distinct 
t1.user_id,
    friend_favor.sku_id
from
(
    select
        user1_id user_id,
        user2_id friend_id
    from friendship_info
    union
    select
        user2_id,
        user1_id
    from friendship_info
)t1
left join 
favor_info friend_favor
on t1.friend_id=friend_favor.user_id
left join 
favor_info user_favor
on t1.user_id=user_favor.user_id
and friend_favor.sku_id=user_favor.sku_id
where user_favor.sku_id is null