MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点，窗口函数是一种特殊的函数，它可以在一组查询行上执行类似于聚合的操作，但是不会将查询行折叠为单个输出行，而是为每个查询行生成一个结果。窗口函数可以用来处理复杂的报表统计分析场景，例如计算移动平均值、累计和、排名等。
语法如下：
其中，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口：
常用的rows语句范围：
常用的窗口函数如下：
在SQL中，ROW_NUMBER、RANK和DENSE_RANK是用于对查询结果进行排序并生成序号的窗口函数。它们的作用和含义如下：
- ROW_NUMBER():ROW_NUMBER()函数会为结果集中的每一行分配一个唯一的连续整数序号，不会有重复的序号。当有相同排序值时，每行都会有不同的序号。
- RANK(): RANK()函数会为结果集中的每一行分配一个排名，如果有相同的排序值，则会跳过相同的排名，下一个排名会按照跳过的数量递增。
- DENSE_RANK():DENSE_RANK()函数也会为结果集中的每一行分配一个排名，但不会跳过相同的排名，相同的排序值会有相同的排名，排名是连续的。
对employees表按照工资（salary）降序进行排序，并使用ROW_NUMBER、RANK和DENSE_RANK函数来为每个员工分配序号、排名和稠密排名。这样可以看到每个员工在工资排序下的具体序号、排名和稠密排名。
说明：
稠密排名（DENSE_RANK）是一种窗口函数，在SQL中用于为结果集中的每一行分配一个连续的排名，相同的数值会被分配相同的排名，并且排名是连续的，不会跳过。与RANK函数不同的是，DENSE_RANK不会跳过相同的排名，而是按照顺序依次分配排名。
举个例子来说明稠密排名的概念：假设有一个成绩表，其中包含学生姓名和成绩，如果使用DENSE_RANK函数对成绩进行排名，相同成绩的学生会获得相同的排名，而且排名是连续的，不会跳过。例如，如果有三个学生的成绩分别是90分、85分、85分、80分，那么他们的稠密排名可能会是1、2、2、3，即相同成绩的学生会有相同的排名，而排名是连续的。
在实际应用中，稠密排名通常用于需要对数据进行排序并分配连续排名的场景，同时不需要跳过相同排名的情况。通过稠密排名函数，可以方便地为数据集中的每一行分配一个连续的排名，而不会出现跳跃的情况。
上面的案例并没有进行分组操作，那么则是根据employees表中所有的数据分配序号、排名和稠密排名的，那么如果进行分组，则统计的是该分组的配序号、排名和稠密排名，如下：
- 案例：对每个部门的员工按照薪资排序，给部门名、序号、排名和稠密排名
这时候查看结果，会发现由于上面进行了分组，所以统计的结果都是正对具体某个组的：
3.1.PERCENT_RANK() 和 CUME_DIST() 窗口函数
PERCENT_RANK() 函数，作用和含义：
- PERCENT_RANK()函数用于计算某一行在结果集中的相对排名百分比。它返回一个介于0和1之间的值，表示当前行在整个结果集中的相对位置。
- PERCENT_RANK()函数用于将每行按照(rank - 1) / (rows -
1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
CUME_DIST() 函数，作用和含义：
- CUME_DIST()函数用于计算某一行在结果集中的累积分布值。它返回一个介于0和1之间的值，表示当前行在整个结果集中的累积分布比例。
- 返回分组内小于或等于当前rank值的行数/分组内总行数的数据
3.2.案例演示
案例：计算员工工资的排名百分比和累积分布比例。
以上SQL查询将返回一个结果集，包含每位员工的姓名、部门、工资，以及他们在工资排名中的百分比排名和累积分布比例。通过这些窗口函数，我们可以更直观地了解员工工资在整个数据集中的位置和分布情况。查询如下：
说明：
- 排名百分比：以第三行的0.0为例，它是通过(rank - 1) / (rows -
1)计算得到的，其中rows 为当前窗口的记录总行数，即107，rank为排名，即3。 - 累积分布值：以第三行的0.0为例，小于或等于其RANK值3的只有三个数。所以得到3 / 107 =
0.0 - 但是注意：rank值需要升序排列的值，切记！
案例：计算每个部门员工工资的排名百分比和累积分布比例。
执行如下：
4.1.含义说明
在MySQL中，LEAD()和LAG()是用于访问行之间的相邻行的窗口函数。它们的作用和含义如下：
- LEAD(): LEAD()函数用于访问当前行之后的指定偏移量的行。它可以帮助我们获取当前行后面的某一行的值。
- LAG(): LAG()函数用于访问当前行之前的指定偏移量的行。它可以帮助我们获取当前行前面的某一行的值。
4.2.语法
- LEAD()函数：LEAD()函数用于获取当前行之后指定偏移量的行的数值。其语法如下：
参数：
- expression：要获取的列或表达式。
- offset：偏移量，表示要获取的行相对于当前行的位置。默认为1，表示获取当前行之后的一行。
- default：可选参数，当没有足够的行来满足偏移量时返回的默认值。
- order_clause：可选参数，用于指定结果集的排序顺序。
2.LAG()函数：LAG()函数用于获取当前行之前指定偏移量的行的数值。其语法如下：
- expression：要获取的列或表达式。
- offset：偏移量，表示要获取的行相对于当前行的位置。默认为1，表示获取当前行之前的一行。
- default：可选参数，当没有足够的行来满足偏移量时返回的默认值。
- order_clause：可选参数，用于指定结果集的排序顺序。
4.3.案例
对employees表按照工资（salary）进行排序，并使用LEAD()和LAG()函数来获取每个员工的下一个员工的工资和上一个工资
LEAD(salary, 1)表示获取当前行后面一行的工资值，而LAG(salary, 1)表示获取当前行前面一行的工资值。如下：
案例：对employees表中每个部门按照工资（salary）进行排序，并使用LEAD()和LAG()函数来获取部门中每个员工的下一个员工的工资和上一个员工工资
效果如下：
5.1.头尾函数说明
在MySQL中，FIRST_VALUE(expr)和LAST_VALUE(expr)是用于获取窗口中第一个值和最后一个值的窗口函数。它们的作用和含义如下：
- FIRST_VALUE(expr): FIRST_VALUE函数用于返回窗口中指定表达式（expr）的第一个值。
- LAST_VALUE(expr): LAST_VALUE函数用于返回窗口中指定表达式（expr）的最后一个值。
5.2.案例
假设我们有一个名为employees的表，包含以下列：employee_id, last_name, first_name, department_id, salary。我可以使用以下SQL语句来演示FIRST_VALUE和LAST_VALUE函数的作用：
对employees表按照工资（salary）进行排序，并使用FIRST_VALUE和LAST_VALUE函数来获取员工的第一个工资和最后一个工资。FIRST_VALUE(salary)表示获取窗口中工资的第一个值，而LAST_VALUE(salary)表示获取窗口中工资的最后一个值。
注意：LAST_VALUE函数需要指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，以确保获取到最后一个值。
执行后如下：
案例：统计employees中每个部门中第一个员工和最后一个员工的工资
查询效果如下：
6.1.含义说明
在MySQL中，NTH_VALUE(expr, n)和NTILE(n)是用于在窗口中获取第n个值和将数据分割成n个桶的窗口函数。它们的作用和含义如下：
- NTH_VALUE(expr, n): NTH_VALUE函数用于返回窗口中指定表达式（expr）的第n个值。
- NTILE(n): NTILE函数用于将数据分割成n个桶，每个桶中的数据量尽量平均。
6.2.案例演示说明
案例：对employees表按照工资（salary）进行排序，并使用NTH_VALUE和NTILE函数来获取每个员工的第三个工资和将员工按照工资分成4个桶。
说明：
- NTH_VALUE(salary, 3)表示获取窗口中工资的第三个值，
- NTILE(4)表示将数据按照工资分成4个桶，每个桶中的数据量尽量平均。
版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！
转载请注明出处，原文链接：https://www.xkablog.com/haskellbc/65686.html