腾讯大数据面试SQL-向用户推荐好友喜欢的音乐

2024-06-20 08:13:22 浏览数 (1)

一、题目

现有三张表分别为:

用户关注表t_follow(user_id,follower_id)记录用户ID及其关注的人ID,请给用户1推荐他关注的用户喜欢的音乐名称

代码语言:javascript复制
 ---------- -------------- 
| user_id  | follower_id  |
 ---------- -------------- 
| 1        | 2            |
| 1        | 4            |
| 1        | 5            |
 ---------- -------------- 

用户喜欢的音乐t_music_likes(user_id,music_id)

代码语言:javascript复制
 ---------- ----------- 
| user_id  | music_id  |
 ---------- ----------- 
| 1        | 10        |
| 2        | 20        |
| 2        | 30        |
| 3        | 20        |
| 3        | 30        |
| 4        | 40        |
| 4        | 50        |
 ---------- ----------- 

音乐名字表t_music(music_id,music_name)

代码语言:javascript复制
 ----------- ------------- 
| music_id  | music_name  |
 ----------- ------------- 
| 10        | a           |
| 20        | b           |
| 30        | c           |
| 40        | d           |
| 50        | e           |
 ----------- ------------- 

二、分析

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

1.根据用户关注表和用户喜欢的音乐表进行关联,查询出每个用户喜欢的音乐ID;

2.再关联音乐名字表,关联出对应的音乐名称;

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

维度

评分

题目难度

⭐️⭐️⭐️

题目清晰度

⭐️⭐️⭐️⭐️⭐️

业务常见度

⭐️⭐️⭐️⭐️⭐️

三、SQL

1.根据用户关注表和用户喜欢的音乐表进行关联,查询出每个用户关注用户喜欢的音乐ID;

执行SQL

代码语言:javascript复制
select t1.user_id,
       t1.follower_id,
       t2.music_id
from (select user_id,
             follower_id
      from t_follow
      where user_id = 1) t1
         left join
     (select user_id,
             music_id
      from t_music_likes) t2
     on t1.follower_id = t2.user_id

执行结果

代码语言:javascript复制
 ------------- ----------------- -------------- 
| t1.user_id  | t1.follower_id  | t2.music_id  |
 ------------- ----------------- -------------- 
| 1           | 2               | 20           |
| 1           | 2               | 30           |
| 1           | 4               | 40           |
| 1           | 4               | 50           |
| 1           | 5               | NULL         |
 ------------- ----------------- -------------- 

2.关联音乐名字表,关联出对应的音乐名称;

执行SQL

代码语言:javascript复制
select t1.user_id,
       t1.follower_id,
       t2.music_id,
       t3.music_name
from (select user_id,
             follower_id
      from t_follow
      where user_id = 1) t1
         left join
     (select user_id,
             music_id
      from t_music_likes) t2
     on t1.follower_id = t2.user_id
         left join
     (select music_id,
             music_name
      from t_music) t3
     on t2.music_id = t3.music_id

执行结果

代码语言:javascript复制
 ------------- ----------------- -------------- ---------------- 
| t1.user_id  | t1.follower_id  | t2.music_id  | t3.music_name  |
 ------------- ----------------- -------------- ---------------- 
| 1           | 2               | 20           | b              |
| 1           | 2               | 30           | c              |
| 1           | 4               | 40           | d              |
| 1           | 4               | 50           | e              |
| 1           | 5               | NULL         | NULL           |
 ------------- ----------------- -------------- ---------------- 

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

行转列并对重复的音乐名称去重,得到最终结果。行转列使用聚合函数collect_set()函数,然后使用concat_ws转成字符串

执行SQL

代码语言:javascript复制
select t1.user_id,
       concat_ws(',', collect_set(t3.music_name)) as push_music
from (select user_id,
             follower_id
      from t_follow
      where user_id = 1) t1
         left join
     (select user_id,
             music_id
      from t_music_likes) t2
     on t1.follower_id = t2.user_id
         left join
     (select music_id,
             music_name
      from t_music) t3
     on t2.music_id = t3.music_id
group by t1.user_id

执行结果

代码语言:javascript复制
 ------------- ------------- 
| t1.user_id  | push_music  |
 ------------- ------------- 
| 1           | b,c,d,e     |
 ------------- ------------- 

四、建表语句和数据插入

代码语言:javascript复制
--建表语句
CREATE TABLE t_follow (
user_id bigint COMMENT '用户ID',
follower_id bigint COMMENT '关注用户ID'
) COMMENT '用户关注表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' 
;
-- 插入数据
insert into t_follow(user_id,follower_id)
values
(1,2),
(1,4),
(1,5)
;
-- 建表语句
CREATE TABLE t_music_likes (
user_id bigint COMMENT '用户ID',
music_id bigint COMMENT '音乐ID'
) COMMENT '用户喜欢音乐ID'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' 
;
--插入语句
insert into t_music_likes(user_id,music_id)
values
(1,10),
(2,20),
(2,30),
(3,20),
(3,30),
(4,40),
(4,50)
;
--建表语句
CREATE TABLE t_music (
music_id bigint COMMENT '音乐ID',
music_name string COMMENT '音乐名称'
) COMMENT '音乐名字表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' 
;
-- 插入语句
insert into t_music(music_id,music_name)
values
(10,'a'),
(20,'b'),
(30,'c'),
(40,'d'),
(50,'e')
;

0 人点赞