LeetCode_sql_day25（1919.兴趣相同的朋友）

目录

描述：

数据准备：

分析：

代码：

总结：

---

描述：

表: Listens

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| song_id     | int     |
| day         | date    |
+-------------+---------+
该表没有主键，因此会存在重复的行。
该表的每一行所代表的含义是：用户（user_id）在某天（day）听了某首歌曲（song_id）。

表: 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）。

结果表的格式如下例。

示例 1：

输入：
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        |
+----------+----------+
输出：
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
+----------+----------+
解释：
用户 1 和 2 是朋友, 并且他们在同一天内都听了10、11、12的歌曲。所以，他们是兴趣相同的朋友。
用户 1 和 3 在同一天内都听了10、11、12的歌曲，但他们不是朋友。
用户 2 和 4 是朋友，但他们同一天内听过相同的歌曲的数量小于3。
用户 2 和 5 是朋友，并且在都听了了10、11、12的歌曲，但不在同一天内。

数据准备：

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')

insert into Friendship (user1_id, user2_id) values ('2', '4')

insert into Friendship (user1_id, user2_id) values ('2', '5')

分析：

①观察结果 我们需要对listens表进行自连接 连接条件是相同天相同歌曲且左表id小于右表

select l1.user_id l1uid, l2.user_id l2uid, l1.song_id, l1.day
            from Listens l1
                     join Listens l2
            where l1.song_id = l2.song_id
              and l1.day = l2.day
              and l1.user_id < l2.user_id

②然后基于上表 我们找出每天听大于等于三首的两个用户

select l1uid, l2uid, count(distinct song_id) r1
         from t1
         group by l1uid, l2uid, day
         having r1 >= 3

③此时我们已经满足了兴趣朋友的第二个条件

通过与friendship表连接 找到兴趣朋友  关联条件是 两个听众id相同

select distinct l1uid user1_id, l2uid user2_id
from t2
         join Friendship
              on l1uid = Friendship.user1_id and l2uid = Friendship.user2_id

代码：

with t1 as (select l1.user_id l1uid, l2.user_id l2uid, l1.song_id, l1.day
            from Listens l1
                     join Listens l2
            where l1.song_id = l2.song_id
              and l1.day = l2.day
              and l1.user_id < l2.user_id) -- 找出每天听相同歌曲的两个用户
   , t2 as (select l1uid, l2uid, count(distinct song_id) r1
            from t1
            group by l1uid, l2uid, day
            having r1 >= 3)                -- 找出每天听超过两首相同歌曲的两个用户
select distinct l1uid user1_id, l2uid user2_id
from t2
         join Friendship
              on l1uid = Friendship.user1_id and l2uid = Friendship.user2_id;

总结：

本题很容易忽略的一个点是要对数据去重

根据歌曲求和时要对歌曲去重

最后关联听众id时 也要对两者去重