嗨！你来啦！如果说前面文章我们学习的SQL的基础知识，让你从青铜修炼到了星耀，那么今天我们即将学习的SQL的窗口函数，将会带你一路到王者，carry全场，6到飞起！现在我们一起来开始我们的升级之旅吧！

1.什么是窗口函数

①窗口函数有什么用？

在工作中，常常会遇到组内排名问题，如经典的topN问题，这时就轮到SQL的高级功能窗口函数出场啦！窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。

而且，一个非常棒的情况就是：Mysql从8.0版本开始，也和Sql Server、Oracle一样支持在查询中使用窗口函数啦！

下面我将通过实例介绍窗口函数并举例分组排序函数的使用。

②什么是窗口函数？

窗口函数也称为OLAP函数（Online Anallytical Processing），意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数语法：其中[]中的内容可以省略

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

窗口函数大体可以分为以下两种：

• 能够作为窗口函数的聚合函数（sum，avg，count，max，min）；
• rank，dense_rank。row_number等专用窗口函数。

需要注意的是：

• 由于专用窗口函数无需参数，因此通常括号里都是空的。
• 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数的适用范围原则上只能select子句中使用。

2.如何使用专用窗口函数？

接下来，我们通过几颗栗子来看一下几种专用的窗口函数怎么使用吧！

①专用窗口函数rank

栗子：下表为班级表（图1），请在每个班内排名，并得到如图2的结果。

select *,
rank() over (PARTITION BY 班级
ORDER BY 成绩 DESC ) as ranking 
from 班级表;

• rank函数是用来计算记录排序的函数，计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
• partition by 能够设定排序的对象范围，类似于group by语句，这里就是以“班级”划分排序范围。
• order by能够指定哪一列，何种顺序进行排序。也可以通过asc，desc来指定升序降序。

窗口函数兼具分组和排序两种功能（图2中橘色部分为partition分组，蓝色箭头所示为order by 排序）。

通过partition by分组后的记录集合称为窗口，然而partition by不是窗口函数所必须的，如果没有指定PARTITION BY子句，窗口就没有限制。换种说法就是：如果没有显示指定分区，则默认分区就是把整个查询结果集当作一个分区。有一点不太明显，这里提出来：同一个查询中的不同函数，可能会有不同的分区描述。

你可能会想问：窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为：

• group by会分组统计，然后对字段的值去重操作。只显示唯一的类型。分组汇总后改变了表的行数，一行只有一个类别。
• 而partiition by和rank函数是对数据进行分组，然后累加求和，但是他不会去重，会把这个字段的所有的值都显示出来，不会减少原表中的行数。

②其他专用窗口函数：

除了rank函数，专用窗口函数还有dense_rank, row_number有什么区别呢？

• rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次(1/1/1/4)。
• dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次(1/1/1/2)。
• row_number函数：赋予唯一的连续位次(1/2/3/4)。

是不是有点抽象，没关系，下面我们来以几颗栗子看一下专用窗口函数的用法和区别：

★排名问题（如对学生的成绩进行排名）：

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

从结果表可以看出：

• rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4；
• dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2；
• row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

栗子：对商品价格按三种方式排序。

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking,
       dense_rank () over (order by sale_price) as dense_ranking,
       row_number () over (order by sale_price) as row_num
from Product

★topN问题（如查询各科成绩的前两名）：

score表

select * 
from (select *,
 row_number() over (partition by 课程号 order by 成绩 desc) as ranking  
from score) as a 
where ranking ?= 2;

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

-- topN问题 sql模板 
select * from (select *,          
row_number() over (partition by 要分组的列名     
order by 要排序的列名 desc) as ranking
from 表名) as a where ranking ?= N;

3.如何使用聚合窗口函数

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

依然以班级表为栗子，看一下窗口函数是聚合函数时，会出来什么结果：

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是求累计和，count是累计计数，avg是累计后求平均。如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

★聚合函数作为窗口函数的作用：

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

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

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

select * from (select *,                            
avg(成绩) over (partition by 科目) as avg_score             
from 成绩表) as b 
where 成绩 ? avg_score;

4.窗口函数的移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

这里我们直接用聚合函数avg的窗口函数举例说明：

指定最靠近的3行做为汇总对象：

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

指定框架（汇总范围）：这里使用的rows（行）和preceding（之前）两个关键字，将框架指定为截止到之前XX行。因此rows 2 preceding就是将框架指定为截止到之前2行，也就是将作为汇总对象的记录限定为自身记录及前2行的平均。，例如，刚才例子中的0002、0003、0004行数据，就是一个“框架”

假设求学号0004学生的current_avg，当前行为83，前1行记录为89，前两行记录为95，所以（83+89+95）/3=89。

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

★窗口的移动平均的作用：

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

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

好啦，关于窗口函数的学习就到这里啦，最后，依然是一张思维导图，带你一起复习一下窗口函数：

现在，我宣布，SQL关于数据分析的系统知识学习到此结束，恭喜你有掌握了一门新技能，成为SQL王者！

后面的文章将告诉你，我们如何用学到的这项技能进行数据分析，和我一起期待接下来的项目实战吧！

谢谢你呀，一路上有你的陪伴真的很开心！希望你可以一直在，无论路有多长，我们一起走！

喜欢的话，动动手指点个赞吧，你的每个喜欢，都是我码字的动力！祝你早安、午安、晚安！See you ！