每个月Top3的周杰伦歌曲

with t as (
    select month(fdate) as `month`
        ,row_number() over (partition by month(fdate) order by count(*) desc,song_id) as ranking
        ,song_name
        ,count(*) as play_pv
    from play_log
    join user_info using(user_id)
    join song_info using(song_id)
    where year(fdate) = '2022'
    and age between 18 and 25
    and singer_name = '周杰伦'
    group by month(fdate),song_name,song_id    #因为窗口函数中用到song_id排序。所以分组时要加上song_id
)
select *
from t
where ranking <= 3

select 
    *
from
(
# 按月份分区、按播放总量降序、歌曲id升序进行rank排序
select
    a.month,
    row_number() over(partition by a.month order by play_pv desc, song_id asc) as ranking,
    song_name,
    play_pv
from
(
# 先搜索周杰伦22年每个月的不同歌曲及播放总量
select
    month(pl.fdate) as month,
    si.song_name,
    count(si.song_name) as play_pv,
    si.song_id
from
    play_log as pl inner join
    song_info as si on si.song_id = pl.song_id inner 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 
    month(pl.fdate),
    si.song_name,
    si.song_id
) a
) b
having 
    ranking <= 3

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

with t as(
select month(pl.fdate) as month,
rank() over (partition by month(pl.fdate) order by count(pl.song_id) desc,si.song_id) as ranking,
si.song_name as song_name,
count(*) as play_pv

from play_log pl,song_info si,user_info ui
where pl.user_id=ui.user_id
and pl.song_id = si.song_id
and ui.age between 18 and 25 
and si.singer_name = '周杰伦'
and year(pl.fdate) ='2022'
group by month(pl.fdate),song_name,pl.song_id
)
select *
from t
where ranking <= 3

千幸万苦终于搞懂了

首先链接三张表，按照18-25，2022年，周杰伦的歌筛选条件，用rank函数对同一月份的总播放数排序，rank函数在排序时会有两个排序相等的情况，给他多设置一个参数可以解决这个问题，或者使用ROW_NUMBER()函数

select * 
from 
(select month(fdate) month,
row_number()over(partition by month(fdate) order by count(song_id) desc ,p.song_id) ranking,
song_name,
count(p.song_id) play_pv 
from play_log p
join song_info s on s.song_id = p.song_id
join user_info u on u.user_id = p.user_id
where age <= 25 and age >= 18 and year(fdate) = '2022' and singer_name = '周杰伦'
group by month,song_name,p.song_id) a
where ranking < 4

WITH
    my_play_log AS (
        SELECT
            MONTH(pl.fdate) AS month,
            si.song_id,
            si.song_name,
            ui.user_id
        FROM
            play_log pl
            JOIN song_info si ON pl.song_id = si.song_id
            AND si.singer_name = '周杰伦'
            JOIN user_info ui ON pl.user_id = ui.user_id
            AND ui.age BETWEEN 18 AND 25
    ),
    agg AS (
        SELECT
            month,
            song_id,
            song_name,
            COUNT(*) AS play_pv
        FROM
            my_play_log
        GROUP BY
            month,
            song_id,
            song_name
    )
SELECT
    a.*
FROM
    (
        SELECT
            month,
            ROW_NUMBER() OVER (
                PARTITION BY
                    month
                ORDER BY
                    play_pv DESC,
                    song_id ASC
            ) AS ranking,
            song_name,
            play_pv
        FROM
            agg
        ORDER BY
            month ASC,
            ranking ASC
    ) AS a
WHERE
    ranking between 1 and 3;

with play_log_name as(
    select
        month(p.fdate) as month,
        p.user_id,
        p.song_id,
        s.song_name
    from play_log p 
    join song_info s on p.song_id = s.song_id
    where s.singer_name = '周杰伦' and year(p.fdate) = 2022
),

usr_age_play as(
    select
        pln.month,
        u.age,
        pln.song_id,
        pln.song_name
    from play_log_name pln
    join user_info u on pln.user_id = u.user_id
),

constrained_age_play_count as (
    select
        month,
        song_id,
        song_name,
        count(*) as play_pv
    from usr_age_play
    where age between 18 and 25
    group by month, song_id, song_name
),

ranked_songs as (
    select
        month,
        song_name,
        play_pv,
        row_number() over(partition by month order by play_pv desc, song_id asc ) as ranking
    from constrained_age_play_count
)

select 
    month,
    ranking,
    song_name,
    play_pv
from ranked_songs
where ranking <=3
order by month, ranking