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

/*2021年，每个创作者，每月涨粉率，截止当月的总粉丝量
涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。
--涨粉率   count(1)
--掉粉率   count(2)
--现在粉丝  sum（涨粉）-sum（掉粉）*/

select v.author,
       date_format(u.start_time,'%Y-%m') as month,
       round((sum(if(u.if_follow=1,1,0))-sum(if(u.if_follow=2,1,0)))/count(u.id),3) as fans_growth_rate,
       sum(sum(if(u.if_follow=1,1,0))-sum(if(u.if_follow=2,1,0)))over(partition by v.author order by date_format(u.start_time,'%Y-%m'))  as total_fans
from tb_user_video_log u
left join tb_video_info v on u.video_id=v.video_id	
where year(start_time)=2021
group by v.author, date_format(u.start_time,'%Y-%m')
order by v.author,total_fans