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

题目详情见牛客

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

思路题解

SELECT
    t2.author,
    t2.月份,
    t2.fans_growth_rate,
    sum(t2.每月总的涨粉量) over (PARTITION BY t2.author ORDER BY t2.月份) AS "总粉丝数目" 
FROM
    (
    SELECT
        t1.author,
        t1.月份,
        ROUND(SUM(CASE t1.点关注 WHEN 1 THEN 1 WHEN 2 THEN - 1 ELSE NULL END) / COUNT(t1.点关注), 3) AS "fans_growth_rate",
        SUM(CASE t1.点关注 WHEN 1 THEN 1 WHEN 2 THEN - 1 ELSE NULL END) AS "每月总的涨粉量" 
    FROM
        (
        SELECT
            a.author,
            b.if_follow AS "点关注",
            DATE_FORMAT(b.start_time, '%Y-%m') AS "月份" 
        FROM
            tb_video_info AS a
            LEFT JOIN tb_user_video_log AS b ON a.video_id = b.video_id 
        WHERE
            YEAR(b.start_time) = 2021 
        ) AS t1 
    GROUP BY
        t1.author,
        t1.月份 
    ) AS t2 
ORDER BY
    t2.author ASC, t2.月份 ASC;

1 第一步 找到21年的数据

 SELECT
            a.author,
            b.if_follow AS "点关注",
            DATE_FORMAT(b.start_time, '%Y-%m') AS "月份" 
        FROM
            tb_video_info AS a
            LEFT JOIN tb_user_video_log AS b ON a.video_id = b.video_id 
        WHERE
            YEAR(b.start_time) = 2021 

第二步 把上一段得到的表作为t1，找到21年的每个月的涨粉率和每月总的涨粉量

（第一步和第二步是可以的合并，这里为了思路解析清晰）

SELECT
        t1.author,
        t1.月份,
        ROUND(SUM(CASE t1.点关注 WHEN 1 THEN 1 WHEN 2 THEN - 1 ELSE NULL END) / COUNT(t1.点关注), 3) AS "fans_growth_rate",
        SUM(CASE t1.点关注 WHEN 1 THEN 1 WHEN 2 THEN - 1 ELSE NULL END) AS "每月总的涨粉量" 
    FROM t1
    GROUP BY
        t1.author,
        t1.月份 

第三步，继续把上段表看作t2，考察窗口函数累计问题

SELECT
    t2.author,
    t2.月份,
    t2.fans_growth_rate,
    sum(t2.每月总的涨粉量) over (PARTITION BY t2.author ORDER BY t2.月份) AS "总粉丝数目" 
FROM t2 
ORDER BY
    t2.author ASC, t2.月份 ASC;

这里核心是把t2.每月总的涨粉量按照每个作者分组，进行每个月的顺序累加

sum(t2.每月总的涨粉量) over (PARTITION BY t2.author ORDER BY t2.月份) AS    "总粉丝数目"

|窗口函数详情知识点参考++++》猴子分析| |