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

select 
    b.author as author,
    date_format(a.start_time,'%Y-%m') as month,
    round(sum(case 
                when a.if_follow = 1 then 1
                when a.if_follow = 2 then -1 else 0 end)/
    count(a.start_time),3) as 	fans_growth_rate,
    sum(sum(case 
                when a.if_follow = 1 then 1
                when a.if_follow = 2 then -1 else 0 end))over(partition by author order by date_format(a.start_time,'%Y-%m')) as total_fans

from
    tb_user_video_log a left join tb_video_info b 
    on a.video_id = b.video_id
where 
    year(start_time) = 2021
group by 
    b.author,
    date_format(a.start_time,'%Y-%m')
order by author,total_fans

sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

sum开窗函数注意点：

1、开窗但是不排序：sum([distinct] <expr>) over ([partition_clause])

返回当前窗口累计汇总值，同一个窗口内的sum开窗值是相同的

2、开窗也排序：sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

返回当前窗口从开始行到当前行的累计汇总值