HiveSQL 面试题 - 向用户推荐朋友收藏的商品

1 需求

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

• 好友关系表 friendship_info

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

• 收藏表 favor_info

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

• 期望结果

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

2 实现

• 思路

初看这个需求还是有些绕的，而且实现过程中需要注意很多细节否则就会导致出错。

主思路是统计出每个用户自己收藏的商品和自己的朋友收藏的商品，然后从中取出朋友收藏的且自己未收藏的商品，用来给自己推荐。需要注意好友关系表中的关系是双向的，与收藏表进行关联时，关联字段的不同拿出来的数据也是不同的。

• 实现

1 查出每个人的朋友都收藏了哪些商品，注意关联字段 user2_id，表示查询朋友收藏的商品

SELECT *
from friendship_info
     inner join
     favor_info
     on friendship_info.user2_id = favor_info.user_id

查询部分数据如下，其中 sku_id 字段为朋友 user2_id 收藏的商品

user1_id	user2_id	user_id	sku_id	create_date
101	102	102	1	2021-09-24
101	102	102	2	2021-09-24
101	102	102	8	2021-09-23
101	102	102	12	2021-09-22
101	102	102	11	2021-09-23
101	102	102	9	2021-09-25
101	102	102	4	2021-09-25
101	102	102	6	2021-09-23
101	102	102	7	2021-09-26
101	104	104	9	2021-09-28
101	104	104	7	2021-09-28
101	104	104	8	2021-09-25
101	104	104	3	2021-09-28
101	104	104	11	2021-09-25
101	104	104	6	2021-09-25
101	104	104	12	2021-09-28
101	106	106	12	2021-10-08
101	106	106	1	2021-10-08
101	106	106	4	2021-10-04
101	106	106	5	2021-10-08
101	106	106	2	2021-10-04
101	106	106	6	2021-10-04
101	106	106	7	2021-10-08
101	108	108	9	2021-10-08
101	108	108	3	2021-10-10
101	108	108	8	2021-10-10
101	108	108	10	2021-10-07
101	108	108	11	2021-10-07
101	1010	1010	2	2021-09-29
101	1010	1010	9	2021-09-29
101	1010	1010	1	2021-10-01

2 对结果 1 进行简化，因为某个用户的多个朋友可能会收藏相同的商品，以下 SQL 查出来某个用户的所有的朋友的收藏信息

SELECT user1_id,
       sku_id sku_id_friend
from friendship_info
     inner join favor_info on friendship_info.user2_id = favor_info.user_id
group by user1_id, sku_id

user1_id	sku_id_friend
101	1
101	10
101	11
101	12
101	2
101	3
101	4
101	5
101	6
101	7
101	8
101	9

3 找出每个人自己都收藏了哪些商品，为了方便比较，这里将每个人自己的收藏使用 collect_list(sku_id) 收集到集合中

select user_id, collect_list(sku_id) sku_id_self
from favor_info
group by user_id

user_id	sku_id_self
101	[“3”,“12”,“6”,“10”,“5”]
1010	[“2”,“9”,“1”]
102	[“1”,“2”,“8”,“12”,“11”,“9”,“4”,“6”,“7”]
103	[“8”,“5”,“6”,“12”,“7”,“10”,“4”,“11”,“3”]
104	[“9”,“7”,“8”,“3”,“11”,“6”,“12”]
105	[“8”,“9”,“7”,“11”,“5”,“4”,“10”]
106	[“12”,“1”,“4”,“5”,“2”,“6”,“7”]

4 关联两份数据，使用 !array_contains(sku_id_self, sku_id_friend) 从中筛选出最终结果

select user_id, sku_id_friend sku_id
from (SELECT user1_id, sku_id sku_id_friend
      from friendship_info
           inner join favor_info on friendship_info.user2_id = favor_info.user_id
      group by user1_id, sku_id) t1
     right join (select user_id, collect_list(sku_id) sku_id_self from favor_info group by user_id) t2
                on t1.user1_id = t2.user_id
where !array_contains(sku_id_self, sku_id_friend)

user_id	sku_id
101	1
101	9
101	8
101	7
101	4
101	2
101	11