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

SELECT 
author,
date_format(start_time,'%Y-%m') month,
round(sum(IF(if_follow=2,-1,if_follow))/count(author),3) fans_growth_rate,
sum(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 t1 LEFT join tb_video_info t2 
on t1.video_id = t2.video_id
where YEAR(start_time) = 2021
GROUP BY author,month
ORDER BY author,total_fans
#直接使用if来判断,然后用窗口函数统计