涨粉率计算

• 注意点
  • 涨粉的定义及时点，定义已给出，默认end_time为点击follow的时间点（涨粉的时点）
  • 粉丝增长计算中，2代表-1，if判断即可
  • 当前粉丝量是累计计算，使用sum(粉丝增长量) over (partition by 作者id order 月份)
  • 返回值中Month是带有年份的，因此在按作者id和月份分组计算粉丝增长时，使用max(end_time)保留日期字段；在最后返回时，使用date_format()截取时间即可
  • 最后按author、粉丝总量进行升序

select
    t.author,
    date_format(t.dt, '%Y-%m') as month,
    round(t.fans_growth_cnt / t.play_cnt, 3) as fans_growth_rate,
    sum(t.fans_growth_cnt) over (partition by t.author order by month(t.dt)) as total_fans
from (
    select
        tvi.author,
        max(tuvl.end_time) as dt,
        sum(if(tuvl.if_follow = 2, -1, tuvl.if_follow)) as fans_growth_cnt,
        count(tuvl.video_id) as play_cnt
    from tb_user_video_log as tuvl
        inner join tb_video_info as tvi on tuvl.video_id = tvi.video_id
    where year(tuvl.end_time) = 2021
    group by tvi.author, month(tuvl.end_time)
) as t
order by author, total_fans
;