问题切分

筛选条件：2021年

要求的是：每个创作者每月的涨粉率及截止当月的总粉丝量

隐藏条件：涨粉率保留3位小数，结果按创作者ID、总粉丝量升序排序

重点问题

怎么求每个创作者每月的涨粉率？

首先group by author,substring(start_time,1,7)

涨粉率=(加粉量 - 掉粉量) / 播放量

加粉量-掉粉量：sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end)

播放量：count(start_time)

怎么求截止当月的总粉丝量？

求累计想到聚合窗口函数，这里是先聚合到月，再开窗：sum(xxx) over(partition by author order by substring(start_time,1,7))

整合代码

select

    author,

    month,

    fans_growth_rate,

    sum(fans) over(partition by author order by month) total_fans

from

    (select

        author,

        substring(start_time,1,7) month,

        round(sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end)/count(start_time),3) fans_growth_rate,

        sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end) fans

    from

        tb_user_video_log tu left join tb_video_info tv

        on tu.video_id = tv.video_id

    where year(start_time) = 2021

    group by 1,2) a

order by 1,4