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

select author,date_format(start_time,'%Y-%m') as month,
       round(sum(if(if_follow=2,-1,if_follow))/count(if_follow),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 author order by date_format(start_time,'%Y-%m')) total_fans
from tb_user_video_log a
left join tb_video_info b
on a.video_id=b.video_id
where year(start_time)=2021
group by author,month
order by author,total_fans

#终于理解两层sum的含义啦：内层sum是跟group by 搭配的，外层sum是跟我们窗口函数搭配的。先运行内层再运行外层！