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

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

• 利用avg配合if求涨粉率
• 利用两层sum
• 第一层是窗口函数，用于累加
• 第二层用于汇总每个月的情况
• 注意时间的筛选条件