MySQL窗口函数

最新推荐文章于 2025-06-16 21:33:54 发布

原创最新推荐文章于 2025-06-16 21:33:54 发布 · 1.2k 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

一、什么是窗口函数

MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点，窗口函数是一种特殊的函数，它可以在一组查询行上执行类似于聚合的操作，但是不会将查询行折叠为单个输出行，而是为每个查询行生成一个结果。窗口函数可以用来处理复杂的报表统计分析场景，例如计算移动平均值、累计和、排名等。

语法如下：

<窗口函数>over(partition by 分组字段 order by 排序字段)
# 注意分组和排序字段不是必须项，视问题情况而定

其中，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口：

函数名（[expr]） over(partition by <指定要处理的数据范围> order by <要排序的字段> rows between <数据范围>)

常用的rows语句范围：

rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
# 当order by后面缺少rows语句时，窗口规范默认范围如下：
rows between unbounded preceding and current row.
# 当order by和rows语句都缺失, 窗口规范默认是 
rows between unbounded preceding and unbounded following

常用的窗口函数如下：

分类	函数	作用
序号函数	ROW_NUMBER()	（添加序号）会为结果集中的每一行分配一个唯一的连续整数序号，不会有重复的序号。当有相同排序值时，每行都会有不同的序号。
	RANK()	（排名）会为结果集中的每一行分配一个排名，如果有相同的排序值，则会跳过相同的排名，下一个排名会按照跳过的数量递增。
	DENSE_RANK()	（排名）也会为结果集中的每一行分配一个排名，但不会跳过相同的排名，相同的排序值会有相同的排名，排名是连续的。
分布函数	PERCENT_RANK()	用于计算某一行在结果集中的相对排名百分比。它返回一个介于0和1之间的值，表示当前行在整个结果集中的相对位置。(rank-1)/(rows-1)
分布函数	CUME_DIST()	用于计算某一行在结果集中的累积分布值。它返回一个介于0和1之间的值，表示当前行在整个结果集中的累积分布比例。<=当前rank值的行数/总行数
前后函数	LAG(expr,n)	返回当前行的前n行的expr的值
前后函数	LEAD(expr,n)	返回当前行的后n行的expr的值
头尾函数	FIRST_VALUE(expr)	返回第一个expr的值
头尾函数	LAST_VALUE(expr)	返回最后一个expr的值
其他函数	NTH_VALUE(expr,n)	返回第n个expr的值
其他函数	NTILE (n)	将有序数据分为n个桶，记录等级数

二、有序函数

在SQL中，ROW_NUMBER、RANK和DENSE_RANK是用于对查询结果进行排序并生成序号的窗口函数。它们的作用和含义如下：

ROW_NUMBER():ROW_NUMBER()函数会为结果集中的每一行分配一个唯一的连续整数序号，不会有重复的序号。当有相同排序值时，每行都会有不同的序号。
RANK(): RANK()函数会为结果集中的每一行分配一个排名，如果有相同的排序值，则会跳过相同的排名，下一个排名会按照跳过的数量递增。
DENSE_RANK():DENSE_RANK()函数也会为结果集中的每一行分配一个排名，但不会跳过相同的排名，相同的排序值会有相同的排名，排名是连续的。

对employees表按照工资（salary）降序进行排序，并使用ROW_NUMBER、RANK和DENSE_RANK函数来为每个员工分配序号、排名和稠密排名。这样可以看到每个员工在工资排序下的具体序号、排名和稠密排名。

SELECT 
    employee_id, 
    last_name, 
    first_name, 
    department_id, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

说明：

稠密排名（DENSE_RANK）是一种窗口函数，在SQL中用于为结果集中的每一行分配一个连续的排名，相同的数值会被分配相同的排名，并且排名是连续的，不会跳过。与RANK函数不同的是，DENSE_RANK不会跳过相同的排名，而是按照顺序依次分配排名。

举个例子来说明稠密排名的概念：假设有一个成绩表，其中包含学生姓名和成绩，如果使用DENSE_RANK函数对成绩进行排名，相同成绩的学生会获得相同的排名，而且排名是连续的，不会跳过。例如，如果有三个学生的成绩分别是90分、85分、85分、80分，那么他们的稠密排名可能会是1、2、2、3，即相同成绩的学生会有相同的排名，而排名是连续的。

在实际应用中，稠密排名通常用于需要对数据进行排序并分配连续排名的场景，同时不需要跳过相同排名的情况。通过稠密排名函数，可以方便地为数据集中的每一行分配一个连续的排名，而不会出现跳跃的情况。

上面的案例并没有进行分组操作，那么则是根据employees表中所有的数据分配序号、排名和稠密排名的，那么如果进行分组，则统计的是该分组的配序号、排名和稠密排名，如下：

案例：对每个部门的员工按照薪资排序，给部门名、序号、排名和稠密排名

SELECT departments.department_name,last_name,first_name,salary,
ROW_NUMBER() OVER (PARTITION BY employees.department_id ORDER BY     salary DESC) AS 序号,
RANK() OVER (PARTITION BY employees.department_id ORDER BY     salary DESC) AS 排名,
DENSE_RANK() OVER (PARTITION BY employees.department_id ORDER BY     salary DESC) AS 稠密排名
FROM employees INNER JOIN departments ON employees.department_id = departments.department_id

这时候查看结果，会发现由于上面进行了分组，所以统计的结果都是正对具体某个组的：
在这里插入图片描述

三、分布函数

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.案例演示

案例：计算员工工资的排名百分比和累积分布比例。

SELECT
    department_id,
    last_name,
    first_name,
    salary,
    RANK() OVER (ORDER BY salary) AS ranking,
    PERCENT_RANK() OVER (ORDER BY salary)AS 相对排名百分比,
  CUME_DIST() OVER(ORDER BY salary)AS 累积分布值
FROM employees

以上SQL查询将返回一个结果集，包含每位员工的姓名、部门、工资，以及他们在工资排名中的百分比排名和累积分布比例。通过这些窗口函数，我们可以更直观地了解员工工资在整个数据集中的位置和分布情况。查询如下：

在这里插入图片描述

说明：

排名百分比：以第三行的0.018867924528301886为例，它是通过(rank - 1) / (rows -
1)计算得到的，其中rows 为当前窗口的记录总行数，即107，rank为排名，即3。
累积分布值：以第三行的0.028037383177570093为例，小于或等于其RANK值3的只有三个数。所以得到3 / 107 =
0.028037383177570093
但是注意：rank值需要升序排列的值，切记！

案例：计算每个部门员工工资的排名百分比和累积分布比例。

SELECT 
    department_id,
    employee_id,
    last_name,
    first_name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary) AS ranking,
    PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS 排名百分比,
    CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS 累积分布比例
FROM employees;

执行如下：
在这里插入图片描述

四、前后函数

4.1.含义说明

在MySQL中，LEAD()和LAG()是用于访问行之间的相邻行的窗口函数。它们的作用和含义如下：

LEAD(): LEAD()函数用于访问当前行之后的指定偏移量的行。它可以帮助我们获取当前行后面的某一行的值。
LAG(): LAG()函数用于访问当前行之前的指定偏移量的行。它可以帮助我们获取当前行前面的某一行的值。

4.2.语法

LEAD()函数：LEAD()函数用于获取当前行之后指定偏移量的行的数值。其语法如下：

LEAD(expression, offset, default) OVER (order_clause)

参数：

expression：要获取的列或表达式。
offset：偏移量，表示要获取的行相对于当前行的位置。默认为1，表示获取当前行之后的一行。
default：可选参数，当没有足够的行来满足偏移量时返回的默认值。
order_clause：可选参数，用于指定结果集的排序顺序。

2.LAG()函数：LAG()函数用于获取当前行之前指定偏移量的行的数值。其语法如下：

LAG(expression, offset, default) OVER (order_clause)

expression：要获取的列或表达式。
offset：偏移量，表示要获取的行相对于当前行的位置。默认为1，表示获取当前行之前的一行。
default：可选参数，当没有足够的行来满足偏移量时返回的默认值。
order_clause：可选参数，用于指定结果集的排序顺序。

4.3.案例

对employees表按照工资（salary）进行排序，并使用LEAD()和LAG()函数来获取每个员工的下一个员工的工资和上一个工资

SELECT
    employee_id,
    last_name,
    first_name,
    department_id,
    salary,
    LEAD(salary,1) OVER (ORDER BY salary) AS 下一个员工薪资,
    LAG(salary,1) OVER (ORDER BY salary) AS 上一个员工薪资
FROM employees

LEAD(salary, 1)表示获取当前行后面一行的工资值，而LAG(salary, 1)表示获取当前行前面一行的工资值。如下：
在这里插入图片描述

案例：对employees表中每个部门按照工资（salary）进行排序，并使用LEAD()和LAG()函数来获取部门中每个员工的下一个员工的工资和上一个员工工资

SELECT
    department_id,
    employee_id,
    last_name,
    first_name,
    salary,
    LEAD(salary,1) OVER (PARTITION BY department_id ORDER BY salary) AS 下一个员工薪资,
    LAG(salary,1) OVER (PARTITION BY department_id ORDER BY salary) AS 上一个员工薪资
FROM employees

效果如下：

在这里插入图片描述

五、头尾函数

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函数的作用：

SELECT
    employee_id,
    last_name,
    first_name,
    department_id,
    salary,
    FIRST_VALUE(salary) OVER (ORDER BY salary) AS 第一个值,
    LAST_VALUE(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 最后一个值
FROM employees

对employees表按照工资（salary）进行排序，并使用FIRST_VALUE和LAST_VALUE函数来获取员工的第一个工资和最后一个工资。FIRST_VALUE(salary)表示获取窗口中工资的第一个值，而LAST_VALUE(salary)表示获取窗口中工资的最后一个值。

注意：LAST_VALUE函数需要指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，以确保获取到最后一个值。

执行后如下：

在这里插入图片描述

案例：统计employees中每个部门中第一个员工和最后一个员工的工资

SELECT
    department_id,
    employee_id,
    last_name,
    first_name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS 第一个值,
    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 最后一个值
FROM 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个桶。

SELECT
    employee_id,
    last_name,
    first_name,
    department_id,
    salary,
    NTH_VALUE(salary,3) OVER (ORDER BY salary) AS third_salary,
    NTILE(4) OVER (ORDER BY salary) AS salary_bucket
FROM employees