一、什么是窗口函数

窗口函数，也叫OLAp函数（Online Anallytical Processing,联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法

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

<窗口函数>的位置，可以放以下两种函数：

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

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

由于窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

为什么叫“窗口”函数？

因为partition by分组后的结果称为“窗口”，表示“范围“。

窗口函数有以下功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

二、如何使用窗口函数？

例：

• 专用窗口函数rank

如果我们想在每个班级内按成绩排名，得到下面的结果。

其sql语句代码如下：

select *,rank() over (partition by 班级
order by 成绩 desc）as ranking from 班级表

思路：

每个班级内按成绩排名

1）每个班级内：按班级分组 partition by用来对表分组。

2）按成绩排名：order by子句的功能是对分组后的结果进行排序。

***group by 和 partition by 的区别

group by：分组汇总后改变了表的行数，一行只有一个类别。

partition by&rank：不减少原表行数。

三、其他专用窗口函数

• 专用窗口函数rank, denserank, 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_number
from 班级表

在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持（）空着就可以。

• 案例1：经典排名问题

下图是“班级“表中的内容，记录了每个学生所在班级和对应的成绩。

现在需要按成绩来排名，如果两个分数相同，那么排名要是并列的。

正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。

<思路>

涉及到排名问题，可以使用窗口函数，注意rank(),denserank,row_number的区别。

<代码>

select *,dense_rank() over (order by 成绩 desc) as dense_rank from 班级表

• 案例2: 经典top n问题

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N)记录。

－按课程号分组取成绩最大值所在行的数据

select 学号,课程号,max(成绩) from score
group by 课程号;

我们可以使用group by 和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。这时，可以使用关联子查询来查询。

select * from score as a 
where 成绩=(select max(成绩) from score as b
where b.课程号=a.课程号);

• 分组取每组最小值

select * from score as a
where 成绩=(select min(成绩) from score as b
where a.课程号=b.课程号)

• 每组最大的n条记录

现有“成绩表“，记录了每个学生各科的成绩。

问题：查找每个学生成绩最高的2个科目。

select * from 
(select *,row_number() over (partition by 姓名 order by 成绩
desc) as ranking
from 各科成绩表)
where dense_rank<=2;

四、聚合函数作为窗口函数

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

select *, sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over （order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 成绩表;

比如0005号后面的聚合窗口函数结果是：学号0001～0005五人成绩的总和、平均、计数及最大最小值。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

这样使用窗口函数的优点：

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值）等。同时可以看出每一行数据，对整体统计数据的影响。

案例：

现有“成绩表”，记录了每个学生各科的成绩。

问题：查找单科成绩高于该科目平均成绩的学生名单

select * from 
(select *,avg(成绩) over (partition by 科目) as 平均成绩
from 各科成绩表)
where 成绩>平均成绩;

五、窗口函数的移动平均

select *, avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from 班级表;

仔细看上面的窗口函数中，用了rows和preceding这两个关键字，是“之前～行“的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录以及前2行的平均。

例如：学号0004学生的current_avg，是自己和前2位同学的平均，即0002，0003，0004三位同学成绩的平均，其他数据的情况也一样，下图非常直观的可以看到计算过程：

每一行得到的结果，都是当前行和前面2行的平均（共3行）。想要计算当前行与前n行（共n+1行）的平均时，只要调整rows...preceding 中间的数字即可。

这里需要注意：在移动平均中，被选出的数据构成一个“框架“，例如，刚才例子中的0002，0003，0004行数据，就是一个”框架“。

这样使用窗口函数有什么用呢？

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：

在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。