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

优化：减少粉丝量，增加粉丝量，粉丝量保持不变看似是三种情况，但是其实用if就行，当值为2时转为-1，为其他两种时保持原样
难点：目前的粉丝量=本月涨粉/降粉情况与历史月份粉丝量的累加，利用窗口函数SUM(__) OVER(PARTITION BY__ORDER BY__ )可以实现累加。窗口函数group by 按什么进行分类，order by在类目内对什么进行累加。
SELECT 
    author,
    DATE_FORMAT(start_time,'%Y-%m') AS month,
    ROUND(SUM(IF(if_follow =2, -1, if_follow))/COUNT(video_id),3) AS fans_growth_rate,
    SUM(SUM(IF(if_follow =2, -1, if_follow))) OVER(PARTITION BY author ORDER BY date_format(start_time,'%Y-%m')) AS total_fans
FROM tb_user_video_log u 
    join tb_video_info i using(video_id)
WHERE YEAR(start_time) = 2021
GROUP BY author, month
ORDER BY author,total_fans