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

select author,month,
       round(sum(if_follw_new)/count(1),3) as fans_growth_rate,
       cumlate_fans as total_fans
from
(select author,
       substring_index(start_time,'-',2) as month,
        if(if_follow=1 or if_follow=0,if_follow,-1) as if_follw_new,
        sum(if(if_follow=1 or if_follow=0,if_follow,-1))over(partition by author order by substring_index(start_time,'-',2)) as cumlate_fans
from tb_user_video_log tuvl
    join tb_video_info
    using(video_id)
where year(end_time)='2021') t
group by author,month,cumlate_fans
order by author,cumlate_fans

我们先对原表进行处理得到图中所示表，利用sum窗口函数有累加的特性可以得到每个作者的不同月份累积粉丝