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

# 字段：author、month、每月涨粉率fans_growth_rate、total_fans
# # tb1：连接
with tb1 as(
    select uid,author,date_format(start_time,'%Y-%m') as start_date,if_follow,
    # 增加关注字段
    if(if_follow = 2,-1,if_follow) as follow
    # 播放量
    from tb_user_video_log left join tb_video_info using(video_id)
    where year(start_time) = 2021
)
# tb2：
# 涨粉率 = (加粉量-掉粉量)/播放量
# tb2 as(
    select author,start_date,
    # count(if(follow=1,follow,null)),count(if(follow=-1,follow,null)),count(follow),
    round((count(if(follow=1,follow,null))-count(if(follow=-1,follow,null)))/count(follow),3) as fans_growth_rate,
    # 总粉丝数
    sum(sum(follow)) over(partition by author order by start_date) as total_fans

    from tb1
    group by author,start_date
    order by author , sum(sum(follow)) over(partition by author order by start_date) 
# )

# tb3：求取累计粉丝数， 需要解决sum()over()和group by的共存问题
# select distinct author,start_date,fans_growth_rate,
# sum(follow)over(partition by author order by start_date) as total_fans
# from tb1 left join tb2 using(author,start_date)






# SELECT author, date_format(start_time,'%Y-%m') month,
# # 涨粉率
# round(sum(case when if_follow=1 then 1
#                when if_follow=2 then -1
#                else 0 end)/count(author),3) fans_growth_rate,

# sum(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)) # follow//
# over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
# FROM tb_user_video_log log 
# left join tb_video_info info on log.video_id=info.video_id
# where year(start_time)=2021
# group by author,month
# order by author,total_fans  

# 这个版本可以，是因为sum()over()内部已经聚合了一个sum

# 注意点：“
# 1. sum()over(partition by )和group by 是可以共存的，但是前者内部需要一个聚合函数，比如本题就是内部存在一个sum预先聚合
# 2. group by 执行顺序先于 sum()over(partition by)