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

select distinct author,date_format(start_time,'%Y-%m') month,
round(avg(if(if_follow=2,-1,if_follow))over(partition by author,date_format(start_time,'%Y-%m') ),3) fans_grouwth_rate,
sum(if(if_follow=2,-1,if_follow))over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
from tb_user_video_log l left join tb_video_info i
on l.video_id=i.video_id
where year(start_time)=2021 and year(end_time)=2021 
order by author,total_fans,month

窗口函数+left join+if

1.利用video_id将两张表连接

2.利用窗口函数对author和month进行分组，通过avg+if求分组内的fans_growth_rate

3.利用窗口函数对author分组，再利用order by求分组累加粉丝数目

4.最后调整顺序（order by）和输出(distinct)