select函数_SQL学习笔记（七）SQL高级功能窗口函数

本篇学习SQL中的高级聚合处理窗口函数，要点归纳如下：

• 窗口函数可以同时实现分组和排序功能，并且不减少原表行数。

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

• 能够作为窗口函数使用的函数：

1. 专用窗口函数，如rank, dense_rank, row_number等。
2. 聚合函数，如sum,avg, count, max, min等。

• 关键字：over ,partition by和order by

一、什么是窗口函数？

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

窗口函数中通过partition by 分组后的记录称为“窗口”，它表示“范围”的意思，这也是窗口函数的由来。

二、窗口函数有什么用？

主要用于解决组内排名、TopN问题，例如：每个部门按业绩来排名，找出每个部门排名topN的员工进行奖励等。

窗口函数支持情况：MySQL从version 8.0开始支持窗口函数，大多商业数据库和部分开源数据库也提供支持OLAP用途的功能，窗口函数也被称作分析函数。

三、如何使用窗口函数？

1.窗口函数语法

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

partition by用来对表进行分组，设定排序的对象范围；order by 是对分组后的结果进行排序，用来指定按照哪一列、何种顺序进行排序，默认是按照升序（asc）排列。

2.能够作为窗口函数使用的函数

• 聚合函数（sum,avg,count,max,min)
• 专用窗口函数（rank,dense_rank,row_number等）

3.注意事项

• 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
• 专用窗口函数无需参数，因此通常括号中都是空的。
• partition by子句可是省略，省略就是不指定分组。

4.使用方法举例

（1）专用窗口函数

• rank函数

左边的图片是班级表，想在每个班级内按照成绩排名得到右图的结果。可用如下sql 代码实现：

select 学号,班级,成绩,rank() over (partition by 班级
                                     order by 成绩 desc) as ranking
from 班级表;

窗口函数同时具备了group by子句分组的功能和order by子句排序的功能，但是，使用group by分组汇总后会改变表的行数，而partiition by和rank函数不会减少原表中的行数。

• 其他专用窗口函数

rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例如：有2条记录排在第1位时，就会按照：1位、1位、3位......进行排列

dense_rank 函数：计算排序时，即使存在相同位次的记录，也不会跳过之后的位次。例如：有2条记录排在第1位时，就会按照：1位、1位、2位......进行排列

row_number函数：赋予唯一的连续位次，也就是不考虑并列情况。例如：有2条记录排在第1位时，就会按照：1位、2位、3位......进行排列

举个例子一目了然，运行如下sql代码将得到：

select 学号,班级,成绩,rank() over (rder by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as d_ranking,
row_number() over (order by 成绩 desc) as r_ranking
from 班级表;

（2）聚合函数

所有聚合函数都可以作为窗口函数，用法和专用窗口函数相同。聚合函数sum、avg、count、max、min都是针对自身记录以及自身记录以上的所有数据进行计算的。

聚合函数作为窗口函数，可以在每一行的数据里直观看到截止到本行数据，统计数据是多少，比如：按照时间的顺序，计算各使时期的销售总额就需要用到这种累计的统计方法。同时也可以看出每一行数据对整体数据的影响。

举个例子，运行如下sql代码将得到：

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 班级表;

学号"0002"对应的current_sum是对“0001”和“0002”求和得到的；current_avg是对“0001”和“0002”取平均值；计数、最大值、最小值也是同理。

（3）窗口函数的移动平均

窗口函数可以理解为将表以窗口为单位进行分割，并在其中进行排序的函数。它还包括了在窗口中指定更加详细的汇总范围的备选功能——框架。

主要通过关键字row和preceding来实现将框架指定为“截止到之前XX行”的命令。

通过关键字 row和following来实现将框架指定为“截止到之后XX行”的命令。

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

举个例子，运行如下sql代码将得到：

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

"row 2 preceding"就是将框架指定为“截止到之前2行”（3行记录）也就是将汇总对象的记录限定为：自身（当前记录）、之前1行的记录和之前2行的记录。这样的统计方法称为“移动平均”。

也就是说框架是根据当前记录来确定的，和固定窗口不同，其范围会随着当前记录的变化而变化。

四、常见面试题

1.面试经典排名问题

（1）按照成绩来对学生进行排名，如果分数相同，排名并列。

也就是有2条记录排在第1位时，就会按照：1位、1位、2位......进行排列，这里要用到专用窗口函数dense_rank()。

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

2.面试经典TopN问题

常见的分组取最大、最小、每组最大的N条（top N）记录。表内容如下图:

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

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

（2）按课程号分组取成绩最小值所在行的数据

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

（3）表内容如下图，查找每个学生最高的两个科目

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

• topN 问题万能模板

select *
from(select *, row_number()over(partition by 要分组的列名 order by 要排序的列名 desc) as ranking
from 表明) as a 
where ranking<=N;

3.如何在每个组里比较

表为上图各科成绩表，查找单科成绩高于该科目平均成绩的学生名单

方法一：关联子查询

select 姓名
frrm 各科成绩表 as a
where 成绩>(avg(成绩)
 from 各科成绩表 as b
where a.科目=b.科目);

方法二：窗口函数

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