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

# # 
# select tb_video_info.author,month,fans_growth_rate,total_fans

# # 粉丝总数
# count(tb_user_video_log.if_follow)


# # 关联表信息
# join tb_video_info using(video_id)

# # 筛选2021年记录
# where Year(start_time) = 2021

# # 计算每个创作者每月的涨粉量和播放量
# ## 按创作者和月份分组
# group by author, Date_format(start_time, "%Y-%m")

# ## 计算涨粉量，特殊情况if_follow = 2表示掉粉
# sum(if (if_follow = 2, -1, if_follow)) as fans_add_cnt

# ## 计算播放量(每条记录就是一次播放)
# count(1) as play_cnt

# # 计算每个创作者每个月的涨粉量和播放量
# ## 计算涨粉率， 保留3个小数： 
# round(fans_add_cnt/ play_cnt, 3)
# ## 计算当月的总粉丝量（累积求和）：
# sum(fans_add_cnt) over(partition by author order by month)



select author,`month`,round(fans_add_cnt / play_cnt,3) as fans_growth_rate,
        sum(fans_add_cnt) over (partition by author order by month) as total_fans
from (
    select author, date_format(start_time, "%Y-%m") as `month`,
            sum(if(if_follow = 2, -1, if_follow)) as fans_add_cnt,
            count(1) as play_cnt
    from tb_user_video_log
    join tb_video_info using(video_id)

    where Year(start_time) = 2021
    group by author, `month`
) as t_author_monthly_fans_play_cnt
order by author, total_fans;

这是人能想出来的事？？