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

针对tb_user_video_log和tb_video_info进行取数，并对结果根据author和total_fans进行排序

select author,date_format(end_time,'%Y-%m') as month,round(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)/count(author),3) as fans_add_rate,sum(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)) over(partition by author order by date_format(end_time,'%Y-%m')) as total_fans from tb_user_video_log as vlog left join tb_video_info as vinfo on vinfo.video_id=vlog.video_id where year(end_time)=2021 group by author,month order by author,total_fans

• 获取年月信息，使用date_format获取年月括号中放时间和格式
• tb_user_video_log表中的if_follow中1表示新增一个关注，2 表示取消一个关注，将2变为-1方便运算

case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end

• 对上一条结果求和并除以author的数量得到增长率，此时表哥根据月份和作者进行分组，因此统计tb_user_video_log中的作者数量便可获取播放量
• 计算粉丝的累积数量，此处使用窗口函数OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
• over语法：OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )PARTITION BY 子句进行分组；ORDER BY 子句进行排序。窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。
• sum(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)) over(partition by author order by date_format(end_time,'%Y-%m'))over必须搭配其他函数使用，见上一条

select author,date_format(end_time,'%Y-%m') as month,round(sum(if(if_follow=2,-1,if_follow))/count(author),3) as fans_rate,sum(sum(if(if_follow=2,-1,if_follow))) over (partition by author order by date_format(end_time,'%Y-%m')) as total_fans from tb_user_video_log as uvlog left join tb_video_info as vinfo on uvlog.video_id=vinfo.video_id where year(end_time)=2021 group by author,month order by author,total_fans

计算粉丝数量方法不同