开窗函数mysql



目录

• 前言
• 窗口函数的格式
• 函数（Function）的类型
• 开窗函数over()
• 窗口函数使用
• ROW_NUMBER()
• RANK()与DENSE_RANK()
• LEAD()与LAG()
• FIRST_VALUE()与LAST_VALUE()
• NTILE()
• MAX()、MIN()、AVG()、SUM()与COUNT()
• 窗口从句的使用
• 窗口从句进阶

技术交流

技术要学会分享、交流，不建议闭门造车。一个人可以走的很快、一堆人可以走的更远。

相关文件及代码都已上传，均可加交流群获取，群友已超过2000人，添加时最好的备注方式为：来源+兴趣方向，方便找到志同道合的朋友。

MySQL从8.0版本开始支持窗口函数了，窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

窗口函数经常会在leetCode的题目中使用到

窗口函数的格式

函数（Function）的类型

不是所有的函数（Function）都支持开窗函数。目前支持的窗口函数可结合的函数有：

1. 排名函数 ROW_NUMBER()；
2. 排名函数 RANK() 和 DENSE_RANK()；
3. 错行函数 lead()、lag()；
4. 取值函数 First_value()和last_value()；
5. 分箱函数 NTILE()；
6. 统计函数，也就是我们常用的聚合函数 MAX()、MIN()、AVG()、SUM()、COUNT()

开窗函数over()

我们在Function函数之后需要跟上一个开窗函数over()，over()函数参数包括了三个子句（分组子句，排序子句和窗口子句），根据实际需求选择子句：

1. partition by query_patition_clause：即分组，通过query_patition_clause进行分组，一般是表中的某一个字段，所以可以把partition by 看作与GROUP BY 具有相同功能的语法。
2. order by order_by_clause：即排序，通过order_by_clause 进行排序，一般是在分组（partition by）之后再进行排序，如此一来，就是在组内进行排序。如果没有前面的分组子句（partition by），那么就是全部数据进行排序。和普通MySQL中的查询语句一样，排序从句也支持ASC和DESC的用法。
3. Window_clause：窗口从句，它是排序之后的功能扩展，它标识了在排序之后的一个范围，它的格式是：

其中rows和range为二选其一：

1. rows是物理范围，即根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）；
2. range是逻辑范围，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内

between…and…用来指定范围的起始点和终结点，start_expr为起始点，end_expr为终结点

Start_expr为起始点，起始点有下面几种选项：

1. unbounded preceding：指明窗口开始于分组的第一行，以排序之后的第一行为起点；
2. current row：以当前行为起点；
3. n preceding：以当前行的前面第n行为起点；
4. n following：以当前行的后面第n行为起点；

end_expr为终结点，终结点有下面几种选项：

1. unbounded following：以排序之后的最后一行为终点；
2. current row：以当前行为终点；
3. n preceding：以当前行的前面第n行为终点；
4. n following：以当前行的后面第n行为终点；

窗口函数使用

使用一个具体的实例来说明窗口函数使用方法，首先创建一个测试表，有字段id，name和sale，借用实际生活中的例子，假设一个公司有销售部门（id）为1和2，每个部门内有若干个成员（name），每个成员有自己的销售业绩（sale），然后就可以使用一些函数来做统计，首先创建测试表test，并且只对一个分组（id=1）进行分析

表中的数据为：

ROW_NUMBER()

row_number函数根据字段col1进行分组，在分组内部根据字段col2进行排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内的排序是连续且唯一的），例如：

RANK()与DENSE_RANK()

rank函数根据字段col1进行分组，在分组内部根据字段col2进行跳跃排序，有相同的排名时，相同排名的数据有相同的序号，排序序号不连续；

dense_rank函数根据字段col1进行分组，在分组内部根据字段col2进行连续排序，有相同的排名时，相同排名的数据有相同的序号，但是排序序号连续，rank函数和dense_rank函数的区别看例子：

以上是rank函数的用法，再看dense_rank函数

到这里小结一下，row_number函数，rank函数和dense_rank函数都是一种排名函数，他们有以下区别：

1. row_number是没有重复的一种排序，即使对于两行相同的数据，也会根据查询到的顺序进行排名；而rank函数和dense_rank函数对相同的数据会有一个相同的次序；
2. rank函数的排序是可能不连续的，dense_rank函数的排序是连续的

LEAD()与LAG()

lead函数与lag函数是两个偏移量函数，主要用于查找当前行字段的上一个值或者下一个值。lead函数是向下取值，lag函数是向上取值，如果向上取值或向下取值没有数据的时候显示为NULL，这两个函数的格式为：

其中：

• EXPR通常是直接是列名，也可以是从其他行返回的表达式；
• OFFSET是默认为1，表示在当前分区内基于当前行的偏移行数；
• DEFAULT是在OFFSET指定的偏移行数超出了分组的范围时(因为默认会返回null)，可以通过设置这个字段来返回一个默认值来替代null。

看具体例子，下面是lead函数和lag函数的基本用法，参数只有目标字段，则OFFSET偏移量默认为1，DEFAULT默认为NULL

将OFFSET偏移量设置为2，即可以查到当前行的后面第2行的数据，如果当前行的往下数2行没有数据，则会显示NULL，看例子：

将OFFSET偏移量设置为2，同时将DEFAULT设置为"Empty",如果当前行的往下数2行没有数据，则会显示"Empty"，即把默认显示的NULL换成我们自定义的显示内容，看例子：

DEFAULT内容也可以显示其它字段的信息，例如有这个场景：如果下面行没有数据，则显示它自己这一行，只要把DEFAULT换成sale字段即可，可以自作尝试

这里需要指出的是lead函数和lag函数中三个参数的顺序是固定的，即第一个参数EXPR，一般为某一个字段或者其它表达式；第二个参数是偏移量，第三个参数是显示的默认值，例如，我们只传入一个参数

FIRST_VALUE()与LAST_VALUE()

其中EXPR通常是直接是列名，也可以是从其他行返回的表达式，根据字段col1进行分组，在分组内部根据字段col2进行排序，first_value函数返回一组排序值后的第一个值，last_value返回一组排序值后的最后一个值

如果你使用下列代码进行分组并排序之后，查询最后一个值，那么得到的结果可能会和你想象中的不一样

不要急~你使用的语法没有错误，逻辑也没有错误，这种理想偏差来自last_value函数的默认语法，因为在开窗函数over()中除了分组和排序，还有一个窗口的从句，在经过排序之后，使用last_value函数生效的范围是第一行至当前行，在上面的例子id=1分组中，每一行显示的所谓最后一个值last value来自第一行到当前行这个范围内的最后一个，这里，我们仅对id=1组逐行分析，id=2分组同理可证，希望对你能理解上面代码为什么会出现这种结果能够有所帮助

1. 查询到第1行sale=100，只有当前一行，最后一个值只有100，开窗结果为100；
2. 查询到第2行sale=100，200两个数据，最后一个值是200，开窗结果为200；
3. 查询到第3行sale=100，200，200三个数据，最后一个值是200，开窗结果为200；
4. 查询到四行sale=100，200，200，300四个数据，最后一个值是300，开窗结果为300，至此id=1的分组查询完毕

这里还是需要注意：窗口从句有一个默认的规则，就和上面分析的一样，是从排序之后第一行到当前行的范围，这个规则是可以自己定义的，而且非常灵活，我会在最后会详细介绍窗口从句的用法

NTILE()

NTILE函数对一个数据分区中的有序结果集进行划分，举一个生活中的例子，我们想要把一些鸡蛋放入若干个篮子中，每个篮子可以看成一个组，然后为每个篮子分配一个唯一的组编号，这个组里面就有一些鸡蛋。我们假设篮子的编号可以反映放在内部鸡蛋的体积大小，例如编号较大的篮子里面放着一些体积较大的鸡蛋，编号较小的篮子则放着体积较小的鸡蛋，现在，因为体积特别大的鸡蛋和特别小的鸡蛋不适合放入规定范围包装盒内进行出售，所以要进行筛选，在进行分组之后，我们只需要拎出合适范围的带有编号的篮子就能拿到我们想要的鸡蛋

NTILE函数在统计分析中是很有用的。例如，如果想移除异常值,我们可以将它们分组到顶部或底部的“桶”中，然后在统计分析的时候将这些值排除。在统计信息收集可以使用NTILE函数来计算直方图信息边界。在统计学术语中，NTILE函数创建等宽直方图信息。其语法如下：

ntile_num是一个整数，用于创建“桶”的数量，即分组的数量，不能小于等于0。其次需要注意的是，在over函数内，尽量要有排序ORDER BY子句

这里因为我平时用不到NTILE函数，如果统计分析学需要的同学，可以自己再去深度研究一下，因为我这个案例中数据量太小，发挥不了NTILE函数的作用，简单说明用法：

MAX()、MIN()、AVG()、SUM()与COUNT()

我们知道聚合函数的语法是一样的，可以实现不一样的统计功能

为了测试聚合函数，我这里使用另一个测试表，而且在下面的例子中，我先用max函数求最大值为例，因为大家都知道聚合函数五兄弟用法是一模一样的

如果既有分组也有排序，那么排序之后的开窗函数是默认排序之后第一行数据到当前行（逻辑层面）的最大值，那么可想而知，既然已经排序了，那么当前行肯定是最大值，就会出现下面的现象，我会在表的旁边加上注释

其实，在上面这个代码中，完整的显示是这样的：

其中代码

是排序之后的默认窗口从句，它表示了一个范围，通过between…and…指定一个范围，unbounded preceding表示排序之后的第一行，current row表示当前行。

其中range是逻辑层面的范围，逻辑范围意思是排序之后把具有相同的值看成同一行，例如上面第3、4行有两个相同的值val=3，那么会把第三行和第三行看成同一行，所以range与排序之后的行号是没有关系的，取定的范围和字段值有关；

与之相对应的是rows物理范围，物理范围就是严格根据排序之后的行号所确定的，例如：

现在你可以回开头再仔细研究窗口从句的用法了，我们一起来看一个例子帮助你理解窗口子句的用法：

在这里我们用了

rows是物理范围，只和排序之后的行号有关，和当前行的数值无关，between…and…圈示了一个范围，unbounded preceding表示排序之后的第一行，unbounded following表示排序之后的最后一行，因此得到上面的结果，就是可以取得每个分组从第一行开始到最后一行之间这个范围的最大值

接下来，我会用几个具体例子来更好的说明窗口从句的使用

窗口从句的使用

学完聚合函数之后，就可以研究窗口子句的使用方法了，这里我们还是使用上面那个表test，换用sum函数来学进行说明，示例一，只使用分组，没有排序：

示例二，同时使用分组和排序：

有兴趣的同学可以证明示例二的正确性，在排序之后手动添加窗口子句，一定会得到相同的结果：

示例三，同时使用了分组和排序，但是窗口从句使用物理范围rows：

rows是物理范围，聚合函数的生效范围是严格根据行号来的，这种用法也更好解释，但是实际生活中可能使用逻辑范围range应用更广泛，举一个实际的栗子来说明：班级内相同成绩的学生是有相同的名次的，那么老师在计算平均分的时候肯定是用逻辑范围进行相加再求平均值，不可能具有相同的分数的若干个同学中只取了一个

窗口从句进阶

希望通过上面三个例子能帮助你初步了解什么是窗口从句及其使用语法，到这里你可能会想，为什么范围总是要从第一行开始呢？可不可以自己自定义一个范围呢，答案是可以的，而且可以是任意范围，例如：

再来试试使用range逻辑范围，会产生什么奇妙的结果，这次我们使用sum函数

现在你就彻底弄清楚了逻辑范围range和物理范围rows的区别了~

版权声明：

本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。

本文网址：https://www.mushiming.com/mjsbk/13261.html