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

select 
author,date_format(start_time,'%Y-%m') as month,
round((sum(case when if_follow =1 then 1 else 0 end) -
 sum(case when if_follow =2 then 1 else 0 end))/count(*),3) as fans_growth_rate,
 sum((sum(case when if_follow =1 then 1 else 0 end) -
 sum(case when if_follow =2 then 1 else 0 end))) 
 over(partition by author order by date_format(start_time,'%Y-%m') ) as total_fans
from tb_video_info join tb_user_video_log using(video_id)
where year(start_time) = 2021
group by author,date_format(start_time,'%Y-%m')
order by author,total_fans

重点是日期格式的准备
另外就是大量 case when 的计算

聚合窗口函数的使用，加入orderby后具有累计计算的能力。

[[分组计算]]