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

select author, month, fans_growth_rate, sum(delta_fans)over(partition by author order by month rows between unbounded preceding and current row) total_fans
from (select author,date_format(start_time,'%Y-%m') month,round(avg(case if_follow when 1 then 1 when 2 then -1 else 0 end),3) fans_growth_rate,sum(case if_follow when 1 then 1 when 2 then -1 else 0 end) delta_fans
from tb_user_video_log a left join tb_video_info b using(video_id)
where year(start_time)=2021
group by author,month) tab1
order by author,total_fans

累计问题