首页 > 试题广场 >

每个月Top3的周杰伦歌曲

[编程题]每个月Top3的周杰伦歌曲
  • 热度指数：120985 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
从听歌流水中找到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月同理。



备注:
MySQL中，日期转月份的函数为 month()，例：SELECT MONTH(‘2016-01-16') 返回 1。
算法知识视频讲解
添加回答
Sqlite
谦虚的布拉德offer多多头像 谦虚的布拉德offer多多
SELECT
    month,
    ranking,
    song_name,
    play_pv
FROM #第二层子表，因为WHERE的生效在SELECT之前，不能直接在第二层子表中，使用SELECT中的对象筛选
    (SELECT
        t1.month AS month, #月份
        row_number() OVER( #分区排序
            PARTITION BY t1.month #分区方式为月份
            ORDER BY t1.song_cnt DESC #排序依据为播放量，降序排列
            ) AS ranking,
        t1.song_name, #歌曲名称
        t1.song_cnt AS play_pv, #播放次数
        t1.song_id #歌曲ID
    FROM #第一层子表，分组显示每月份、每个歌曲的播放次数
        (SELECT
            MONTH(pl.fdate) AS month, #月份
            si.song_name, #歌曲名称
            COUNT(pl.song_id) AS song_cnt, #播放次数
            si.song_id #歌曲ID
        FROM #主表为播放记录，因此需要左连接其他表，条件为歌曲ID及用户ID匹配
            play_log AS pl
            LEFT JOIN song_info AS si ON si.song_id = pl.song_id
            LEFT JOIN user_info AS ui ON ui.user_id = pl.user_id
        WHERE #条件为年龄，年份，歌手
            (ui.age >= 18 AND ui.age <= 25)
            AND
            (YEAR(pl.fdate) = 2022)
            AND
            (si.singer_name = "周杰伦")
        GROUP BY #分组方式为歌曲名、歌曲ID、月份
            si.song_name,
            si.song_id,
            MONTH(pl.fdate)
        ORDER BY #排序方式为歌曲ID
            song_id
        ) AS t1
    ORDER BY #排序方式为歌曲ID、歌曲播放次数降序、歌曲ID
        t1.month,
        t1.t1.song_cnt DESC,
        t1.song_id
    ) AS t2
WHERE
    t2.ranking <=3




发表于 2025-06-06 15:58:01 回复(0)
Sqlite
丞相何故发笑头像 丞相何故发笑 
SELECT month, ranking, song_name, play_pv
FROM (
    SELECT 
        MONTH(p.fdate) AS month,
        s.song_name,
        COUNT(*) AS play_pv,
        ROW_NUMBER() OVER (PARTITION BY MONTH(p.fdate) ORDER BY COUNT(*) DESC) AS ranking
    FROM play_log p
    JOIN user_info u ON p.user_id = u.user_id
    JOIN song_info s ON p.song_id = s.song_id
    WHERE YEAR(p.fdate) = 2022
      AND u.age BETWEEN 18 AND 25
      AND s.singer_name = '周杰伦'
    GROUP BY MONTH(p.fdate), s.song_name
) t
WHERE ranking <= 3
ORDER BY month, ranking;

发表于 2025-05-28 16:39:48 回复(0)
Sqlite
牛客383458750号头像 牛客383458750号 
with  jay_play_log as (
    select
    month(p.fdate) month,
    s.song_name,
    count(*) as play_pv
from
    play_log p
    join song_info s
    on p.song_id = s.song_id
    join user_info u on p.user_id = u.user_id
where
    u.age between 18 and 25
    and year(p.fdate) =2022
    and s.singer_name = '周杰伦'
group by
    month(p.fdate),
    s.song_name
    ),
ranked_song as (
    select
    month,
    song_name,
    play_pv,
    row_number() over (
        partition by month
        order by 
             play_pv desc,song_name
    ) AS ranking
from jay_play_log
)
select
month,ranking,song_name,play_pv
from ranked_song
where ranking <= 3
order by month,ranking;

这到底错在哪了，真疯了，结果显示这样的，谁来救救我

发表于 2025-05-06 20:11:34 回复(0)
Sqlite
平安是福花开富贵头像 平安是福花开富贵 
select mth as month
     , ranking
     , song_name
     , play_pv
from (
select mth
     , row_number() over(partition by mth order by play_pv desc,song_id asc)   as ranking
     , song_name
     , play_pv
from (
    select mth,log.song_id,song_name,count(log.song_id) as play_pv
    from 
    (
        select month(fdate) as mth,user_id,song_id
        from play_log
        where year(fdate) = 2022
    ) log
    join (
        select user_id
        from user_info
        where age >=18 and age <=25
        ) a on log.user_id = a.user_id
    join (
        select song_id,song_name
        from song_info
        where singer_name = '周杰伦'
        ) b on log.song_id = b.song_id
    group by mth,song_name,song_id

) result 
) r
where ranking <= 3

发表于 2025-03-11 16:58:37 回复(0)
Sqlite
御坂202412头像 御坂202412
如果某个月里，有两首或多首歌的播放量是一样的，那么还要按照歌曲编号再排一次顺序。
确实，题目没有明确说明这个要求，但实际项目中要考虑的东西甚至不止于此！求职者能否考虑到这些隐藏条件，也是面试官要考察的一部分！ 
            ROW_NUMBER() OVER (
                PARTITION BY
                    MONTH (fdate)
                ORDER BY
                    COUNT(1) DESC,
                    song_info.song_id ASC
            ) AS ranking
发表于 2025-02-14 11:39:06 回复(0)
Sqlite
青衣白马少年时头像 青衣白马少年时 
select *
from (select month(fdate) month,
rank()over(partition by month(fdate) order by count(song_id) desc, song_id) ranking,
song_name,
count(song_id) play_pv
from play_log 
join user_info using(user_id)
join song_info using(song_id)
where singer_name = '周杰伦' and age between 18 and 25
group by month(fdate),song_name,song_id) ranked
where ranking between 1 and 3
如果排序的时候不 额外加song_id会出现并列的情况，其次窗口函数用到的列在group by 中也要加上
发表于 2024-12-25 15:53:45 回复(0)
Sqlite
牛客717164362号头像 牛客717164362号
select * from(
select MONTH(fdate)as month ,
ROW_NUMBER()over(partition by month(fdate) order by (b.song_id)desc,
b.song_id asc )ranking,song_name,COUNT(c.song_id) play_pv
from user_info a 
left join play_log b on a.user_id = b.user_id 
left join song_info c on b.song_id = b.song_id 
where (age between 18 and 25) and (singer_name like '%周杰伦%')and (YEAR(fdate)= 2022)
group by MONTH(fdate),song_name,b.song_id) q
where ranking <3

发表于 2024-11-26 10:56:20 回复(0)
Sqlite
一颗宏心头像 一颗宏心
# 使用窗口函数rank()结合子查询
select `month`,ranking,song_name,play_pv
from (
        select `month`,rank() over(partition by `month` order by play_pv desc,song_id) as ranking,song_name,play_pv from (
            select distinct month(p.fdate) `month`,s.song_name,s.song_id,
            count(*) over(partition by s.song_name,month(p.fdate)) as play_pv
            from song_info s inner join play_log p
            on s.song_id = p.song_id
            inner join user_info u
            on p.user_id = u.user_id
            where s.singer_name='周杰伦' and u.age between 18 and 25
        )t1
)t2
where ranking <=3

发表于 2024-09-19 22:32:43 回复(0)
Sqlite
流尘浅林头像 流尘浅林 
#弄了个屎山代码
with t as (
    select
        d.month,row_number() over (
            partition by d.month
            order by count(d.song_id) desc,s.song_id
            ) as ranking,
            song_name,
            count(d.song_id) as play_pv
    from
    (   
        select month (fdate) as month,user_id,song_id
        from play_log
        where year (fdate) = 2022
    ) d
    inner join (
        select song_id,song_name,singer_name
        from song_info
        where singer_name = '周杰伦'
    ) s on s.song_id = d.song_id
    inner join (
        select user_id,age
        from user_info
        where age between 18 and 25
    ) u on d.user_id = u.user_id
        group by d.month,song_name,d.song_id
)
select * 
from t
where ranking <= 3;

发表于 2024-09-05 10:47:38 回复(0)
Sqlite
风度翩翩的中国人很喜欢走神头像 风度翩翩的中国人很喜欢走神
select
*
from (
select
month,row_number()over(partition by month order by play_pv desc ) ranking,song_name,play_pv
from (
select
month(fdate) month,song_name,count(*) play_pv
from play_log a
inner join song_info b using(song_id)
inner join user_info c using(user_id)
where singer_name='周杰伦'
and age between 18 and 25
and year(fdate)=2022
group by month,song_name,a.song_id
order by month,play_pv desc,a.song_id
)t
)t1 where ranking<=3

发表于 2024-08-19 10:28:56 回复(0)
提交观点

问题信息

SQL
来自：2023年腾讯音乐秋招...
难度：
13条回答 1486浏览

热门推荐

相关试题

每个月Top3的周杰伦歌曲

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号