题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
http://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
首先计算出每个作者每个月的新增的粉丝数量，count(tu.if_follow)计算出tb_user_video_log表中所有的播放量，综合算出涨粉率
select
tv.author
,DATE_FORMAT(tu.end_time,"%Y-%m") month
,round((sum(case when tu.if_follow = 1 then 1 when tu.if_follow = 2 then -1 else 0 end) / count(tu.if_follow)),3) fans_growth_rate
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
group by tv.author, month
比较难的是计算出当前的粉丝量，结合sum() over()来进行计算，将当前的新粉丝和之前的粉丝结合起来，变成总的粉丝数量
sum(sum(case when tu.if_follow = 1 then 1 when tu.if_follow = 2 then -1 else 0 end)) over(partition by tv.author order by DATE_FORMAT(tu.end_time,"%Y-%m"))
将sql结合起来，先过滤出2021年的数据，并根据author和total_fans进行排序，不这样排序过不了。。。
select
tv.author
,DATE_FORMAT(tu.end_time,"%Y-%m") month
,round((sum(case when tu.if_follow = 1 then 1 when tu.if_follow = 2 then -1 else 0 end) / count(tu.if_follow)),3) fans_growth_rate
,sum(sum(case when tu.if_follow = 1 then 1 when tu.if_follow = 2 then -1 else 0 end)) over(partition by tv.author order by DATE_FORMAT(tu.end_time,"%Y-%m")) total_fans
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
where DATE_FORMAT(tu.end_time,"%Y") = '2021'
group by tv.author, month
order by tv.author, total_fans
