Mysql从8.0版本开始,也和Sql Server、Oracle一样支持在查询中使用窗口函数,本文将根据官方文档,通过实例介绍窗口函数并举例分组排序函数的使用。
英语好的大佬请移步官方文档~点我!
本文用到的学生分数表格和语句如下:
DROP
首先创建了如下班级分数表格为例:
![5dc161c28ad4bcf0b8ca67c7353ff775.png](https://i-blog.csdnimg.cn/blog_migrate/8f4177b45582afc42f762f3e894ad789.png)
窗口函数可以大体分为两大类,第一类是能够作为窗口函数的聚合函数:SUM、AVG、COUNT、MAX、MIN,第二类是以RANK、DENSE_RANK、ROW_NUMBER为代表的专用窗口函数。为了便于理解窗口函数,首先以聚合函数sum()为例,下面分别使用窗口函数和聚合函数展示每个学生的成绩总分:
-- 作为窗口函数
![0133b8c717a536ffbaa505c2764bd1c3.png](https://i-blog.csdnimg.cn/blog_migrate/9a5b4469b3d20a420b41f7005f1c700b.jpeg)
-- 与直接使用sum()聚合函数得到的结果一样
![dc9c96d038bbc064afd098c53b2dac0c.png](https://i-blog.csdnimg.cn/blog_migrate/dda511d28d31194314f57aa2c1925156.png)
在这个例子中sum()函数作为窗口函数,通过对‘学生’分区后,加总各个科目的分数得到总分,得分结果与sum()聚合函数的结果一致,但结果中保留了每一行的信息,出现了重复的总分行。因为,所有窗口函数的执行在JOIN, WHERE, GROUP BY, HAVING的结果集之后,在ORDER BY, LIMIT, SELECT DISTINCT之前。当PARTITION BY执行时GROUP BY的聚合过程已经完成了,因此不会再产生数据聚合。
窗口函数的语法
window_function_name
首先需要指定窗口函数的函数名,也就是在上个例子中用的sum(),之后的OVER子句中即使没有内容,括号也需要保留,窗口由[partition_defintion],[order_definition],[frame_definition]确定,任何一个都不是必须的。
- partition_defintio 窗口分区
PARTITION
根据表达式的计算结果来进行分区(列名也是一种表达式)。在例子中"PARTITION BY 学生"对学生列的值分区。
2. order_definition 窗口排序
ORDER
为分区内的行的排列顺序。以下可以实现对学生的科目分数降序排列,并得出学生的科目最高分
SELECT
3. frame_definition 窗口框架
frame_clause
窗口框架的作用对分区进一步细分,frame_unit有两种,分别是ROWS和RANGE,ROWS通过指定当前行之前或之后的固定数目的行来限制分区中的行,RANGE按照排序列的当前值,根据相同值来确定分区中的行。以下通过计算当前行的前两行的平均值计算分数的移动平均分数。
SELECT
![42089717a3d01e46c7ff3d5439a28951.png](https://i-blog.csdnimg.cn/blog_migrate/4c301ddade9cc3c0dd9d0121f8c5a2f6.jpeg)
下面我们使用RANGE对每个分区内从第一行到当前行计算平均值,可以看到由于RANGE根据当前值来确定行,张三的第二行就已经出现了三门的均分,对于三门分数不同的李四,滑动平均值得结果没有变化。
SELECT
![267d70c4b71d5a9e681980c4777ae2cc.png](https://i-blog.csdnimg.cn/blog_migrate/1176694a8ea0910ab4b9f785b7cf7b0d.jpeg)
frame_extent指定帧的起止点,在其中也可以只用frame_start(结束位置就默认为当前行)和frame_between指定起点和终点
frame_start和frame_end可以是以下几种:
- CURRENT ROW: 当前行
- UNBOUNDED PRECEDING: 区间的第一行
- UNBOUNDED FOLLOWING:区间的最后一行
- N PRECEDING: 当前行之前的N行,可以是数字,也可以是一个能计算出数字的表达式
- N FOLLOWING:当前行之后的N行,可以是数字,也可以是一个能计算出数字的表达式
如果没指定帧的话,默认的frame取决于ORDER BY。
- 如果有ORDER BY,SQL会默认帧是区间内从第一行(UNBOUNDED PRECEDING)到当前行(CURRENTROW)
SELECT
![b692f092ce01bfefca77fea9fee37ca0.png](https://i-blog.csdnimg.cn/blog_migrate/131cffaa909ca3811b12b000562ac258.png)
- 如果没有ORDER BY,SQL会默认帧是区间内从第一行(UNBOUNDED PRECEDING)到最后一行(UNBOUNDED FOLLOWING)
SELECT
![d23994a90fc1bcb63b00831b64cb8163.png](https://i-blog.csdnimg.cn/blog_migrate/f7bf178f88125abd8e4549ce72ed6a92.png)
日常我们更常用的是在窗口函数中使用排序函数:
- ROW_NUMBER: 函数名即是排序方法,也就是输出结果集分区的行号(例如:1,2,3,4,5...)
- RANK: 返回结果集的分区内数据进行跳跃排序。 也就是为相同数值的行输出相同排序结果,对于下一行不同的数据将返回行号(例如:1,1,3,4...)
- DENSE_RANK: 返回结果集分区中每行的连续排名,排名值没有间断。行排名等于该行之前不同排名值的数量加一(例如:1,1,2,3,4...)
- NTILE: 将有序分区中的数据分发到指定数目的组中。以本文数据为例,将60-90分的分数等分为4组,即第1组为[90, 82.5),第2组为[82.5, 75),第3组为[75, 67.5),第4组为[67.5, 60]
SELECT
![b161b6b9676ab8c904a55fd612af29f0.png](https://i-blog.csdnimg.cn/blog_migrate/af868048187e7bf51b306325a433052d.jpeg)