sql server累计求和函数_SQL高级功能：窗口函数

继续前面的深入学习，今天学习有关SQL的高级功能。

开始学习前，也是作为一个提醒，避免走弯路。先确认下自己的mysql版本是什么，mysql8.0以后才开始支持窗口函数。

一、窗口函数

1、什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。“窗口”，是范围的意思。

2、如何使用窗口函数？

窗口函数的基本语法如下：

‹窗口函数› over (partition by ‹用于分组的列名›  order by ‹用于排序的列名›)

例如下图，是班级表中的内容

题目：查询每个班级内按成绩排名的结果

解答：

select *,
       rank() over(partition by 班级 order by 成绩 desc) as 'ranking'
from class;

查询结果：

‹窗口函数›的位置，可以放两种函数：专用窗口函数和聚合函数。

接下来，就结合实例，给大家介绍几种窗口函数和窗口函数的用法。

1） 专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。

• rank函数：相同值排名相同，下一排名根据之前的记录个数而定
• dense_rank函数：相同值排名相同，排名连续不间断
• row_number函数：不管值是否相同，依次连续排名

对上面专用窗口函数的使用描述似乎不太好理解，有点懵。没关系，我们通过一个例子来厘清其中的区别。

题目：查询所有学生按成绩排名情况

解答：

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 class;

得到结果：

从上面的结果（后4位排名）可以看出：

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是： 1，1，1，4。
dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是： 1，1，1，2。
row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的 1，2，3，4。

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

趁热打铁，我们再来看几道经典的面试题。

题目1：在上面的班级表（class）中，查询每个班级中学生成绩最大值所在行的数据

方法一：关联子查询

select *  
from class as a  
where 成绩 = ( 
           select max(成绩)  
           from class as b  
           where b.班级 = a.班级);

得到结果：

方法二：窗口函数

① 第一步，用rank窗口函数。先求得按班级分组并把成绩倒序排序的结果

select *,
       rank() over(partition by 班级 order by 成绩 desc) as 'ranking'      
from class;

得到结果：

② 第二步，取出各班成绩最高（即从结果中取出“排名=1”的行）

select * from (
              select *,
                     rank() over(partition by 班级 order by 成绩 desc) as 'ranking'      
              from class
) as a
where a.ranking ='1';

最终查询结果：

对比两种方法得到的结果，是一样的。

题目2：查询各个班级成绩前两名的记录

解答：

select * from (
              select *,
                     rank() over(partition by 班级 order by 成绩 desc) as 'ranking'      
              from class
) as a
where a.ranking <=2;

得到结果：

2） 聚合函数，如sum. avg, count, max, min等。

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

下面通过具体的题目来体会一下。

题目：按成绩升序排序后查询以下结果：当前累计成绩总分、成绩平均分、最高分成绩、最低分成绩、统计人数以及当前记录之前两位同学的平均成绩

解答：

select *,
       sum(成绩) over(order by 成绩 ) as 当前累计总分,
       avg(成绩) over(order by 成绩 ) as 当前累计平均分,
       max(成绩) over(order by 成绩 ) as 当前累计最高分,
       min(成绩) over(order by 成绩 ) as 当前累计最低分,
       count(成绩) over(order by 成绩 ) as 当前统计人数,
       avg(成绩) over(order by 成绩 rows 2 preceding) as 当前记录之前两位同学的平均成绩  
from class;

查询结果是：

---

二、总结

1.窗口函数语法

‹窗口函数› over (partition by ‹用于分组的列名›            order by ‹用于排序的列名›)

(1) <窗口函数>里面可以放以下两种函数：① 专用窗口函数
rank：相同值排名相同，下一排名根据之前的记录个数而定
dense_rank：相同值排名相同，排名连续不间断
row_number：不管值是否相同，依次连续排名② 聚合函数
sum：求和
avg：求平均值
max：求最大值
min：求最小值
count：计数

(2) 窗口函数的“窗口”可以进行“移动”，变成“窗口平移函数”

<窗口函数> over (partition by <分组字段> [asc/desc] order by <排序字段> [asc/desc] rows N preceding)

2. 窗口函数具备的功能

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名（区别于group by）

group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

3. 窗口函数的使用场景1）经典排名问题
业务需求“在每组内排名”。比如，每个部门按业绩来排名2）经典topN问题
找出每个组的前N名。比如，找出每个部门排名前N的员工进行奖励3）经典组内比较问题
比如查找每个组里大于平均值的数据，可以有两种方法：关联子查询和窗口函数

4. 使用窗口函数的注意事项

1）窗口函数原则上只能写在select子句中
2）partition子句可是省略，省略就是不指定分组。
3）注意各窗口函数的区别和使用条件

最后还要强调的是，尤其当语句复杂时，要牢记之前一直说的SQL语句书写顺序和运行顺序。在运行顺序中，select子句是最后被运行的。

以上就是对SQL语句的进一步深探，主要新学了窗口函数相关知识，并结合一些经典的面试类型题，更快更好学习了知识点的运用，有的也提供了一题多解，拓宽了解题思路和方法。对于之前SQL相关学习，除了多回顾知识点，真正掌握还有待多加练习。