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

WITH t1 AS (
    SELECT tv.author,
           SUBSTR(start_time,1,7) AS month,
           CASE WHEN if_follow = 0 then 0
               WHEN if_follow = 1 then 1
             WHEN if_follow = 2 then -1
            END AS fens,
           COUNT(author) OVER (PARTITION BY author,SUBSTR(start_time,1,7)) AS bofangliang
    FROM tb_user_video_log tu
    LEFT JOIN tb_video_info tv
    ON tu.video_id = tv.video_id
    WHERE SUBSTR(start_time,1,4) = '2021'
    ORDER BY month ASC
),
t2 AS (
    SELECT author,
           month,
           ROUND(sum(fens) / bofangliang,3) AS fans_growth_rate,
           SUM(fens) fans_month
           FROM t1
    GROUP BY author, month
),
t3 AS (
    SELECT
        author,
        month,
        fans_growth_rate,
        sum(fans_month) OVER (PARTITION BY author ORDER BY author,month ROWS UNBOUNDED PRECEDING) AS total_fans
    FROM t2
)
SELECT * FROM t3
ORDER BY author ASC,total_fans ASC;

# 关键在于自定义窗口函数的 PARTITION BY author