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

select
author
,month
,fans_growth_rate
,sum(inc_fo) over(partition by author order by month) total_fans
from
(
    select
    author
    ,left(start_time,7) month
    ,round(sum(inc_fo) / count(1),3) fans_growth_rate
    ,round(sum(inc_fo),3) inc_fo
    from
    (
        select 
        log.video_id video_id
        ,start_time
        ,author
        ,case when if_follow = 1 then 1
            when if_follow = 0 then 0
            when if_follow = 2 then -1
        else -100000 end inc_fo
        from tb_user_video_log log 
        left join tb_video_info info 
        on log.video_id = info.video_id
    ) a
    group by 1,2
) b
where left(month,4) = '2021'
order by 1,4

我们的目的是分析2021年各位作者的粉丝增长情况。通过计算每个月的粉丝增长率以及累计粉丝数，我们能够清楚地看到每位作者在2021年各月的粉丝动态变化。

首先，代码的核心在于从数据库中获取与作者、视频相关的数据，并通过一系列操作最终得到作者的月度粉丝增长率和累计粉丝数。这些数据主要来自两个表：tb_user_video_log 和 tb_video_info。

代码的主要逻辑：

1. 数据提取和处理：首先，从 tb_user_video_log 表中提取 video_id、start_time 和 author。通过 CASE 语句，将 if_follow 字段的值转换为增减粉丝数的指标 inc_fo，其中：if_follow = 1 表示粉丝增加，因此 inc_fo 为 1。if_follow = 0 表示没有变化，inc_fo 为 0。if_follow = 2 表示粉丝减少，inc_fo 为 -1。其他异常情况设定为一个极小值，用于标记无效数据。
2. 数据聚合：将上述结果按作者和月份分组，计算出每个月的粉丝增长总数 inc_fo，并进一步计算平均粉丝增长率 fans_growth_rate，保留三位小数。
3. 窗口函数：关键部分是使用了一个窗口函数 sum(inc_fo) over(partition by author order by month)，它累积计算每位作者在每个月的总粉丝数，并按月份顺序累加。这部分是代码的核心，帮助我们跟踪作者的粉丝数随着时间的变化。
4. 数据过滤和排序：最后，只保留2021年的数据，并按作者和累计粉丝数进行排序，方便我们观察各个作者的表现。

总体来说，这段代码通过窗口函数和聚合操作，详细计算了2021年各位作者的粉丝增长情况，提供了一个清晰的粉丝增长趋势。