一什么是窗口函数

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

基本语法

‹窗口函数› over (partition by ‹用于分组的列名›   --partition by用来对表分组，partition by分组后的结果称为“窗口”，表示“范围”的意思
order by ‹用于排序的列名›)    --order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列

‹窗口函数›的位置，可以放以下两种函数：

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

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

2. partition by 和 group by 的区别

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

3. 窗口函数的功能

同时具有分组和排序的功能
不减少原表的行数

二专用窗口函数

专用窗口函数包括rank, dense_rank, row_number，在这三个专用窗口函数的SQL语句中，函数后面的括号不需要任何参数，保持()空着就可以。

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

2. 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 d_rank,
row_number() over(order by 成绩 desc) as row_num   --注意别名不可与窗口函数同名，否则报错
from 班级表;

运行结果：

三个函数的区别如下：

三面试经典排名问题

将班级表按成绩排名，如果两个分数相同，那么排名要是并列的。

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

2. 查询scores表，实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同，且平分后的下一个名次应该是下一个连续的整数值，名次之间不应该有“间隔”。

select *, dense_rank() over(order by score desc) as d_rank
from scores;

四面试经典TopN问题

分组取每组最大值

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

使用关联子查询：

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

使用窗口函数：

select 学号, 课程号, 成绩
from (
select *, dense_rank() over(partition by 课程号,
order by 成绩 desc) as ranking
from score) as a
where ranking = 1;

2. 分组取每组最小值

案例：按课程号分组取成绩最小值所在行的数据

使用关联子查询：

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

3. 每组最大的N条记录

案例：现有“成绩表”，记录了每个学生各科的成绩。表内容如下。问题：查找每个学生成绩最高的2个科目。

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

如果只写括号中子查询的select where语句，SQL会报错，运行到”where ranking › 2”的时候，因为select子句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

❌错误示范：

select *, row_number() over (partition by 姓名
order by 成绩 desc) as ranking 
from 成绩表 
where ranking <= 2;  --报错，找不到ranking列

4. TopN问题万能模板

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

五聚合函数作为窗口函数

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

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

运行结果：

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

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

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

六案例：如何在每个组里比较

基于各科成绩表，查找单科成绩高于该科目平均成绩的学生名单。

使用窗口函数：

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

2. 使用关联子查询：

select *
from 成绩表 as a
where 成绩 > (
select avg(成绩) 
from 成绩表 as b
where b.科目 = a.科目);

ITPub博客

SQL 窗口函数

一什么是窗口函数

二专用窗口函数

三面试经典排名问题

四面试经典TopN问题

五聚合函数作为窗口函数

六案例：如何在每个组里比较

最新文章

ITPub博客

SQL 窗口函数

一 什么是窗口函数

二 专用窗口函数

三 面试经典排名问题

四 面试经典TopN问题

五 聚合函数作为窗口函数

六 案例：如何在每个组里比较

最新文章

一什么是窗口函数

二专用窗口函数

三面试经典排名问题

四面试经典TopN问题

五聚合函数作为窗口函数

六案例：如何在每个组里比较