/*计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
解题：1.首先，明确涨粉率的定义，将日期进行整理，对应后group
2. 总粉丝量，注意是每个月累计的状态，一般来说是各个月份的累加减去掉粉数，所以运用窗口函数+order by 实现滚动累和
3. 题目中要求日期是2021年，需要明确start_time内的year（）；如果是已经date_format后的mon字段，可以使用字符串提取参数进行选择*/ 
select author,mon,round((add_fan-sub_fan)/move,3) as rate, fan_amount
from(
    select i.author,date_format(start_time,'%Y-%m')as mon
    ,sum(if(if_follow=1,1,0))as add_fan,sum(if(if_follow=2,1,0))as sub_fan
    ,count(start_time) as move
    ,sum(SUM(case when if_follow=1 then 1 
                  when if_follow=2 then -1
                  else 0 end)
        ) over(partition by i.author order by date_format(start_time,'%Y-%m') ) as fan_amount
    from tb_user_video_log t 
    left join tb_video_info i on t.video_id = i.video_id
    where year(t.start_time)= 2021
    group by i.author,mon
)a
order by author,fan_amount