MySQL 窗口函数

MySQL 中的窗口函数用于对与**当前行**相关的一组行进行计算。当前行是指函数评估所发生的行。窗口函数执行的计算类似于使用聚合函数进行的计算。但是，与对整个表执行操作的聚合函数不同，窗口函数不会产生分组到一行的结果。这意味着窗口函数对一组行执行操作并**_为每一行生成一个聚合值_**。因此，每一行都保持唯一的标识。

窗口函数是 MySQL 8 版本中引入的新功能，它提高了查询的执行性能。这些函数使我们能够更有效地解决与查询相关的问题。

语法

以下是使用窗口函数的基本语法

在语法中，我们可以看到我们首先指定了窗口函数的名称，后跟一个表达式。然后，我们指定了包含三个表达式的 **OVER** 子句，它们是**_partition_definition、order_definition 和 frame_definition_**。

它确保 OVER 子句始终具有开头和结尾括号，即使它没有任何表达式。

让我们看看 OVER 子句中使用的每个表达式的语法

分区子句

此子句用于**划分或拆分**行到分区中，并且分区边界分隔这些分区。窗口函数对每个分区进行操作，当它跨越分区边界时，它将再次初始化。此子句的语法如下所示

在分区子句中，我们可以定义一个或多个用逗号分隔的表达式。

ORDER BY 子句

此子句用于指定分区内行的顺序。以下是 ORDER BY 子句的语法

我们还可以使用它根据多个键对分区内的行进行排序，其中每个键由一个表达式指定。此子句还可以定义一个或多个用逗号分隔的表达式。尽管 ORDER BY 子句可以与所有窗口函数一起使用，但建议将其与对顺序敏感的窗口函数一起使用。

帧子句

帧是窗口函数中**_当前分区的子集_**。因此，我们使用帧子句来定义当前分区的子集。使用帧子句创建当前分区子集的语法如下

我们可以使用当前行来定义一个帧，该帧允许在分区内相对于当前行的位置移动。

在语法中，**frame_unit** 可以是 **ROWS 或 RANGE**，负责定义帧行和当前行之间的关系类型。如果 frame_unit 是 ROWS，则帧行和当前行的偏移量是行号。而如果 frame_unit 是 RANGE，则偏移量是行值。

**frame_start** 和 **frame_between** 表达式用于指定帧边界。frame_start 表达式有三部分

**UNBOUNDED PRECEDING**：这里，帧从当前分区的第一个行开始。

**N PRECEDING：**这里，N 是一个文字数字或一个计算为数字的表达式。它是第一个当前行之前的行数。

**CURRENT ROW：**它指定最近计算的行

frame_between 表达式可以写成

上述表达式可以包含以下内容之一

**frame_start：**我们之前已经解释过它。

**UNBOUNDED FOLLOWING**：它指定帧在分区中的最后一行结束。

**N FOLLOWING：**它是第一个当前行之后的物理 N 行。

如果在 OVER 子句中未指定 frame_definition，则 MySQL 默认使用以下帧

窗口函数概念

在本节中，我们将看到如何在 MySQL 中使用窗口 函数。因此，我们首先使用以下语句创建一个名为 **"Sales"** 的表

接下来，我们必须使用 INSERT 语句 将记录添加到表中，如下所示

要验证表中的记录，请使用 SELECT 语句

执行后，我们可以看到记录已成功添加到表中。

为了理解窗口函数，我们首先来看看聚合函数在 MySQL 中是如何工作的。聚合函数评估多行并将结果集生成到一行中。因此，执行以下语句，该语句使用聚合函数“SUM”并返回给定年份所有员工的总销售额

输出

同样，我们使用带有 **GROUP BY** 子句的 **"SUM"** 函数，该子句作用于行的子集。因此，执行以下语句，该语句返回按特定年份分组的所有产品的总销售额

输出

在这两个示例中，我们都可以看到聚合函数在查询执行后将行数减少到一行。

与聚合函数类似，窗口函数也适用于行的子集，但它不会将结果集减少到一行。这意味着窗口函数对一组行执行操作并为每一行生成聚合值。**例如，** 执行以下语句，该语句返回每个产品的销售额以及给定年份所有产品的总销售额

输出

在上面的示例中，我们可以看到窗口操作使用了 **OVER** 子句，该子句负责将查询行分区到由窗口函数处理的组中。在这里，OVER 子句按年份对行进行分区，并对每个分区生成一个总和。成功计算后，它将此总和与每个分区行对应。

窗口函数的类型

我们可以将窗口函数主要分为以下三类

聚合函数

它是一个作用于多行并在单行中产生结果的函数。一些重要的聚合函数是

COUNT、SUM、AVG、MIN、MAX 等。

排名函数

它是一个允许我们对给定表中分区的每一行进行排名的函数。一些重要的排名函数是

RANK、DENSE_RANK、PERCENT_RANK、ROW_NUMBER、CUME_DIST 等。

分析函数

它是一个由幂级数局部表示的函数。一些重要的分析函数是

NTILE、LEAD、LAG、NTH、FIRST_VALUE、LAST_VALUE 等。

分析函数示例

在这里，我们将使用 **NTILE** 窗口函数。此函数将一个**整数**值作为参数，该参数将组分为多个整数值。例如，如果我们使用 **NTILE(4)**，那么它将总记录分为四组。当总记录为奇数时，它会将奇数记录添加到第一行。以下查询更清楚地解释了这一点。

输出

在上面的输出中，我们可以看到我们总共有 9 行。因此，NTILE 函数将其分为四行，并且一个额外的行将添加到第一行。

让我们看看另一个使用 **"LEAD"** 函数的示例。此函数用于查询表中的多行，而无需连接表本身。这意味着我们可以从当前行访问**下一行**的数据。它返回下一行的输出。执行以下语句以更清楚地理解它

输出