MySQL Window窗口函数

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

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

一、MySQL Window窗口函数 语法

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

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

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

它确保 OVER 子句总是有一个左括号和右括号，即使它没有任何表达式。

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

二、Partition 条件

该子句用于将行划分或拆分为分区，分区边界将这些分区分开。窗口函数对每个分区进行操作，当它越过分区边界时，会再次初始化。该子句的语法如下所示：

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

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

三、Order By 条件

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

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

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

四、Frame 条件

Frame是窗口函数中当前分区的子集。所以我们使用 frame 子句来定义当前分区的一个子集。使用 frame 子句创建当前分区的子集的语法如下：

frame_unit {<frame_start>|<frame_between>}  

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

在语法中，可以为ROWS 或 RANGE的frame_unit负责定义帧行与当前行之间的关系类型。如果 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 子句按年份对行进行分区，并对每个分区产生一个总和。计算成功后，它会产生与每个分区行对应的总和。

六、窗口函数的类型

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

聚合函数

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

计数、总和、平均值、最小值、最大值等等。

排名函数

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

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;  

输出结果为：