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

select
tvi.author,
date_format(tuvl.start_time, "%Y-%m") as month,
round(sum(case when tuvl.if_follow=1 then 1 when tuvl.if_follow=2 then -1 end)/count(tuvl.if_follow), 3) as fans_growth_rate,
sum(sum(case when tuvl.if_follow=1 then 1 when tuvl.if_follow=2 then -1 end)) over(partition by tvi.author order by date_format(tuvl.start_time, "%Y-%m") asc) as total_fans
from
tb_video_info as tvi
inner join
tb_user_video_log as tuvl
on tvi.video_id=tuvl.video_id
where year(tuvl.start_time)=2021
group by tvi.author, date_format(tuvl.start_time, "%Y-%m")
order by tvi.author, total_fans

难点在于总粉丝量的累计计算。在这记录一下第五行为什么使用两个sum。

我们首先知道在使用over()窗口函数开窗时，可以根据partition by分区来计算我们想要的值，partition by如果用了order by排序，那他使用的聚合函数sum(), max(), min()等会变成累计求和，累计最大值，累计最小值，没用order by排序，则和group by分组的效果是一样的，只不过group by会改变行数，一个类别只有一行，partition by不会改变行数，为每一行保留计算下来的值。这里贴个结合实例学习的链接(https://zhuanlan.zhihu.com/p/150812199 ) 供学习巩固一下。

弄懂了partition by和group by的区别联系后，再理一下sql的执行顺序。

from > join > where > group by > having > select > distinct > order by > limit

一个窗口函数所考虑的行属于那些通过查询的FROM子句产生并通过WHERE、GROUP BY、HAVING过滤的“虚拟表”。

窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中。它们不允许出现在其他地方，例如GROUP BY、HAVING和WHERE子句中。这是因为窗口函数的执行逻辑是在处理完这些子句之后。

所以实际上窗口函数就是在group by分组having完后的结果集上去开窗分区计算。

现在分析为什么用两个sum。

sum(sum(case when tuvl.if_follow=1 then 1 when tuvl.if_follow=2 then -1 end)) over(partition by tvi.author order by date_format(tuvl.start_time, "%Y-%m") asc) as total_fans

把date_format(tuvl.start_time, "%Y-%m")记为month，将他拆开变成

1. sum() over(partition by tvi.author order by month asc) as total_fans
2. sum(case when tuvl.if_follow=1 then 1 when tuvl.if_follow=2 then -1 end)

经过group by后的分组就是红框内的数据，然后在这个结果集上做开窗分区（虚线框内的数据都被group by分组为"一行"），所以，

第一个sum就是按author分区，将month以正序排序做累计求和。

第二个sum就是正常sum求和，根据case when计算出当月的涨粉量。