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

SELECT
    author,
    month,
    ROUND(fans_growth_rate, 3) AS fans_growth_rate,
    SUM(sum_fans) OVER(PARTITION BY author ORDER BY month) AS total_fans
FROM(
    SELECT
        author,
        DATE_FORMAT(end_time, '%Y-%m') AS month,
        AVG(
            CASE
            WHEN if_follow = 1 THEN 1
            WHEN if_follow = 0 THEN 0
            ELSE -1
        END) AS fans_growth_rate,
        SUM(
            CASE
            WHEN if_follow = 1 THEN 1
            WHEN if_follow = 0 THEN 0
            ELSE -1
        END) AS sum_fans
    FROM tb_user_video_log t1
    JOIN tb_video_info t2 ON t1.video_id = t2.video_id
    WHERE YEAR(end_time) = '2021'
    GROUP BY author, month
) t
ORDER BY author, total_fans

关键在于如何累加每个月的粉丝量，这需要使用到SUM+窗口函数。但是SUM内的又应该是已经按照author和month分组汇总好的每个月的粉丝增量，这会非常绕，所以最好将这一部分分组汇总的操作放在子查询中完成，思路会清晰一些。