力扣1919 兴趣相同的朋友

本文介绍了一个SQL查询问题,通过两个数据表(Listens和Friendship)找出在同一天听过至少三首相同歌曲且为朋友关系的用户。通过左连接和分组操作,展示了如何在数据库中实现这一功能。
摘要由CSDN通过智能技术生成

        识别出共享相同音乐品味的朋友对。我们将通过一个具体的案例,展示如何从两个不同的数据表中提取、分析并最终确定那些兴趣相同的朋友,这不仅是对SQL查询技巧的一次生动展示,也是对数据分析能力的一次考验。

目录

题目描述

解题思路

1. 确定朋友关系

2. 获取听歌记录

3. 筛选相同歌曲的听歌记录

4. 计算共同听歌数量

5. 确定兴趣相同的朋友

完整代码及解释


题目描述

表: 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的歌曲,但不在同一天内。

解题思路

1. 确定朋友关系

Friendship表中提取所有朋友对,这是识别兴趣相同的朋友的基础。

2. 获取听歌记录

对每对朋友,分别获取他们的听歌记录。这一步涉及到将Friendship表中的每个朋友对与Listens表进行关联,以获得他们各自的听歌历史。

3. 筛选相同歌曲的听歌记录

对于每对朋友,筛选出他们在同一天内听过的相同歌曲。这一步是通过比较两个用户在同一天听的歌曲是否相同来实现的。

4. 计算共同听歌数量

对筛选后的记录按朋友对和日期进行分组,计算每对朋友在每个共同的日期上听过的相同歌曲数量。

5. 确定兴趣相同的朋友

从上一步中筛选出在至少一个相同的日期内共同听过至少三首相同歌曲的朋友对,认定他们为兴趣相同的朋友。

完整代码及解释

select
    u1 as user1_id ,
    u2 as user2_id 
from(
    select d1, u1, u2
    from(
        select  t1.user1_id as u1,
                t2.song_id as s1,
                t1.user2_id as u2,
                t3.song_id as s2,
                t2.day as d1,
                t3.day as d2
        from
        (   
            select user1_id, user2_id
            from Friendship 
        )t1
        left join(
            select user_id, song_id, day
            from Listens 
        )t2 on t1.user1_id = t2.user_id
        left join(
            select user_id, song_id, day
            from Listens 
        )t3 on t1.user2_id = t3.user_id
    )t
    where d1 = d2 and s1 = s2
    group by d1, u1, u2
    having count(distinct s1) > 2
)tt
group by u1, u2

对于每个用户(无论是user1还是user2),都从Listens表中获取他们的听歌记录。这一步骤通过两次左连接(LEFT JOIN)Listens表来完成,一次连接匹配user1_id的听歌记录,另一次连接匹配user2_id的听歌记录。

在得到了朋友对的听歌记录后,需要筛选出那些在同一天听了同一首歌的记录。这是通过比较两次左连接得到的结果中的daysong_id来实现的。

对于筛选出来的记录,按照日期、user1_iduser2_id进行分组,并计算每组中不同歌曲的数量。这里使用count(distinct s1)来确保歌曲的唯一性。

最后,从上一步得到的结果中选择满足条件(即在同一天内听过至少三首相同歌曲的)的朋友对的user1_iduser2_id

通过

  • 11
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

昊昊该干饭了

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值