MySQL窗口函数—让查询变得更简单

日期：2025/04/04 13:35来源：未知人气：53

导读：一、窗口函数概述1、什么是窗口函数MySQL从8.0开始支持窗口函数，有的也叫分析函数（处理相对复杂的报表统计分析场景），这个功能在大多商业数据库和部分开源数据库中早已支持。窗口的意思是将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似，但与聚合函数不同的地方是：聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结......

一、窗口函数概述

1、什么是窗口函数

MySQL从8.0开始支持窗口函数，有的也叫分析函数（处理相对复杂的报表统计分析场景），这个功能在大多商业数据库和部分开源数据库中早已支持。

窗口的意思是将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似，但与聚合函数不同的地方是：聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）。窗口函数会对每一条数据进行计算，并不会使返回的数据变少（每一行返回一个结果）。

2、窗口函数有哪些

窗口函数可以分为两类：一类既可以做为聚合函数，也可以作为窗口函数，当函数单独使用时是聚合函数，当与over关键字同时使用时作为窗口函数。另一类是专用窗口函数，他们必须与 over 关键字同时使用。

（1）聚合函数（聚合函数不是本文讨论的重点）

MySQL聚合函数详解——让查询变得很简单

AVG() 返回自变量的平均值
BIT_AND() 返回按位AND
BIT_OR() 返回按位或
BIT_XOR() 返回按位异或
COUNT() 返回返回的行数的计数
COUNT(DISTINCT) 返回多个不同值的计数
GROUP_CONCAT() 返回串联的字符串
JSON_ARRAYAGG() 将结果集作为单个JSON数组返回
JSON_OBJECTAGG() 将结果集作为单个JSON对象返回
MAX() 返回最大值
MIN() 返回最小值
STD() 返回总体标准差
STDDEV() 返回总体标准差
STDDEV_POP() 返回总体标准差
STDDEV_SAMP() 返回样本标准偏差
SUM() 归还总数
VAR_POP() 返回总体标准方差
VAR_SAMP() 返回样本方差
VARIANCE() 返回总体标准方差

（2）专用窗口函数

序号函数：

row_number() 顺序排序：对数据中的序号进行顺序显示,不管其排序结果是否出现重复值，排序结果为1,2,3,4,5…
rank() 并列排序：相同字段数值并列排序，且跳过重复序号，如, 1,1,3,4,5 。rank函数没有参数，但需要指定按照那个字段进行排名，所以使用rank函数必须用order by参数，order by的排序字段就是排名字段
dense_rank() 并列排序：相同字段数值并列排序，且不跳过重复序号，如：1,1,2,3,4

分布函数：

percent_rank() 累计百分比。函数计算结果为：小于该条记录值的所有记录的行数/该分组的总行数-1，所以该记录的返回值为[0,1]。和之前的RANK()函数相关，每行按照如下公式进行计算： (rank - 1) / (rows - 1) 其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。
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个桶，记录桶的编号

3、基本语法

4、测试数据准备

二、窗口函数使用

1、初识窗口函数：使用聚合函数

通常来说，我们写一个聚合函数，会将分组内的数据进行聚合，形成一行。而窗口操作不会将多组查询行折叠成单个输出行。相反，它们为每一行产生一个结果：

由结果我们可以看出，窗口函数会逐行渲染数据，每一行数据不会合并，而是一行一行的累加：

2、序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

序号函数中，ORDER BY影响行的编号顺序。没有ORDER BY，行编号是不确定的。

row_number() 顺序排序：对数据中的序号进行顺序显示,不管其排序结果是否出现重复值，排序结果为1,2,3,4,5…
rank() 并列排序：相同字段数值并列排序，且跳过重复序号，如, 1,1,3,4,5 。rank函数没有参数，但需要指定按照那个字段进行排名，所以使用rank函数必须用order by参数，order by的排序字段就是排名字段
dense_rank() 并列排序：相同字段数值并列排序，且不跳过重复序号，如：1,1,2,3,4

根据结果我们很明显的可以看出这三个函数的区别，虽然都是用于产生序号，用法稍微有一些区别。

通过使用序号函数，我们可以很轻松的获取分组内前几条数据：

3、分布函数：PERCENT_RANK()、CUME_DIST()

percent_rank() 累计百分比。函数计算结果为：小于该条记录值的所有记录的行数/该分组的总行数-1，所以该记录的返回值为[0,1]。和之前的RANK()函数相关，每行按照如下公式进行计算： (rank - 1) / (rows - 1) 其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。
cume_dist() 累计分布值。分组值小于等于当前值的行数与分组总行数的比值，（0,1]。分组内大于等于当前rank值的行数/分组内总行数。（常用）

由结果我们可以看出，PERCENT_RANK就是统计小于该值的比例，也就是排名的百分比。CUME_DIST()函数主要用于查询小于或等于该值的比例。

4、前后函数

lag(expr,n) 返回当前行的前n行的expr的值
lead(expr,n) 返回当前行的后n行的expr的值

LAG() 函数用于在查询结果中访问当前行之前的行的数据。它允许您检索前一行的值，并将其与当前行的值进行比较或计算差异。LAG()函数对于处理时间序列数据或比较相邻行的值非常有用。LAG()函数完整的表达式为 LAG(column, offset, default_value)，包含三个参数： column：就是列名，获取哪个列的值就是哪个列名，很好理解。 offset: 就是向前的偏移量，取当前行的前一行就是1，前前两行就是2。 default_value：是可选值，如果向前偏移的行不存在，就取这个默认值。

我们通过结果也可以看出，LAG显示该值与上一个值的变化（默认n为1），LEAD正好相反显示该值与下一个值的变化。常用于计算上一个值与下一个值的分数差，也可以用于统计两次请求之间相差的时间等等。

细心的小伙伴也发现了，此处我们的sql有一些变化。没错，window函数可以在最后进行命名，复用起来更加方便，后续我们会详细介绍。

5、头尾函数：FIRST_VALUE()、LAST_VALUE()

FIRST_VALUE(expr)函数返回第一个expr的值。 LAST_VALUE(expr)函数返回最后一个expr的值。

从结果看，我们对FIRST_VALUE()很清晰，就是获取的第一个值，但是LAST_VALUE()获取的值跟我们想象中的不太一样呢？没错，LAST_VALUE()是获取的框架中的最后一个值，这里引入了一个重要概念：框架（frame），框架是一个动态的概念，是组的子集，从LAST_VALUE函数可以更好看出框架的动态变化，也就是说，LAST_VALUE()获取的是截止当前行的框架内最后一个值（就是当前行自己），而不是整个组的最后一个值。