SQL159 每个创作者每月的涨粉率及截止当前的总粉丝量

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;

1. 内层子查询 (t_author_monthly_fans_play_cnt)：这个子查询首先从tb_user_video_log表和tb_video_info表中选择数据，通过video_id列进行连接。然后，它使用DATE_FORMAT函数来格式化start_time列为"YYYY-MM"格式的月份。接着，它计算每个作者每个月的粉丝增加数量（fans_add_cnt）和播放次数（play_cnt）。粉丝增加数量是通过if_follow列来判断的，如果if_follow等于2，则表示减少一个粉丝，否则表示增加一个粉丝。播放次数是通过计算每个月的记录数来得到的。最后，它按作者和月份进行分组。

2. 外层查询：外层查询使用内层子查询的结果，并使用ROUND函数来计算粉丝增长率（fans_growth_rate），这是通过将fans_add_cnt除以play_cnt并四舍五入到小数点后三位来计算的。然后，它使用SUM窗口函数和OVER子句来计算每个作者每月的累计粉丝数（total_fans），这是通过PARTITION BY author按作者分区并在ORDER BY month``的月份顺序上计算的累积和。

3. 排序：最后，查询使用ORDER BY author, total_fans来按作者和累计粉丝数进行排序。

整个查询的逻辑是先计算出每个作者在2021年每个月的粉丝增加数量和播放次数，然后计算出每个作者的粉丝增长率和累计粉丝数，并按照作者和累计粉丝数进行排序。