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

难点主要在于计算截止当前的总粉丝量。这一步要使用窗口函数来完成

sum(<column>) over(partition by <partition column> order by <order column>)

• <column>：要计算总和的列名
• partition by <partition column>：按照指定的列名进行分区，将数据划分为多个子集，每个子集内的行均有相同的分区键值。在窗口函数中，分区是用来限制透过窗口函数处理的范围的。
• order by <order column>：按照指定的列名进行排序，控制窗口函数计算总和时的顺序。

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(*),
        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
    JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id
WHERE YEAR(start_time)=2021
GROUP BY
    author,
    month
ORDER BY
    author,total_fans ASC,fans_growth_rate DESC