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

#需求：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
#输出：创作者ID、月份、涨粉率、截至当月总粉丝量
#要求：涨粉率=(加粉量-掉粉量)/播放量，保留三位小数；输出按创作者ID、总粉丝量升序；时间限制为2021年；if_follow=1为涨粉，if_follow=2为掉粉
#思路：子查询两张表，分别计算涨粉和掉粉量，union，再链接tb_video_info，然后group by video_id,date_format(end_time,'%Y-%m') month;或者用case when 判定if_follow，赋值1或-1
select author,tt1 'month',round(tt2/tt3,3) fans_growth_rate,
sum(tt2)over(partition by author order by tt1) total_fans
from(
  select author,date_format(end_time,'%Y-%m') tt1,sum(if(if_follow=2,-1,if_follow)) tt2,count(*) tt3
  from tb_user_video_log join tb_video_info using(video_id)
  where year(end_time)='2021'
  group by 1,2
) t1
order by 1,4
#总结：在case when中，需要判定的只有if_follow=2的情况，其他的都可以返回原值；
#且case写在子查询中时，核查要group by才不会报错，但返回的值不对，所以这里改为sum(if())更直观有效；
#难点在于截至到month时间的总粉丝量，需要用窗口函数，对tt2求和，按tt1进行累加