大数据面试SQL（十四）：向用户推荐好友喜欢的音乐

文章目录

向用户推荐好友喜欢的音乐

一、题目

二、分析

三、SQL实战

四、样例数据参考

---

向用户推荐好友喜欢的音乐

一、题目

现有三张表样例数据分别为：

1、用户关注表t1_follow记录用户及其关注的人。

2、用户喜欢的音乐t1_music_likes。

3、音乐名字表t1_music。

请给用户1凡尘雨推荐他关注的用户喜欢的音乐名称。

 

目标结果：

 

二、分析

本题要给用户1凡尘雨推荐其关注的用户喜欢的音乐名称，主要是考察表之间的关联，并考察行转列及去重相关操作。

1、根据用户关注表和用户喜欢的音乐表进行关联，查询出每个用户喜欢的音乐ID。

2、再关联音乐名字表，关联出对应的音乐名称。

3、行转列并对重复的音乐名称去重，得到最终结果。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、SQL实战

1、根据用户关注表和用户喜欢的音乐表进行关联，查询出每个用户关注用户喜欢的音乐ID。

查询语句：

select t1.user_id,
       t1.user_name,
       t1.follower_id,
       t1.follower_name,
       t2.music_id
from (select user_id,user_name,follower_name,follower_id
      from t1_follow
      where user_id = 1) t1
      left join
     (select user_id,
             music_id
     from t1_music_likes) t2
     on t1.follower_id = t2.user_id;

查询结果：

 

2、关联音乐名字表，关联出对应的音乐名称。 

查询语句：

select t1.user_id,
       t1.user_name,
       t1.follower_id,
       t1.follower_name,
       t2.music_id,
       t3.music_name
from (select user_id,user_name,follower_name,follower_id
      from t1_follow
      where user_id = 1) t1
         left join
     (select user_id,
             music_id
      from t1_music_likes) t2
     on t1.follower_id = t2.user_id
         left join
     (select music_id,
             music_name
      from t1_music) t3
     on t2.music_id = t3.music_id
;

查询结果：

3、行转列并对重复的音乐名称去重，得到最终结果。

查询语句：

select user_id,
       concat_ws(',', collect_list(music_name)) as push_music
from (
         select user_id,music_id,max(music_name) as music_name
         from (
                  select t1.user_id,
                         t1.user_name,
                         t1.follower_id,
                         t1.follower_name,
                         t2.music_id,
                         t3.music_name
                  from (select user_id,user_name,follower_name,follower_id
                        from t1_follow
                        where user_id = 1
                        ) t1
                        left join
                       (select user_id,
                               music_id
                        from t1_music_likes
                        ) t2
                       on t1.follower_id = t2.user_id
                       left join
                       (select music_id,
                               music_name
                        from t1_music
                        ) t3
                       on t2.music_id = t3.music_id
              ) tt
         where music_id is not null
         group by user_id,music_id
     ) tt_all
group by user_id;

查询结果：

四、样例数据参考

--建表语句
CREATE TABLE t1_follow (
   user_id bigint COMMENT '用户ID',
   user_name string COMMENT '用户名',
   follower_id bigint COMMENT '关注用户ID',
   follower_name string COMMENT '关注用户名'
) COMMENT '用户关注表';
-- 插入数据
insert into t1_follow(user_id,user_name,follower_id,follower_name)
values
    (1,'凡尘雨',2,'琴流音'),
    (1,'凡尘雨',4,'飘过的浮云'),
    (1,'凡尘雨',5,'无法言说的痛')
;
-- 建表语句
CREATE TABLE t1_music_likes (
    user_id bigint COMMENT '用户ID',
    music_id bigint COMMENT '音乐ID'
) COMMENT '用户喜欢音乐ID';
--插入语句
insert into t1_music_likes(user_id,music_id)
values
    (1,10),
    (2,20),
    (2,30),
    (3,20),
    (3,30),
    (4,40),
    (4,50)
;
--建表语句
CREATE TABLE t1_music (
    music_id bigint COMMENT '音乐ID',
    music_name string COMMENT '音乐名称'
) COMMENT '音乐名字表';
-- 插入语句
insert into t1_music(music_id,music_name)
values
    (10,'海阔天空'),
    (20,'星辰大海'),
    (30,'岁月神偷'),
    (40,'爱如潮水'),
    (50,'高山流水')
;

---

• 📢博客主页：https://lansonli.blog.csdn.net
• 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
• 📢本文由 Lansonli 原创，首发于 CSDN博客🙉
• 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨