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 cntFROM (SELECT DISTINCT * FROM Listens) aINNER JOIN (SELECT DISTINCT * FROM Listens) bON a.user_id <> b.user_idAND a.song_id = b.song_idAND a.day = b.day) tLEFT JOIN Friendship t1ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_idLEFT JOIN Friendship t2ON 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) aINNER JOIN (SELECT DISTINCT * FROM Listens) bON a.user_id <> b.user_idAND a.song_id = b.song_idAND 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 cntFROM (SELECT DISTINCT * FROM Listens) aINNER JOIN (SELECT DISTINCT * FROM Listens) bON a.user_id <> b.user_idAND a.song_id = b.song_idAND a.day = b.day) tLEFT JOIN Friendship t1ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_idLEFT JOIN Friendship t2ON 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 cntFROM (SELECT DISTINCT * FROM Listens) aINNER JOIN (SELECT DISTINCT * FROM Listens) bON a.user_id <> b.user_idAND a.song_id = b.song_idAND a.day = b.day) tLEFT JOIN Friendship t1ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_idLEFT JOIN Friendship t2ON 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 cntFROM (SELECT DISTINCT * FROM Listens) aINNER JOIN (SELECT DISTINCT * FROM Listens) bON a.user_id <> b.user_idAND a.song_id = b.song_idAND a.day = b.day) tLEFT JOIN Friendship t1ON t.user1_id = t1.user1_id AND t.user2_id = t1.user2_idLEFT JOIN Friendship t2ON 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;
总结
对于这种想要反向关系,可以对表进行自连接.