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

#涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
#if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。
#问题：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量


with main as(
    select author,mid(start_time,1,7) as month,
    count(if(if_follow=1,1,null)) follow_add,
    count(if(if_follow=2,1,null)) follow_sub,
    count(start_time) b
    from tb_user_video_log a
    inner join tb_video_info b
    using(video_id)
    where year(start_time)=2021
    group by author,month
)
select 
    author,
    month,
    round((follow_add-follow_sub)/b ,3) as fans_growth_rate,
    sum(follow_add-follow_sub) over(partition by author order by month) as total_fans
from main
order by author,total_fans

1.先写出每个作者每个月的涨粉数和降粉数,以及播放量 创建一个临时表

wtih tmp as(

select author,mid(start_time,1,7) month,

count(if(if_follow=1,1,null)) follow_add,

count(if(if_follow=2,1,null)) follow_sub,

count(start_time) b

from tb_user_video_log a

inner join tb_video_info b

using(video_id)

where year(start_time)=2021

group by author,month

)

2.算出涨粉率和截止每个月的总粉丝量(窗口函数)

select author,(follow_add-follow_sub)/b,sum(follow_add-follow_sub) over(partition by author order by month)

from tmp

order by author,total_fans