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

SELECT
    author,
    month,
    fans_growth_rate,
    SUM(fans_cnt) OVER(PARTITION BY author ORDER BY month) AS total_fans # SUM窗口函数
FROM(
SELECT # 先把fans_growth_rate计算出来，这里我先把每个月的新增粉丝量给计算出来了
    author,
    DATE_FORMAT(end_time, '%Y-%m') AS month,
    ROUND(SUM(IF(if_follow = 2, -1, if_follow)) / COUNT(if_follow), 3) AS fans_growth_rate,
    SUM(IF(if_follow = 2, -1, if_follow)) AS fans_cnt # 每月新增粉丝
FROM tb_user_video_log tu
JOIN tb_video_info tv
ON tu.video_id = tv.video_id
WHERE YEAR(start_time) = 2021 #条件计算2021年
GROUP BY author, month
) AS tb1
ORDER BY author, total_fans #隐藏条件