一、明确需求

计算2021年每个创作者每月的涨粉率，截至当月的总粉丝量

二、需求分解

1. 互动表选择时间2021年，增加字段：年-月
2. 联结信息表info,根据author,年月字段分组，计算加粉量、掉粉量、播放量
3. 根据加粉量、掉粉量、播放量计算涨粉率、运用sum()窗口函数计算累计粉丝数

三、实现代码

select  author
    , month
    , round((plus_f - sub_f) / bf_num,3) fans_growth_rate
    , sum(plus_f - sub_f) over(partition by author order by month asc
                        rows between unbounded preceding and current row) total_fans
from
    (
    select a.author
        , b.m month 
        , count(if(b.if_follow = 1,b.uid,null)) plus_f    # 涨粉量
        , count(if(b.if_follow = 2,b.uid,null)) sub_f    # 掉粉量
        , count(*) bf_num    # 播放量
    from (select distinct * from tb_video_info) a
    left join (select distinct *,date_format(start_time,'%Y-%m')m 
                from tb_user_video_log
                where year(start_time) = '2021') b
        on a.video_id = b.video_id
    group by a.author,b.m
    )x
where month is not null      #剔除没有互动记录的创作者
order by author asc,total_fans asc

四、拓展阅读

窗口函数sum(col) over(partition by col1 order by col2 [range][rows])

• 按值累加range:默认累加方式，按order by 后的col2为标准进行排序累加，相同col2 和sum(col)结果相同

以本题为例：
sum(plus_f - sub_f) over(partition by author order by month asc [range between unbounded preceding and current row])
[range ……]部分可省略

• 按行累加rows:按order by col2为标准进行排序累加，一行一行累加

# 以本题为例，按行累加
sum(plus_f - sub_f) over(partition by author order by month asc rows between unbounded preceding and current row)

因为本题中我先进行了author，month的分组，所以记录都是一个author，month就一行，按值加总和按行加总结果相同

推荐阅读:sum()窗口函数: