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

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

# 匹配视频播放表和作者名，并且计算出粉丝增长情况
with new_table as(
select author,
       u.video_id,
       date_format(start_time,"%Y-%m") as month,
       if_follow,
       (case when if_follow = 1 then 1 
             when if_follow = 2 then -1 
             else 0 end) as fans # 涨粉+1，掉粉-1
from tb_user_video_log u
join tb_video_info v 
on u.video_id = v.video_id
where year(start_time) = 2021
)

# 涨粉率,就是是涨粉量/播放量：round(sum(fans)/count(*),3) as fans_growth_rate

# 每个月的总粉丝量：sum(fans)
# 截止当月的总粉丝量，用月份排序，往前累计， 用sum(每个月的总粉丝量) over (partition by ...)

select author,
       month,
       round(sum(fans)/count(*),3) as fans_growth_rate,
       sum(sum(fans)) over (partition by author order by month) as total_fans
from new_table
group by author,month
order by author, total_fans # 结果按创作者ID、总粉丝量升序排序。!!!