sql server累计求和函数_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,
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问题
• 组内排名
• 组内比较