题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

select
author,
month,
round(fans_add/num,3) fans_growth_rate,
sum(fans_add) over (partition by author order by month) as total_fans
from
(
    select 
    author,
    date_format(start_time,"%Y-%m") as month,
    sum(
        case 
        when if_follow=2 then -1 
        when if_follow=1 then 1
        else 0
        end
    ) fans_add,
    count(*) num
    from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id
    where date_format(start_time,"%Y")='2021'
    group by author,month
)t
order by author,total_fans

看这个题目第一眼：抽象不好懂，第二眼：if_follow为1是关注，0是不变，2是取关，一下就明白了了，这里就是要sum，第三眼：截止当月的总粉丝量，意味着要用开窗函数累加！sum + over (partition by 粉丝加减量)+ ordet by 月份!，开写！ MD错了，为什么，第四眼：哦注释里写了要按照author和月份month排序，有点小坑，改了一下过了