select函数_SQL的高级功能-窗口函数

今天我们学习sql的高级功能——窗口函数

一、窗口函数有什么用

在我们日常工作中，会经常遇到在每个部门中按业绩排序或找出每个部门排名前N的员工进行奖励。在遇到这种既要分组也要排序的问题是，我们的窗口函数就派上用场了。

二、什么是窗口函数

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

基本语法如下：

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

partiton by用来对表分组

order by用来对分组结果进行排序，根据需求选择升序ACS或降序DESC排列

窗口函数一般分为两类：

1.专用窗口函数，包括rank,dense_rank,row_number

2.聚合函数，包括max,min,sum,avg,count

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

三、如何使用窗口函数

• 专用窗口函数rank

现在我们对下面班级表中各班级成绩进行排序

运行代码

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

我们得到下面查询结果

通过ranking列，我们可以清晰的看到每个班级每个学生成绩的排名情况。

partiton by 用来对班级列进行分组

order by 对班级分组结果的成绩列进行降序排列

同时我们看到，在得到的查询结果中，分组列并没有被合并掉，这一点是跟group by子句的分组有明显的区别，因此我们可以得到这样的结论：

• group by分组汇总改变了表的行数，一行只有一个类别
• partition by和rank函数不会减少原表中的行数

那么我们便得到窗口函数所具有的功能：

1.同时具备分组和排序的功能

2.不改变原表中的行数

• rank函数与denserank函数、row_number函数的区别

我们用依旧用上面的班级表，分别用三个专用窗口函数求每个班级的成绩排名

运行代码

select * ,
(rank() over(order by 成绩 DECS)) as ranking,
(dense_rank() over(order by 成绩 DESC)) as dense_rank,
(row_number() over(order by 成绩 DESC)) as row_num
from 班级表;

查询结果

可以看到，在遇到排名相同时每个函数的表现都不一样。

rank函数，遇到并列名次时，会占据下一个名次位置，后面名次会跳过并列名次的数量

dense_rank函数，遇到并列名次时，不会占据下一个名次位置，后面名次会连续下去

row_number函数，不受并列名次影响，根据顺序一次排列下去

敲黑板：上面三个专用窗口函数中，函数后面的括号内不需要任何参数，保持为空就好。

练习

1.对每个班级同学成绩进行排序，正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2

select * ，
(dense_rank() over(partition by 班级
                   order by 成绩 DESC)) as dense_rank
from 班级表;

2.经典排名问题：编写一个sql查询来实现分数排名。如果两个分数相同，则两个分数排名（rank）相同。请注意，平分后的下一个名次应该是一个连续的整数值。下面是scores表,分数从高到底排序。

select Score,(dense_rank() over(order by Score DESC)) as dense_rank
from scores;

3.经典TopN问题：现有‘成绩表’，记录了每个学生各科成绩。表内容如下。问题：查找每个学生成绩最高的两个科目

-- 先求出每个学生成绩的排名
select *,row_number() over(partition by 姓名 order by 成绩 DESC) as row_number
from 成绩表;

-- 再求出每个学生成绩最高的两个科目
select * from (select *,row_number() over(partition by 姓名 order by 成绩 DESC) as row_number
from 成绩表) as a 
where row_number <= 2;

敲黑板

• 我们把第一步的查询结果作为第二步的主表，然后用where子句做筛选即可。
• row_number函数是不受并列名次所影响
• 通过上述题目，可以总结出一个求TopN排名的模板

-- 求TopN排名的模板
select * from (select *,row_number() over(partition by <要分组的列名> 
                                          order by <要排序的列名> DESC) as ranking 
from 表名) as 别名  
where ranking <= N;

四、其他专用窗口函数

1.什么是聚合窗口函数

聚合窗口函数和专用窗口函数的用法完全相同，在窗口函数的位置上换上据很函数即可，括号里面指定要聚合的列名。

2.聚合窗口函数怎么用

我们运行聚合窗口函数代码

select *,
sum(成绩) over(order by 学号) as current_sum,
avg(成绩) over(order by 学号) as avg_sum,
count(成绩) over(order by 学号) as count_sum,
max(成绩) over(order by 学号) as max_sum,
min(成绩) over(order by 学号) as min_sum
from 成绩表;

得到查询结果

我们通过观察知道，每一行的聚合结果都是动态变化，当我们在第一行针对的是第一行的数据

当我们来到第二行，我们聚合的范围就扩大到了一、二行，以此类推。我们要想知道全部表格的聚合结果，直接跳到最后一行即可。

3.聚合窗口函数有什么作用

它作为窗口函数，可以在每一行的数据里直观的看到截止到本行数据各类聚合函数统计。同时也可以看出每一行数据对整体统计数据的影响。

4.窗口函数的移动平移

我们通过一个例子来说明

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

得到以下查询结果

我们可以看到，窗口函数中的关键字rows和preceding是表示‘之前X行’的意思，

翻译过来就是得到的结果是自身记录及前2行的平均。每一行的结果都是自身和前2行的平均。

想要计算当前行与前n行（共n+1行）的平均值时，可以通过修改rows...preceding中间的数字来改变。

这样的聚合窗口函数一般用于公司业绩排名中，通过移动平均，直观地看到与相邻名次业绩的平均、求和等统计数据。

5.练习 现有“成绩表”，记录了每个学生各科的成绩。表内容如下：查找单科成绩高于该科目平均成绩的学生名单

方法一:聚合窗口函数
-- 先求出每门科目的平均成绩
select *,avg(成绩) over(partition by 科目) as 平均成绩
from 成绩表;

-- 然后求出单科成绩高于对应科目平均成绩的学生名单
select * 
from (select *,avg(成绩) over(partition by 科目) as 平均成绩 from 成绩表) as a
where 成绩 > 平均成绩;

方法二：关联子查询
select * from 成绩表 a
where 成绩 > (select avg(成绩) from 成绩表 b where a.姓名=b.姓名 group by 科目);

五、学习总结

1. 窗口函数分为专用窗口函数（rank,denserank,row_number）和聚合窗口函数(sum,avg,count,max,min)
2. 它们同时具有分组和排序的功能
3. 它们不改变原表的行数，通常用于各组内排名
4. 窗口函数原则上只能写在select子句中
5. 窗口函数中的partition by子句可以省略，得到的结果就只有排序没有分组了
6. 窗口函数的应用场景包括，经典topN问题、经典排名问题、在每组里比较的问题