题解|分组求和注意|每个创作者每月的涨粉率及截止当前总粉丝量

## 涨粉率 = (加粉量 - 掉粉量)/播放量

SELECT
    author,
    DATE_FORMAT(start_time,'%Y-%m') AS month,
    ROUND((SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0 END ))/COUNT(start_time),3)
    AS fans_growth_rate,
    SUM(SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0 END)) OVER(PARTITION BY author 
    ORDER BY DATE_FORMAT(start_time,'%Y-%m')) AS total_fans
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE YEAR(start_time) = 2021
GROUP BY author, month
ORDER BY author, total_fans

### total_fans也要用到窗口函数！！！！ 
### 直接写SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0 END) AS total_fans然后按照下面GROUP BY的分组，其实只是对每个月的粉丝量进行了求和。
### 但是题目要求是截止目前的累计粉丝数，就要用到窗口函数进行累加了。
### 简单来说，第一个sum是对各个月的求和，第二个sum是对多个月的累计求和。