sqlserver累计求和函数_SQL--⾼阶功能(窗⼝函数)
这篇⽂章我们来了解⼀下SQL⾼级功能(窗⼝函数):
1. 窗⼝函数
2. 专⽤的窗⼝函数
3. 专⽤窗⼝函数案例
4. 聚合函数窗⼝函数
5. 聚合函数窗⼝函数案例
6. 总结
1.窗⼝函数
什么是窗⼝函数?
在⽇常⼯作中,经常会遇到需要在组内排名这类的需求,这时候就需要使⽤SQL的⾼级功能窗⼝函数来解决问题啦。窗⼝函数也成为OLAP 函数(联机分析处理),可以对数据库进⾏实时的分析处理。
如何使⽤窗⼝函数?
因为窗⼝函数是对where⼦句或者group by ⼦句处理后的结果进⾏操作,所以窗⼝函数原则上只能写在select⼦句中。窗⼝基本语法如下:
select 字段,
<;窗⼝函数> over (partition by ⽤于分组的列名
order by ⽤于排序的列名)
窗⼝函数可以放两种函数:
1)专⽤窗⼝函数:rank、dense_rank、row_number等专⽤窗⼝函数
2)聚合函数:如sum,avg ,count,max,min等
2.专⽤窗⼝函数:
rank、dense_rank、row_number等专⽤窗⼝函数使⽤⽅法⼀样,功能区别是,当排序中出现并列名次时,⽐如,有三个第五名时:
rank函数会按照并列排名,并占⽤并列排名名次之后的位置:
5、5、5、8,这样前⾯出现的并列名次并不会影响后⾯⼈的真实名次;
dense_rank会在顺延并列名词之后的名词,同样的实例中,它会这样排序:
5、5、5、6,所有排序元素的名词都是顺序连贯的;
row_number排序中没有并列名词,按顺序连贯不重复的排序:
5、6、7、8;
举例体会⼀下⼏个函数的区别:
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dense_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
3.专⽤窗⼝函数案例
⾯试经典问题---Top N 问题
分组取每组最⼤值
select *
from score as a
where 成绩=(select max(成绩)
from score as b
where a.课程号=b.课程号)
分组取每组最⼩值
和取最⼤值⼀样,只是把函数max换成min
分数排名(如果两个分数相同,排名应相同,但是名词之间不应有间隔,应该是连续的整数)
select *
from (select *,dense_rank over (partition by 姓名 order by 成绩 desc) as dense_rank
from scores
按姓名分组,取每组最⼤的N条记录
select *
from (select *,row_number() over (partition by 姓名 order by 成绩 desc) as ranking
from score) as a
where ranking<n
4.聚合函数作为窗⼝函数:sum、count、average、max、min
聚和窗⼝函数常⽤的函数有sum、count、average、max、min等函数,和上⾯提到的专⽤窗⼝函数⽤法完全相同,只需要把聚合函数写在窗⼝函数的位置就可以了,但是函数后⾯括号⾥⾯不能为空,需要指定聚合的列名。
我们举例来看⼀下窗⼝函数是聚合函数的情况:
select *,
count(成绩) over (order by 姓名) as count_current,
sum(成绩) over(order by 姓名) as sum_current,
avg(成绩) over (order by 姓名) as avg_current,
rank函数怎么用
max(成绩) over (order by 姓名) as current_max,
min(成绩) over (order by 姓名) as current_min
from 班级表
以sum函数举例说明⼀下:
聚合函数sum在窗⼝函数中,是累加到⾃⾝的数据进⾏求和的结果。⽐如0004号,在使⽤sum窗⼝函
数后的结果,是对0001~0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。
⽐如0005号后⾯的聚合窗⼝函数结果是:学号0001~0005五⼈成绩的总和、平均、计数,最⼤值和最⼩值。
如果想要知道所有⼈成绩的总和、平均等聚合结果,看最后⼀⾏即可。
聚合窗⼝函数有什么作⽤呢?
聚合函数作为窗⼝函数,可以在每⼀⾏的数据⾥直观的看到,截⽌到本⾏数据,统计数据是多少(最⼤值、最⼩值等)。同时可以看出每⼀⾏数据,对整体统计数据的影响。
5.聚合函数窗⼝函数案例:
查单科成绩⾼于该科⽬平均成绩的学⽣名单
由于是在单科成绩内作⽐较,所以要⽤分组,但是如果使⽤group by分组,则每组只返回⼀⾏数据,不符合题⽬要求,所以应该⽤partition 窗⼝函数分组,同时,考虑到sql运⾏顺序是先from ,where 到select,故需要加⼀个关联⼦查询,代码如下:
select *
from(select *,avg(成绩) over(partition by 科⽬ ) as avg_score
from 成绩表)  as b
where 成绩>avg_score
窗⼝函数的移动平均
在聚合函数avg()的窗⼝函数中,看到的是从第⼀个数据到⾃⾝的移动平均值,⽐如刚刚看到的这个例⼦,
select *,
count(成绩) over (order by 姓名) as count_current,
sum(成绩) over(order by 姓名) as sum_current,
avg(成绩) over (order by 姓名) as avg_current,
max(成绩) over (order by 姓名) as current_max,
min(成绩) over (order by 姓名) as current_min from 班级表
如果我们想要计算每⼀⾏和前两⾏的移动平均值怎么做呢? avg ()over (order by __ row 2 preceding )的意思是对每⼀⾏和它的前2⾏进⾏取平均值计算,每⼀⾏得到的结果,都是当前⾏和前⾯2⾏的平均(共3⾏)。具体代码如下:
select *,
avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from 班级表;
概括⼀下,想要计算当前⾏与前n⾏(共n+1⾏)的平均时,只要调整rows…preceding中间的数字即可。
这样使⽤窗⼝函数有什么⽤呢?
由于这⾥可以通过preceding关键字调整作⽤范围,在以下场景中⾮常适⽤:
在公司业绩名单排名中,可以通过移动平均,直观地查看到与相邻名次业绩的平均、求和等统计数据。
6.总结:
1.窗⼝函数有以下功能:
同时具有分组(partition by)和排序(order by)的功能
不减少原表的⾏数,所以经常⽤来在每组内排名
2.窗⼝函数使⽤场景:
Top N问题
组内排名
组内⽐较