个人感想：近期状态此起彼伏，进度也是越来越慢了。但是我一直对自己说：今天得自己比昨天至少有进步。但是还不够，所以，在调整好自己的作息以后，五月，就应该开始高速前行。

相信自己可以做到，加油，加油，加油。

一、为什么要使用窗口函数

1、我们已经学会了使用group by 分组函数，但是它有一个缺点：在实际得运算统计中，会改变表得行数，一行只显示一个类别；

2、在工作中，我们又经常会遇到需要每组内排名得相关问题：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

所以，针对以上得这几个问题，group by已经不适用，需要我们使用新的函数：窗口函数。

二、什么是窗口函数

定义

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），简单得说，窗口函数和我们平常使用得函数一样，都是对数据进行分析处理。

窗口函数得位置可以放下两种函数：

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

基本语法

【窗口函数】 over (partition by ‹用于分组的列名›

order by ‹用于排序的列名›)

例如:每个班级内按照成绩进行排名
select *,
rank() over  (partition by  班级 order by 成绩 desc) as ranking
from 班级表

三、怎么用窗口函数

1、“窗口”含义

在基本语法中，“partition by”分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思，

2、窗口函数两种类型

类型一：专用窗口函数（如图）

案例：对于班级中的学生进行成绩排名

select *,       
                  rank() over (order by 成绩 desc) as ranking,             
   
                 dense_rank() over (order by 成绩 desc) as dese_rank,      
          
                 row_number() over (order by 成绩 desc) as row_numfrom 班级表

类型二：聚和函数(如图）

案例：SUM作为窗口行数

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

3、窗口函数功能

同时具有分组和排序的功能；
不减少原表的行数；
减少数据算法时间。

4、使用注意事项

窗口函数原则上只能写在select子句中（因为窗口函数是对where或者group by子句处理后的结果进行操作）；
“partition by”子句可以省略，省略就是不指定分组；
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中.

四、窗口函数使用场景（练习题）

题目一：面试经典排名问题-对成绩进行不同方式得排名（专用窗口函数）

select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over （order by 成绩 desc) as dense_rank,
row_number(0 over (order by 成绩 desc) as row_number
from 班级表

结果如下图：

题目二：面试经典topN问题-分组取每组最大值、最小值，每组最大的N条（top N）记录。

问题1：按课程号分组取成绩最大值所在行的数据

错误：
select 课程号,max(成绩) as 最大成绩
from score 
group by 课程号;


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

分析：直接使用，没有关联，不能显示所有得数据，所以需要使用关联子查询

问题2：现有“成绩表”，记录了每个学生各科的成绩。表内容如下。问题：查找每个学生成绩最高的2个科目（专用窗口函数）

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


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

分析：考虑到sqld的运行顺序，第一种运行到”where ranking › 2”的时候，因为select字句还没有被执行，
因此select中的“ranking”列还没有出现，从而导致报错。

得到结果如图：

topN问题模板

# topN问题 sql模板

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

题目三：如何在每个组里比较？

现有“成绩表”，记录了每个学生各科的成绩。表内容如下：

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

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

分析：同样，需要注意sql运行顺序。

结果如下图

题目四：窗口函数的移动平均问题（聚和函数）

直接使用聚合函数avg的窗口函数举例说明

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

分析：每一行得到的结果，都是当前行和前面2行的平均（共3行）。想要计算当前行与前n行（共n+1行）的平均时，
只要调整rows…preceding中间的数字即可。
这里需要注意：在移动平均中，被选出的数据构成一个“框架”，例如，刚才例子中的0002、0003、0004行数据，就是一个“框架”

结果如下图

ITPub博客

零基础学习SQL-SQL高级功能：窗口函数（七）

一、为什么要使用窗口函数

二、什么是窗口函数

三、怎么用窗口函数

四、窗口函数使用场景（练习题）

最新文章