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 |