MySQL高阶1917-Leetcodify好友推荐
目录
题目
为 Leetcodify 用户推荐好友。我们将符合下列条件的用户 x 推荐给用户 y ：
- 用户
x和y不是好友，且 - 用户
x和y在同一天收听了相同的三首或更多不同歌曲。
注意，好友推荐是单向的，这意味着如果用户 x 和用户 y 需要互相推荐给对方，结果表需要将用户 x 推荐给用户 y 并将用户 y 推荐给用户 x。另外，结果表不得出现重复项（即，用户 y 不可多次推荐给用户 x ）。
按任意顺序返回结果表。
准备数据
Create table If Not Exists Listens (user_id int, song_id int, day date);
Create table If Not Exists Friendship (user1_id int, user2_id int);
Truncate table Listens;
insert into Listens (user_id, song_id, day) values ('1', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('1', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('1', '12', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('2', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('2', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('2', '12', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('3', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('3', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('3', '12', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('4', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('4', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('4', '13', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('5', '10', '2021-03-16');
insert into Listens (user_id, song_id, day) values ('5', '11', '2021-03-16');
insert into Listens (user_id, song_id, day) values ('5', '12', '2021-03-16');
Truncate table Friendship;
insert into Friendship (user1_id, user2_id) values ('1', '2');
listens表
friendship表
分析数据
SELECT DISTINCT t.user1_id AS user_id,t.user2_id AS recommended_id
FROM
(SELECT a.user_id AS user1_id
,b.user_id AS user2_id
,a.song_id
,a.day
,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt
FROM (SELECT DISTINCT * FROM Listens) a
INNER JOIN (SELECT DISTINCT * FROM Listens) b
ON a.user_id <> b.user_id
AND a.song_id = b.song_id
AND a.day = b.day) t
LEFT JOIN Friendship t1
ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_id
LEFT JOIN Friendship t2
ON t.user1_id = t2.user2_id AND t.user2_id = t2.user1_id
WHERE t.cnt >= 3 AND t1.user1_id IS NULL AND t2.user1_id IS NULL;
其中
分析一:对listens自连接,让user_id不相等,song_id和day相等,最后利用开窗函数,统计出个数
SELECT a.user_id AS user1_id ,b.user_id AS user2_id ,a.song_id ,a.day ,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt FROM (SELECT DISTINCT * FROM Listens) a INNER JOIN (SELECT DISTINCT * FROM Listens) b ON a.user_id <> b.user_id AND a.song_id = b.song_id AND a.day = b.day;
分析二:再将得到的表和friendship 进行两次左连接,第一次是查找已经存在友谊关系的用户组;第二次查找反向的友谊关系
SELECT * FROM (SELECT a.user_id AS user1_id ,b.user_id AS user2_id ,a.song_id ,a.day ,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt FROM (SELECT DISTINCT * FROM Listens) a INNER JOIN (SELECT DISTINCT * FROM Listens) b ON a.user_id <> b.user_id AND a.song_id = b.song_id AND a.day = b.day) t LEFT JOIN Friendship t1 ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_id LEFT JOIN Friendship t2 ON t.user1_id = t2.user2_id AND t.user2_id = t2.user1_id;
分析三:只保留那些共同听过至少3次相同歌曲的用户组,并且在friendship表中没记录的
SELECT * FROM (SELECT a.user_id AS user1_id ,b.user_id AS user2_id ,a.song_id ,a.day ,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt FROM (SELECT DISTINCT * FROM Listens) a INNER JOIN (SELECT DISTINCT * FROM Listens) b ON a.user_id <> b.user_id AND a.song_id = b.song_id AND a.day = b.day) t LEFT JOIN Friendship t1 ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_id LEFT JOIN Friendship t2 ON t.user1_id = t2.user2_id AND t.user2_id = t2.user1_id WHERE t.cnt >= 3 AND t1.user1_id IS NULL AND t2.user1_id IS NULL;
分析四:去重,选择唯一的用户组
SELECT DISTINCT t.user1_id AS user_id,t.user2_id AS recommended_id FROM (SELECT a.user_id AS user1_id ,b.user_id AS user2_id ,a.song_id ,a.day ,COUNT(a.song_id) OVER (PARTITION BY a.day,a.user_id,b.user_id) AS cnt FROM (SELECT DISTINCT * FROM Listens) a INNER JOIN (SELECT DISTINCT * FROM Listens) b ON a.user_id <> b.user_id AND a.song_id = b.song_id AND a.day = b.day) t LEFT JOIN Friendship t1 ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_id LEFT JOIN Friendship t2 ON t.user1_id = t2.user2_id AND t.user2_id = t2.user1_id WHERE t.cnt >= 3 AND t1.user1_id IS NULL AND t2.user1_id IS NULL;
总结
对于这种想要反向关系,可以对表进行自连接.
原文地址：https://blog.csdn.net/weixin_58305115/article/details/142432727
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！