SUMIF条件转换 & SUM开窗函数累加

核心考点：

1.粉丝数变化计算（SUMIF条件转换）：

对if_follow进行转换，2表示取消关注，所以需要处理为-1，涨粉+1、无变化0正常输出。对转换后的if_follow进行SUM累加即可得到粉丝数变化fans_diff

2.粉丝数的累加:

利用SUM窗口函数对fans_diff，按author维度，month升序，进行累加。

代码如下：

SELECT
  author,
  month,
  fans_growth_rate,
  SUM(fans_diff) OVER (PARTITION BY author ORDER BY month) total_fans
FROM (
  SELECT
    author,
    DATE_FORMAT(start_time, '%Y-%m') month,
    SUM(IF(if_follow = 2, -1, if_follow)) fans_diff,
    ROUND(SUM(IF(if_follow = 2, -1, if_follow)) / COUNT(*), 3) fans_growth_rate
  FROM tb_video_info
  LEFT JOIN tb_user_video_log USING(video_id)
  WHERE YEAR(start_time) = 2021
  GROUP BY 1,2) t
ORDER BY 1,4

P.S:

一次SUM也可以通过的。 上面的代码逻辑更清晰，可读性更强，多次测试下实际性能表现也比1次SUM的方案更好。 在此也附上一次SUM的版本，大家可以自行测试下。

SELECT 
  author, 
  DATE_FORMAT(start_time, '%Y-%m') month, 
  ROUND(SUM(IF(if_follow = 2, -1, if_follow)) / COUNT(*), 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_video_info 
LEFT JOIN tb_user_video_log USING(video_id) 
WHERE YEAR(start_time) = 2021 
GROUP BY 1,2 
ORDER BY 1,4