力扣题
1、题目地址
2、模拟表
表:Friendship
Column Name | Type |
---|---|
user1_id | int |
user2_id | int |
- (user1_id,user2_id) 是 Friendship 表的主键(具有唯一值的列的组合)。
- 该表的每一行表示用户user1_id和user2_id是好友。
表:Likes
Column Name | Type |
---|---|
user_id | int |
page_id | int |
- (user_id,page_id) 是 Likes 表的主键(具有唯一值的列)。
- 该表的每一行表示user_id喜欢page_id。
3、要求
您正在为一个社交媒体网站实施一个页面推荐系统。
如果页面被user_id的 至少一个朋友喜欢 ,而 不被user_id喜欢 ,你的系统将 推荐 一个页面到user_id。
编写一个解决方案来查找针对每个用户的所有可能的 页面建议 。
每个建议应该在结果表中显示为一行,包含以下列:
user_id:系统向其提出建议的用户的ID。
page_id:推荐为 user_id 的页面ID。.
friends_likes:user_id 对应 page_id 的好友数。
以 任意顺序 返回结果表。
4、示例
输入:
Friendship 表:
user1_id | user2_id |
---|---|
1 | 2 |
1 | 3 |
1 | 4 |
2 | 3 |
2 | 4 |
2 | 5 |
6 | 1 |
Likes 表:
user_id | page_id |
---|---|
1 | 88 |
2 | 23 |
3 | 24 |
4 | 56 |
5 | 11 |
6 | 33 |
2 | 77 |
3 | 77 |
6 | 88 |
输出:
user_id | page_id | friends_likes |
---|---|---|
1 | 77 | 2 |
1 | 23 | 1 |
1 | 24 | 1 |
1 | 56 | 1 |
1 | 33 | 1 |
2 | 24 | 1 |
2 | 56 | 1 |
2 | 11 | 1 |
2 | 88 | 1 |
3 | 88 | 1 |
3 | 23 | 1 |
4 | 88 | 1 |
4 | 77 | 1 |
4 | 23 | 1 |
5 | 77 | 1 |
5 | 23 | 1 |
解释:
以 用户1 为例:
- 用户1 是 用户 2、3、4、6 的好友。
- 推荐页面有 23(用户2喜欢),24(用户3喜欢),56(用户3喜欢),33(用户6喜欢),77(用户2和用户3喜欢)。
- 请注意,第 88 页不推荐,因为 用户1 已经喜欢它。
另一个例子是 用户 6:
- 用户6 是 用户1 的好友。
- 用户1 只喜欢了 88 页,但 用户6 已经喜欢了。因此,用户6 没有推荐。
您可以使用类似的过程为 用户 2、3、4 和 5 推荐页面。
代码编写
网友代码
1、tmp 表找出所有的朋友关系对,tmp 跟 Likes one 用 two.user_id 和 one.user_id 相关联,那么 one.friend_id 就是被推荐的用户。
2、这时候我们需要排除 two.friend_id 已经 Likes 过的推荐。这里比较自然而然想到的方式是 (friend_id, page_id) not in (select user_id,page_id from Likes) ,但是会发现这个效率太慢了,在 MySQL 下通过不了,所以转而使用表连接的方式。
3、我们再次用 Likes three 跟 tmp two 表做关联,令 three.page_id = one.page_id,这时候会发现,two.friend_id 没有 Likes 过的推荐全部变成了 null。(这一点很关键,要多想)
4、那么最后把 null 的记录排除,再做一个分组聚合即可。
with tmp as(
select user1_id AS user_id, user2_id AS friend_id from Friendship
union all
select user2_id, user1_id from Friendship
)
select two.friend_id AS user_id,
one.page_id,
count(1) friends_likes
from Likes one
left join tmp two on one.user_id = two.user_id
left join Likes three on three.user_id = two.friend_id and three.page_id = one.page_id
where three.page_id is null
group by 1, 2
order by 1, 3 desc, 2