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

求叠加和的窗口函数并不需要单独的聚合，因此这里需要聚合之后再做嵌套。

select t2.author, 
date_format(t1.start_time,'%Y-%m') as mon,
round(sum(case when t1.if_follow = 2 then -1 else t1.if_follow end) / count(1), 3),
sum(sum(case when t1.if_follow = 2 then -1 else t1.if_follow end)) over(partition by t2.author order by date_format(t1.start_time,'%Y-%m'))
from tb_user_video_log t1
join tb_video_info t2 
on t1.video_id = t2.video_id
where year(end_time) = 2021
group by 1,2 
order by 1,4