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

SELECT author
     , DATE_FORMAT(start_time,'%Y-%m')                                                   AS month
     , ROUND(SUM(CASE if_follow WHEN 1 THEN 1
                                WHEN 2 THEN -1
                                       ELSE 0
                 END) / COUNT(start_time),3)                                             AS fans_growth_rate
     #计算出每个月涨粉率，用CASE WHEN把2转换成-1，然后相加就计算出了每月粉丝量，count(start_time)计算出每月播放量，不会计算NULL值
     , SUM(SUM(CASE if_follow WHEN 1 THEN 1
                              WHEN 2 THEN -1
                                     ELSE 0
               END)) OVER (PARTITION BY author ORDER BY DATE_FORMAT(start_time,'%Y-%m')) AS total_fans
     #用sum()over()计算出每人每月总粉丝量，这里可以用到上面的每月粉丝量
FROM tb_user_video_log JOIN tb_video_info USING (video_id)
WHERE YEAR(start_time) = '2021'
GROUP BY author, DATE_FORMAT(start_time,'%Y-%m')
ORDER BY author, total_fans;