【SQL真题笔记】逐月累加问题与窗口函数

问题描述：

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量。

问题解析：

本问题的关键在于：一个视频创作者，从其发布第一个视频开始：

        第一个月的总粉丝数量为（新粉-掉粉）；

        而从第二个月开始，其总粉丝数就变成了（新粉-掉分+老粉）。

这其中，老粉的数量就为第一个月的总粉丝量。后面的月份以此类推。

关键知识：

MySQL从8.0开始支持窗口函数。

窗口究竟是什么意思呢？组几个词就能知道：时间窗口、窗口期……可见：

        窗口就是范围，即记录的集合；

        窗口函数就是在满足某种条件的记录集合上执行计算的函数。

对于窗口函数的详细描述，请参考https://www.jb51.net/article/229253.htm。

问题解决：

在本题中，在SQL中对于累加问题，我们首先想到sum()；那么直接用聚合函数套窗口函数，其形如：

sum(a)over(partition by x order by y)
//
over 来指定窗口的范围
partition by 来对表分组
order by 来对分组的结果排序

在本题中：a应为本月的（新粉-掉粉）；x应为author；y应为month。即：

        对于每个视频作者分一组（每个作者每月情况的记录放在一起，如a的所有月份的数据，b的所有月份的数据……），

        以月份排序这些分组（a的7,8,9月，b的7,8,9月……），

        再在这个排序的基础上逐月累加。

此外，在本题中，为了得到一列不同值的个数，我们用count(case when ... then 1 end)结构，即当满足when的条件时，count值才+1。

原题：

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

参考代码：

select author,month,round((x-y)/c,3) fans_growth_rate,
sum(x-y)over(partition by author order by month) total_fans
from(select author,date_format(start_time,'%Y-%m') 'month',
count(case when if_follow=1 then 1 end) x,count(case when if_follow=2 then 1 end) y,
count(tb_video_info.video_id) c
from tb_video_info left join tb_user_video_log
on tb_video_info.video_id=tb_user_video_log.video_id
where year(start_time)='2021'
group by author,month) t
order by author,total_fans;

最后

以上就是迷人航空为你收集整理的【SQL真题笔记】逐月累加问题与窗口函数的全部内容，希望文章能够帮你解决【SQL真题笔记】逐月累加问题与窗口函数所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。