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

# 涨粉率 = (加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
# 2021 每月的涨粉， 截至当月的总粉丝量
# sum(case uv.if_follow when 1 then 1 when 2 then -1 end) over (partition by vi.author order by month(uv.start_time)) as fans_change
# 以人为窗口，按照月份排序，
# 构造字段，粉丝变化数
# 每个作者每月的涨粉， 截至当月的总粉丝数
# 每人每月  partition by vi.author order by date_format(uv.start_time, "%Y%m") 

select  distinct
    vi.author
    , date_format(uv.start_time, "%Y-%m") as `month`
    , round(avg(case uv.if_follow when 1 then 1 when 2 then -1 else 0 end) over (partition by vi.author, month(uv.start_time)), 3) as fans_growth_rate
    , sum(case uv.if_follow when 1 then 1 when 2 then -1 end) over (partition by vi.author order by month(uv.start_time)) as total_fans
from tb_video_info as vi 
left join tb_user_video_log as uv on vi.video_id = uv.video_id
where year(uv.start_time) = 2021
order by vi.author, total_fans