首页 > 试题广场 >

每个月Top3的周杰伦歌曲

[编程题]每个月Top3的周杰伦歌曲
  • 热度指数：639 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲
示例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。
算法知识视频讲解
添加回答
牛客100560155号头像 牛客100560155号
上面大佬的答案，做个理解记录，需要从下往上看，大佬牛逼

#取排名前三的歌曲信息
SELECT month,
       ranking,
       song_name,
       play_pv
FROM
#查询2022年每个月18-25岁用户听过周杰伦的歌曲信息，月份、排名、歌名、歌曲id、歌曲每个月的听歌次数
(SELECT month,
             ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC, song_id) AS ranking,
             song_name,
             play_pv,
             song_id
      FROM
#查询2022年每个月18-25岁用户听过周杰伦的歌曲信息，月份、歌名、歌曲id、歌曲每个月的听歌次数
      (SELECT MONTH(fdate)     AS `month`,
                   song_name,
                   song_id,
                   COUNT(song_name) AS play_pv
            FROM
#查询2022年18-25岁用户听过的周杰伦的歌曲信息，日期、歌名、歌曲id
            (SELECT fdate, song_name, song_id
                  FROM play_log
                           JOIN song_info USING (song_id)
                           JOIN user_info USING (user_id)
                  WHERE singer_name = '周杰伦'
                    AND YEAR(fdate) = 2022
                    AND age BETWEEN 18 AND 25) t1

            GROUP BY 1, 2,3) t2
            ) t3
WHERE ranking <= 3;
发表于 2023-05-18 20:30:21 回复(1)
整夜如雨头像 整夜如雨 
SELECT month,
       ranking,
       song_name,
       play_pv
FROM (SELECT month,
             ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC, song_id) AS ranking,
             song_name,
             play_pv,
             song_id
      FROM (SELECT MONTH(fdate)     AS `month`,
                   song_name,
                   song_id,
                   COUNT(song_name) AS play_pv
            FROM (SELECT fdate, song_name, song_id
                  FROM play_log
                           JOIN song_info USING (song_id)
                           JOIN user_info USING (user_id)
                  WHERE singer_name = '周杰伦'
                    AND YEAR(fdate) = 2022
                    AND age BETWEEN 18 AND 25) t1
            GROUP BY 1, 2, 3) t2) t3
WHERE ranking <= 3;

发表于 2023-05-11 16:31:05 回复(0)
kyzheng头像 kyzheng
通过两组算例，供参考~ 

select S7.* from
    (select S6.month, row_number() over(partition by S6.month order by S6.play_pv desc) ranking, S6.song_name, S6.play_pv from
            (select distinct * from
                (select S4.month, S4.song_name, count(S4.song_id) over(partition by S4.month, S4.song_name) play_pv from
                    (select month(S1.fdate) as 'month', S1.user_id, S1.song_id, S2.song_name
                    from play_log S1 join song_info S2
                    on S1.song_id = S2.song_id
                    join user_info S3
                    on S1.user_id = S3.user_id
                    where year(S1.fdate) = '2022'
                    and S3.age >= 18
                    and S3.age <= 25
                    and S2.singer_name = '周杰伦'
                    order by S1.song_id) S4) S5 ) S6    
        order by month, play_pv desc) S7 where S7.ranking <= 3




发表于 2023-05-04 12:54:35 回复(1)
躺平又起来了的秋田犬很冷艳头像 躺平又起来了的秋田犬很冷艳 
select t.month,t.ranking,si.song_name,t.play_pv
from(
    select month(pl.fdate) as month,
    count(pl.user_id) as play_pv,pl.song_id,
    rank() over (partition by month(pl.fdate) order by count(pl.user_id) desc,pl.song_id) as ranking
    from play_log pl  
    where year(pl.fdate)=2022 
    and pl.user_id in (
        select user_id from user_info where age between 18 and 25
    )
    and pl.song_id in (
        select song_id from song_info where singer_name="周杰伦"
    )
    group by month(pl.fdate),pl.song_id)t
left join song_info si on si.song_id=t.song_id
where t.ranking<4
order by t.month,t.ranking

发表于 2024-08-23 11:43:10 回复(0)
牛客菜鸟一号头像 牛客菜鸟一号
select month,ranking,song_name,play_pv
    from
(select month,row_number()over(partition by month order by play_pv desc,song_id)ranking,song_name,play_pv
    from
        (select month,song_id,song_name,count(song_id)play_pv
            from
                (select month(fdate)month,song_id,song_name,singer_name
                    from play_log  join song_info using(song_id) join  user_info using(user_id)
                    where singer_name='周杰伦'and left(fdate,4)='2022'and age between 18and 25)a
            group by month,song_id,song_name)b)c
where ranking<=3
发表于 2023-09-11 10:40:36 回复(0)
Iawliet头像 Iawliet 
With lp As (Select p.*
    from play_log p left join user_info u
    on p.user_id = u.user_id
    where u.age >=18 and u.age <=25),
     
lps As (
    Select lp.*,s.song_name
    from lp left join song_info s
    on lp.song_id = s.song_id
    where singer_name="周杰伦"
),
 result as (
    select month(fdate) as month,song_id,count(*) as num
    from lps
    group by month(fdate),song_id
    order by month, num DESC
 ),
r as (
    select month,row_number() over(partition by month order by num DESC,song_id ASC) ranking,song_id,num as play_pv
    from result
  
)
    select r.month,r.ranking,s.song_name,r.play_pv 
    from r left join song_info s
    on r.song_id = s.song_id
    where ranking in (1,2,3);

发表于 2023-08-13 17:20:22 回复(0)
拒绝无效加班的小熊猫很机灵头像 拒绝无效加班的小熊猫很机灵
SELECT month,
RANK() OVER (PARTITION BY MONTH ORDER BY play_pv DESC,tb12.song_id) ranking,
song_name,play_pv
FROM
(SELECT MONTH(fdate) month,song_id,COUNT(song_id) play_pv
FROM play_log tb1
LEFT JOIN user_info tb2 ON tb1.user_id = tb2.user_id
WHERE age BETWEEN 18 AND 25
GROUP BY month,song_id 
) tb12
LEFT JOIN song_info tb3
ON tb12.song_id = tb3.song_id
WHERE singer_name = '周杰伦'
ORDER BY month;
无语了，做了几个小时，自测都是过的，提交的时候竟然说我编译错误。
发表于 2023-08-03 02:01:40 回复(0)
在记录秋招的肖恩很爱喝奶茶头像 在记录秋招的肖恩很爱喝奶茶 
# 使用了窗口函数
select month,ranking,b2.song_name,play_pv
from
(
    select month(a.fdate) as month,b.song_id as song_id,
    ROW_NUMBER() OVER (partition by month(a.fdate) ORDER BY count(*) DESC ) AS ranking,
    b.song_name,count(*) as play_pv
from play_log a 
left join song_info b on a.song_id=b.song_id
left join user_info c on a.user_id=c.user_id
where date_format(a.fdate,'%Y')='2022' 
and c.age between 18 and 25
      and b.singer_name ='周杰伦'
group by month,b.song_id,b.song_name
) b1 
left join song_info b2 on b1.song_id=b2.song_id
where ranking<4

发表于 2023-07-17 20:42:40 回复(0)
牛客44609022号头像 牛客44609022号
实例通过，仅供参考，但是感觉写的好复杂
with tmp as 
(select month(ls.fdate) as month 
,ls.song_id
,so.song_name
,count(1) as play_pv
from play_log ls-- 歌单表
left join song_info so
on ls.song_id=so.song_id
left join user_info us
on ls.user_id=us.user_id
where us.age between 18 and 25
and so.singer_name='周杰伦'
and substr(ls.fdate,1,4)='2022'
group by  month(ls.fdate) 
,ls.song_id
,so.song_name),
tmp_a as 
(
select month
,row_number() over(partition by month order by play_pv desc,song_id) as ranking
,song_name
,play_pv 
from tmp)
select month
,ranking
,song_name
,play_pv
from tmp_a
where ranking<=3
order by month ,ranking

发表于 2023-07-13 17:54:10 回复(0)
牛客141691936号头像 牛客141691936号 
SELECT
    month,
    ranking,
    song_name,
    play_pv
FROM
    (
        SELECT
            MONTH(pl.fdate) AS month,
            ROW_NUMBER() OVER (PARTITION BY MONTH(pl.fdate) ORDER BY COUNT(*) DESC, si.song_id) AS ranking,
            si.song_name,
            COUNT(*) AS play_pv
        FROM
            play_log pl
            JOIN song_info si ON pl.song_id = si.song_id
            JOIN user_info ui ON pl.user_id = ui.user_id
        WHERE
            ui.age BETWEEN 18 AND 25
            AND YEAR(pl.fdate) = 2022
            AND si.singer_name = '周杰伦'
        GROUP BY
            MONTH(pl.fdate),
            si.song_id,
            si.song_name
    ) AS subquery
WHERE
    ranking <= 3
ORDER BY
    month,
    ranking;

发表于 2023-06-26 16:41:50 回复(0)
烧鹅gogo头像 烧鹅gogo 
# 18-25
# 每个月
# 周杰伦
# top3
select month,ranking,song_name,play_pv
from
        (select month,row_number() over (partition by month
                                                order by count desc,song_id ) as ranking,song_name, play_pv
        from(
            select  month(fdate) as month, count(*) as count,p.song_id,song_name,count(*) as play_pv
            from play_log p
            inner join user_info u
            on p.user_id=u.user_id and age>=18 and age<=25 and year(fdate)=2022
            inner join song_info s
            on p.song_id=s.song_id and singer_name='周杰伦'
            group by  month(fdate),p.song_id,song_name) t
         )k
where ranking<4
发表于 2023-06-14 15:35:48 回复(0)
南开雨头像 南开雨
这样只能通过两个示例， 
排名先后由两者的song_id先后顺序决定
这个条件实现不了，求大佬解答： 
select
    *
from
    (
        select
            month,
            row_number() over (
                partition by
                    month
                order by
                    play_pv desc,song_id
            ) as ranking,
            song_name,
            play_pv
        from
            (
                select
                    month (fdate) as month,
                    song_name,
                    count(*) as play_pv
                from
                    (
                        select
                            *
                        from
                            play_log
                            join song_info using (song_id)
                            join user_info using (user_id)
                        where
                            age between 18 and 25
                            and singer_name like '周杰伦'
                        order by
                            song_id
                    ) as a
                group by
                    month (fdate),
                    song_name
            ) as b
    ) as c
where
    ranking <= 3


发表于 2023-03-29 16:56:26 回复(1)
提交观点

问题信息

上传者：牛客352960号
难度：
12条回答 1048浏览

热门推荐

相关试题

每个月Top3的周杰伦歌曲

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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