回忆是一座桥
却是通往寂寞的牢

SQL案例 - 查询每个月Top3的周杰伦歌曲

描述

从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲。

流水表 play_log:

日期 (fdate) 用户 ID (user_id) 歌曲 ID (song_id)
2022-01-08 10000 0
2022-01-16 10000 0
2022-01-20 10000 0
2022-01-25 10000 0
2022-01-02 10000 1
2022-01-12 10000 1
2022-01-13 10000 1
2022-01-14 10000 1
2022-01-10 10000 2
2022-01-11 10000 3
2022-01-16 10000 3
2022-01-11 10000 4
2022-01-27 10000 4
2022-02-05 10000 0
2022-02-19 10000 0
2022-02-07 10000 1
2022-02-27 10000 2
2022-02-25 10000 3
2022-02-03 10000 4
2022-02-16 10000 4

歌曲表song_info

歌曲 ID (song_id) 歌曲名称 (song_name) 歌手名称 (singer_name)
0 明明就 周杰伦
1 说好的幸福呢 周杰伦
2 江南 林俊杰
3 大笨钟 周杰伦
4 黑键 林俊杰

用户表user_info

user_id age
10000 18

输出:

month ranking song_name play_pv
1 1 明明就 4
1 2 说好的幸福呢 4
1 3 大笨钟 2
2 1 明明就 2
2 2 说好的幸福呢 1
2 3 大笨钟 1

示例1

输入:

drop table if exists play_log;
create table `play_log` (
    `fdate` date,
    `user_id` int,
    `song_id` int
);
insert into play_log(fdate, user_id, song_id)
values 
('2022-01-08', 10000, 0),
('2022-01-16', 10000, 0),
('2022-01-20', 10000, 0),
('2022-01-25', 10000, 0),
('2022-01-02', 10000, 1),
('2022-01-12', 10000, 1),
('2022-01-13', 10000, 1),
('2022-01-14', 10000, 1),
('2022-01-10', 10000, 2),
('2022-01-11', 10000, 3),
('2022-01-16', 10000, 3),
('2022-01-11', 10000, 4),
('2022-01-27', 10000, 4),
('2022-02-05', 10000, 0),
('2022-02-19', 10000, 0),
('2022-02-07', 10000, 1),
('2022-02-27', 10000, 2),
('2022-02-25', 10000, 3),
('2022-02-03', 10000, 4),
('2022-02-16', 10000, 4);

drop table if exists song_info;
create table `song_info` (
    `song_id` int,
    `song_name` varchar(255),
    `singer_name` varchar(255)
);
insert into song_info(song_id, song_name, singer_name) 
values
(0, '明明就', '周杰伦'),
(1, '说好的幸福呢', '周杰伦'),
(2, '江南', '林俊杰'),
(3, '大笨钟', '周杰伦'),
(4, '黑键', '林俊杰');

drop table if exists user_info;
create table `user_info` (
    `user_id`   int,
    `age`       int
);
insert into user_info(user_id, age) 
values
(10000, 18)

输出:

month|ranking|song_name|play_pv
1|1|明明就|4
1|2|说好的幸福呢|4
1|3|大笨钟|2
2|1|明明就|2
2|2|说好的幸福呢|1
2|3|大笨钟|1

说明:

1月被18-25岁用户播放次数最高的三首歌为“明明就”、“说好的幸福呢”、“大笨钟”,“明明就”和“说好的幸福呢”播放次数相同,排名先后由两者的song_id先后顺序决定。2月同理。

答案

解法1:

-- MySQL
with 
    t1 as (
        select 
            month(t1.fdate) month,
            t2.song_name,
            count(*) play_pv
        from
            play_log t1
            left join song_info t2 on t1.song_id=t2.song_id
            left join user_info t3 on t1.user_id=t3.user_id
        where
            year(t1.fdate)=2022
            and t3.age between 18 and 25
            and t2.singer_name = '周杰伦'
        group by 
            month(t1.fdate),
            t2.song_name
    ),
    t2 as (
        select *,row_number() over (partition by month order by play_pv desc) ranking from t1
    )
    select month,ranking,song_name,play_pv from t2 where ranking<=3; 

解法2:

-- MySQL
WITH t AS (
    SELECT
        MONTH(p.fdate) AS `month`
        ,ROW_NUMBER() OVER(PARTITION BY MONTH(p.fdate) ORDER BY COUNT(s.song_name) DESC, p.song_id ASC) AS ranking
        ,s.song_name 
        ,COUNT(s.song_name) AS play_pv
    FROM 
        play_log p 
        LEFT JOIN song_info s ON p.song_id = s.song_id
        LEFT JOIN user_info u ON p.user_id = u.user_id
    WHERE 
        YEAR(p.fdate) = 2022         -- 在2022年
        AND u.age BETWEEN 18 AND 25  -- 18-25岁用户
        AND s.singer_name = '周杰伦'  -- 周杰伦的歌曲
    GROUP BY MONTH(p.fdate), s.song_name, p.song_id  -- 窗口函数中用到song_id,所以分组时要加上
)
SELECT * FROM t WHERE ranking <= 3;  -- 每个月播放次数top 3

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 查询每个月Top3的周杰伦歌曲
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论