【Kay】SQL窗口函数学习

​一.窗口函数

引入：

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

面对这类需求，就需要使用sql的高级功能窗口函数了

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

窗口函数的基本语法：

<窗口函数> OVER (PARTITION BY <用于分组的列名>
                ORDER BY <用于排序的列名>)

<窗口函数>都有哪些呢？

1） 专用窗口函数，包括rank, dense_rank, row_number等排名函数

2） 聚合函数，如sum. avg, count, max, min等

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

三.如何使用？

1.专用窗口函数rank

例如下图，是班级表中的内容

 如果我们想在每个班级内按成绩排名，得到下面的结果。

上面这个结果实现了在每个班级内，按成绩排名，代码如下

SELECT *,
   RANK() OVER (PARTITION BY 班级
                 ORDER BY 成绩 DESC) AS ranking
FROM 班级表

要求是“每个班级内按成绩排名”，并列名次，且并列名次时会占用下一个名次，可分两部分：

1）按班级分组

partition by用来对表分组。在这个例子中，所以我们指定按“班级”分组（partition by 班级）
2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（ASC）排列

通过下图理解partiition by（分组）和order by（在组内排序）的作用

Bonus：GROUP BY 和 PARTITION BY 的区别

 group by分组汇总后改变了表的行数，一行代表一个类别。而partiition by和rank函数不会

例如统计每个班级的人数：

为什么叫“窗口”函数呢？这是因为PARTITION BY 分组后的结果称为“窗口”，新开一列表示开窗

简单来说，窗口函数有以下功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法是

<窗口函数> OVER (PARTITION BY <用于分组的列名>
                ORDER BY <用于排序的列名>)

二、窗口函数rank, dense_rank, row_number有什么区别？

select *,
   rank() over (order by 成绩 desc) 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函数：不考虑并列名次

强调：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以

三、聚合函数作为窗口函数

聚和窗口函数和上面提到的排名窗口函数用法完全相同，聚合窗口函数括号里指定列名

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号的成绩求和

平均、计数、最大最小值，也同理，都是针对自身记录、以及自身记录之上的所有数据进行计算

#这样使用窗口函数的用处：

 聚合函数作为窗口函数，可以在每一行里直观的看到，截止到本行数据，统计数据是多少。提供了实时分析功能，如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可

注：PARTITION子句可省略，省略就是不指定分组，但通常都是加了PARTITION BY的