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

select 
    v.author,
    date_format(u.start_time, '%Y-%m')  as month,
    round(sum(if(if_follow=1, 1, if(if_follow = 2, -1, 0))) / count(u.uid), 3) as fans_growth_rate,
    sum(sum(if(if_follow=1, 1, if(if_follow = 2, -1, 0)))) over (partition by v.author order by date_format(u.start_time, '%Y-%m')) as total_fans
from
    tb_user_video_log u 
    left join tb_video_info v on u.video_id = v.video_id
where 
    year(u.start_time) = '2021' and
    year(u.end_time) = '2021'
group by 
    v.author,
    date_format(u.start_time, '%Y-%m')
order by 
    v.author,
    total_fans

sum(列名) over() 实现累加