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

select tv.author,substr(tu.start_time,1,7) as month,
round(sum(case when if_follow =1 then 1 
when if_follow=2 then -1
else 0 end)/count(*),3) as fans_growth_rate,
sum(sum(case when if_follow =1 then 1 
when if_follow=2 then -1
else 0 end)) over(partition by tv.author order by substr(tu.start_time,1,7)) as total_fans
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
where year(tu.start_time)=2021
group by tv.author,substr(tu.start_time,1,7)
order by tv.author,total_fans

难点在于10月分的粉丝量是在9月基础上累加的，因此算完10月本身的粉丝量变化后，还要用一个sum()over()将两个月的累计在一起