MySQL刷题遇到的盲点（五）窗口函数

窗口函数

语法：

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

• partition by：用来对表分组（ partition 子句可以省略，省略就是不指定分组）

• order by：是对分组后的结果进行排序

<窗口函数>的位置，可以放以下两种函数：

1. 专用窗口函数。如：rank, dense_rank, row_number等专用窗口函数。

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

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

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

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别，而partiition by和rank函数不会减少原表中的行数

窗口函数的功能：

•  同时具有分组和排序的功能
•  不减少原表的行数（经常用来在每组内排名）

使用场景

• 排名问题。比如：每个部门按业绩来排名

• topN问题。比如：找出每个部门排名前N的员工进行奖励

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

 

具体使用方法

一、使用专用窗口函数

rank函数：

如果有并列名次的行，会占用下一名次的位置。 比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：

如果有并列名次的行，不占用下一名次的位置。 比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：

不考虑并列名次的情况。 比如前3名是并列的名次，排名是正常的1，2，3，4。

强调一点：

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

二、使用聚合函数作为窗口函数

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

注意！！！

sum求和，avg平均、count计数、max最大值、min最小值都是针对自身记录、以及自身记录之上的所有数据进行计算。

怎么理解上面这句话呢？ 看下面这个例子：

如上图，比如sum(grade)的结果中：

        001号，在使用sum窗口函数后的结果，是对001号的成绩求和；

        002号，在使用sum窗口函数后的结果，是对001，002号的成绩求和；

        003号，在使用sum窗口函数后的结果，是对001，002，003号的成绩求和；

        004号，在使用sum窗口函数后的结果，是对001，002，003，004号的成绩求和。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

其他函数同理。

为什么要这么设计呢？

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

同时可以看出每一行数据，对整体统计数据的影响。

文章参考：

通俗易懂的学会：SQL窗口函数