MySQL 窗口函数

MySQL中的窗口函数用于在与 当前行 相关的一组行上进行计算。当前行是进行函数计算的那行。窗口函数执行类似于使用聚合函数进行的计算。但是，与在整个表上执行操作的聚合函数不同，窗口函数不产生要分组到一行中的结果。这意味着窗口函数在一组行上执行操作，并为每行 生成一个聚合值 。因此，每行保持其唯一身份。

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

语法

使用窗口函数的基本语法如下：

window_function_name(expression) 
OVER (
    [partition_defintion]
    [order_definition]
    [frame_definition]
)

在语法中，可以看到我们首先指定了窗口函数的名称，然后是一个表达式。然后，我们指定了包含三个表达式的 OVER 子句，它们是 partition_definition, order_definition, and frame_definition .

它确保一个OVER子句始终有开括号和闭括号，即使它没有任何表达式。

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

PARTITION BY子句

该子句用于将行分割或分组成分区，并且分区边界将这些分区分隔开。窗口函数对每个分区进行操作，当它穿过分区边界时，会再次进行初始化。该子句的语法如下：

PARTITION BY <expression>[{,<expression>...}]

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

ORDER BY子句

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

ORDER BY <expression> [ASC|DESC], [{,<expression>...}]

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

帧子句

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

frame_unit {<frame_start>|<frame_between>}

我们可以使用当前行来定义一个框架，该框架可以根据当前行的位置在分区内进行移动。

在语法中，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表达式可以写成：

BETWEEN frame_boundary_1 AND frame_boundary_2

上述表达可以有以下一些内容：

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

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

N FOLLOWING： 它是当前行后面的物理N行。

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

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

窗口函数概念

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

CREATE TABLE Sales(
    Employee_Name VARCHAR(45) NOT NULL,
    Year INT NOT NULL,
            Country VARCHAR(45) NOT NULL,
    Product VARCHAR(45) NOT NULL,
    Sale DECIMAL(12,2) NOT NULL,
    PRIMARY KEY(Employee_Name, Year)
);

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

INSERT INTO Sales(Employee_Name, Year, Country, Product, Sale)
VALUES('Joseph', 2017, 'India', 'Laptop', 10000),
('Joseph', 2018, 'India', 'Laptop', 15000),
('Joseph', 2019, 'India', 'TV', 20000),
('Bob', 2017, 'US', 'Computer', 15000),
('Bob', 2018, 'US', 'Computer', 10000),
('Bob', 2019, 'US', 'TV', 20000),
('Peter', 2017, 'Canada', 'Mobile', 20000),
('Peter', 2018, 'Canada', 'Calculator', 1500),
('Peter', 2019, 'Canada', 'Mobile', 25000);

要验证数据记录是否存在于表中，请使用SELECT语句：

mysql> SELECT * FROM Sales;

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

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

mysql> SELECT SUM(sale) AS Total_Sales FROM Sales;

输出

再次，我们使用带有 “SUM” 函数和 GROUP BY 子句的方法，该方法适用于子行集。因此，执行以下语句，返回按特定年份分组的所有产品的总销售额：

mysql> SELECT Year, Product, SUM(Sale) AS Total_Sales 
FROM Sales 
GROUP BY Year 
ORDER BY Product;

输出

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

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

mysql> SELECT Year, Product, Sale, SUM(Sale) 
OVER(PARTITION BY Year) AS Total_Sales 
FROM Sales;

输出

在上面的示例中，我们可以看到窗口操作使用了 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) ，则将总记录数分为四个组。当总记录是奇数时，它将奇数记录添加到第一行。下面的查询更清楚地解释了这一点。

SELECT Year, Product, Sale, 
NTile(4) OVER() AS Total_Sales 
FROM Sales;

输出

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

让我们看另一个使用 “LEAD” 函数的例子。这个函数用于查询一张表中的多行而不需要将表本身连接起来。它意味着我们可以从当前行访问 下一行 的数据。它返回下一行的输出。执行以下语句以更清楚地了解：

SELECT Year, Product, Sale, 
LEAD(Sale,1) OVER(ORDER BY Year) AS Total_Sales 
FROM Sales;

输出