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

/*
select  A.author,
        A.month,
        round((A.addFanscnt-A.subtrFanscnt)/A.videoCnt,3) fans_growth_rate,
        sum(addFansMonth) over(partition by A.author order by A.month) total_fans 
    from (
   select tvi.author,
          date_format(tvl.start_time,'%Y-%m') month ,
          count(if(tvl.if_follow=1,1,null)) addFanscnt,
          count(if(tvl.if_follow=2,1,null)) subtrFanscnt,
          count(tvl.start_time) videoCnt,
          count(if(tvl.if_follow=1,1,null))-count(if(tvl.if_follow=2,1,null)) addFansMonth 
     from tb_user_video_log tvl join tb_video_info tvi using(video_id) 
    where year(tvl.start_time) = 2021 
    group by tvi.author,date_format(tvl.start_time,'%Y-%m')  
    ) A 
    order by author,total_fans ; 
    */

     select tvi.author,
          date_format(tvl.start_time,'%Y-%m') month ,
          round((count(if(tvl.if_follow=1,1,null))-count(if(tvl.if_follow=2,1,null)))/count(tvl.start_time),3) fans_growth_rate, 
          sum(count(if(tvl.if_follow=1,1,null))-count(if(tvl.if_follow=2,1,null))) 
          over(partition by tvi.author order by date_format(tvl.start_time,'%Y-%m')) total_fans 
     from tb_user_video_log tvl join tb_video_info tvi using(video_id) 
    where year(tvl.start_time) = 2021 
    group by tvi.author,date_format(tvl.start_time,'%Y-%m') 
    order by tvi.author,total_fans ;