目录
题目
准备数据
分析数据
总结
题目
您正在为一个社交媒体网站实施一个页面推荐系统。如果页面被user_id的 至少一个朋友喜欢 ，而 不被user_id喜欢 ，你的系统将 推荐 一个页面到user_id。
编写一个解决方案来查找针对每个用户的所有可能的 页面建议 。
每个建议应该在结果表中显示为一行，包含以下列:
user_id: 系统向其提出建议的用户的ID。page_id: 推荐为user_id的页面ID。.friends_likes:user_id对应page_id的好友数。
以 任意顺序 返回结果表。
准备数据
Create table If Not Exists Friendship (user1_id int, user2_id int);
Create table If Not Exists Likes (user_id int, page_id int);
Truncate table Friendship;
insert into Friendship (user1_id, user2_id) values ('1', '2');
insert into Friendship (user1_id, user2_id) values ('1', '3');
insert into Friendship (user1_id, user2_id) values ('1', '4');
insert into Friendship (user1_id, user2_id) values ('2', '3');
insert into Friendship (user1_id, user2_id) values ('2', '4');
insert into Friendship (user1_id, user2_id) values ('2', '5');
insert into Friendship (user1_id, user2_id) values ('6', '1');
Truncate table Likes;
insert into Likes (user_id, page_id) values ('1', '88');
insert into Likes (user_id, page_id) values ('2', '23');
insert into Likes (user_id, page_id) values ('3', '24');
insert into Likes (user_id, page_id) values ('4', '56');
insert into Likes (user_id, page_id) values ('5', '11');
insert into Likes (user_id, page_id) values ('6', '33');
insert into Likes (user_id, page_id) values ('2', '77');
insert into Likes (user_id, page_id) values ('3', '77');
insert into Likes (user_id, page_id) values ('6', '88');
friendship表
likes表
分析数据
第一步:使用union all进行拉长.这个子查询生成所有可能的好友对，确保 user1_id 和 user2_id 之间的关系被双向考虑
select user1_id,user2_id
from Friendship
union all
select user2_id,user1_id
from Friendship;
第二步:将两张表进行左连接,便计算每个用户的朋友对特定页面的喜欢次数。
with t1 as (select user1_id,user2_idfrom Friendshipunion allselect user2_id,user1_idfrom Friendship
)selectt1.user1_id as user_id,l.page_id,count(*) friends_likes
from t1 left join likes l on t1.user2_id = l.user_id
group by t1.user1_id,page_id;
第三步:排除用户自己已经喜欢的页面.
with t1 as (select user1_id,user2_idfrom Friendshipunion allselect user2_id,user1_idfrom Friendship
)selectt1.user1_id as user_id,l.page_id,count(*) friends_likes
from t1 left join likes l on t1.user2_id = l.user_id
where not exists (select 1from likeswhere user_id = t1.user1_idand page_id = l.page_id
)
group by t1.user1_id,page_id;其中 ,这个子查询检查是否存在用户自己已经喜欢的页面。如果存在，则不包括在最终结果中。
select 1from likeswhere user_id = t1.user1_idand page_id = l.page_id总结
遇到双向考虑的情况,使用union all进行连接
