1919 兴趣相同的朋友
SQL架构
Create table If Not Exists Listens_1919 (user_id int, song_id int, day date);
Create table If Not Exists Friendship_1919 (user1_id int, user2_id int);
Truncate table Listens_1919;
insert into Listens_1919 (user_id, song_id, day) values ('1', '10', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('1', '11', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('1', '12', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('2', '10', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('2', '11', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('2', '12', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('3', '10', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('3', '11', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('3', '12', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('4', '10', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('4', '11', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('4', '13', '2021-03-15');
insert into Listens_1919 (user_id, song_id, day) values ('5', '10', '2021-03-16');
insert into Listens_1919 (user_id, song_id, day) values ('5', '11', '2021-03-16');
insert into Listens_1919 (user_id, song_id, day) values ('5', '12', '2021-03-16');
Truncate table Friendship_1919;
insert into Friendship_1919 (user1_id, user2_id) values ('1', '2');
insert into Friendship_1919 (user1_id, user2_id) values ('2', '4');
insert into Friendship_1919 (user1_id, user2_id) values ('2', '5');
Table: Listens
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| song_id | int |
| day | date |
+-------------+---------+
该表没有主键,因此会存在重复的行。
该表的每一行所代表的含义是:用户(user_id)在某天(day)听了某首歌曲(song_id)。
Table: Friendship
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
(user1_id, user2_id) 是该表的主键。
该表的每一行所代表的含义是,用户(user1_id, user2_id)是朋友。
注意:user1_id < user2_id。
请写一段SQL查询获取到兴趣相同的朋友。用户 x 和 用户 y 是兴趣相同的朋友,需满足下述条件:
用户 x 和 y 是朋友,并且
用户 x and y 在同一天内听过相同的歌曲,且数量大于等于三首.
结果表无需排序。注意:返回的结果需要和源数据表的呈现方式相同 (例如, 需满足 user1_id < user2_id)。
结果表的格式如下例:
Listens table:
+---------+---------+------------+
| user_id | song_id | day |
+---------+---------+------------+
| 1 | 10 | 2021-03-15 |
| 1 | 11 | 2021-03-15 |
| 1 | 12 | 2021-03-15 |
| 2 | 10 | 2021-03-15 |
| 2 | 11 | 2021-03-15 |
| 2 | 12 | 2021-03-15 |
| 3 | 10 | 2021-03-15 |
| 3 | 11 | 2021-03-15 |
| 3 | 12 | 2021-03-15 |
| 4 | 10 | 2021-03-15 |
| 4 | 11 | 2021-03-15 |
| 4 | 13 | 2021-03-15 |
| 5 | 10 | 2021-03-16 |
| 5 | 11 | 2021-03-16 |
| 5 | 12 | 2021-03-16 |
+---------+---------+------------+
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 2 | 4 |
| 2 | 5 |
+----------+----------+
Result table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
用户 1 和 2 是朋友, 并且他们在同一天内都听了10、11、12的歌曲。所以,他们是兴趣相同的朋友。
用户 1 和 3 在同一天内都听了10、11、12的歌曲,但他们不是朋友。
用户 2 和 4 是朋友,但他们同一天内听过相同的歌曲的数量小于3。
用户 2 和 5 是朋友,并且在都听了了10、11、12的歌曲,但不在同一天内。
解题
select distinct t.user1_id
,t.user2_id
from
(select a.user_id as user1_id
,b.user_id as user2_id
,a.song_id
,a.day
,count(1) over (partition by a.user_id,b.user_id,a.day) as cnt
from (select distinct user_id,song_id,day from Listens_1919) a
inner join (select distinct user_id,song_id,day from Listens_1919) b
on a.user_id <> b.user_id
and a.song_id = b.song_id
and a.day = b.day) t
inner join Friendship_1919 t1
on t.user1_id = t1.user1_id and t.user2_id = t1.user2_id
where t.cnt >= 3;