logo图片
MySql教程
MySql用户管理
MySQL数据库
MySql表格和视图
MySQL查询
MySQL索引
MySQL子句
MySQL授权
MySql控制流
MySQL条件
MySQL连接
MySQL键
MySQL触发器
MySQL聚合函数
MySQL常用
MySQL正则表达式
MySql差异性

MySQL 窗口函数

MySQL中的窗口函数,用于对与 当前行相关的一组行进行计算。当前行是发生功能评估的那一行。窗口函数执行的计算类似于使用聚合函数进行的计算。但是,与对整个表执行操作的聚合函数不同,窗口函数不会产生要分组为一行的结果。这意味着窗口函数对一组行执行操作,并且 为每一行生成一个汇总值 。因此,每一行都保持唯一身份。
窗口功能是 MySQL版本 8,可以提高查询的执行性能。这些函数使我们能够更有效地解决与查询相关的问题。

语法

以下是使用窗口函数的基本语法:
window_function_name(expression) 
OVER (
    [partition_defintion]
    [order_definition]
    [frame_definition]
)
在语法中,可以看出我们首先指定了窗口函数的名称,其后是一个表达式。然后,我们指定 OVER 子句,该子句包含三个表达式,分别是 partition_definition,order_definition和frame_definition
确保OVER子句即使没有任何表达式也总是有开括号和闭括号。
让我们看看OVER子句中使用的每个表达式的语法:

分区子句

此子句用于将行 划分或分解为分区,分区边界将这些分区分隔开。窗口函数在每个分区上运行,当它越过分区边界时,它将再次初始化。该子句的语法如下:
PARTITION BY <expression>[{,<expression>...}]
在partition子句中,我们可以定义一个或多个用逗号分隔的表达式。

ORDER BY子句

此子句用于指定分区中行的顺序。以下是ORDER BY子句的语法:
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
我们还可以使用它对多个键的分区中的行进行排序,其中每个键由表达式指定。此子句还可以定义一个或多个用逗号分隔的表达式。尽管ORDER BY子句可以与所有窗口函数一起使用,但建议将其与顺序敏感的窗口函数一起使用。

框架子句

框架是窗口函数中的 当前分区的子集 。因此,我们使用frame子句定义当前分区的子集。使用frame子句创建当前分区的子集的语法如下:
frame_unit {<frame_start>|<frame_between>}
我们可以使用当前行定义一个Frame,该Frame允许相对于当前行的位置在分区中移动。
在语法上, frame_unit ROWS或RANGE 负责定义帧行和当前行之间的关系类型。如果frame_unit是ROWS,则帧行和当前行的偏移量是行号。如果frame_unit为RANGE,则偏移量为行值。
frame_start frame_between 表达式用于指定帧边界。 frame_start表达式具有三件事:
未绑定的前缀: 这里,帧从当前分区的第一行开始。
N PRECEDING : 在这里,N是文字数字或以数字求值的表达式。它是当前第一行之前的行数。
当前行: 它指定最近计算的行
可以写frame_between表达式为:
BETWEEN frame_boundary_1 AND frame_boundary_2
上面的表达式可以包含以下内容之一:
frame_start: 我们之前已经解释过。
未绑定: 它指定分区最后一行中帧的结尾。
N跟随: 这是当前第一行之后的实际N行。
如果未在OVER子句中指定frame_definition,则默认情况下 MySQL 使用以下框架:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

窗口函数概念

在本节中,我们将了解如何使用窗口 MySQL中的功能。因此,我们首先使用以下语句创建一个名为 "销售" 的表:
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窗口函数
要了解窗口函数,首先让我们看看聚合函数在MySQL中的工作方式。聚合函数评估多行并将结果集生成为一行。因此,执行以下使用聚合函数" SUM"的语句,并返回给定年份所有雇员的总销售额:
mysql> SELECT SUM(sale) AS Total_Sales FROM Sales;
输出
MySQL窗口函数
同样,我们将 " SUM" 函数与对行子集起作用的 GROUP BY 子句一起使用。因此,执行以下语句,返回特定年份所有产品组的总销售额:
mysql> SELECT Year, Product, SUM(Sale) AS Total_Sales 
FROM Sales 
GROUP BY Year 
ORDER BY Product;
输出
MySQL窗口函数
在两个示例中,我们可以看到聚合函数将查询执行后的行数减少为单行。
类似于聚合函数,窗口函数也可以处理行的子集,但不能将结果集减少为一行。这意味着窗口函数对一组行执行操作,并为每行产生一个汇总值。 例如,执行以下语句,以返回给定年份的每种产品的销售以及产品的总销售额:
mysql> SELECT Year, Product, Sale, SUM(Sale) 
OVER(PARTITION BY Year) AS Total_Sales 
FROM Sales;
输出
MySQL窗口函数
在上面例如,我们可以看到窗口操作使用 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;
输出
MySQL窗口函数
在上面输出,我们可以看到总共有9行。因此,NTILE函数将其分为四行,并且第一行将增加一行。
让我们看看使用 " LEAD" 函数的另一个示例。此函数用于查询表中的多个行而无需联接表本身。这意味着我们可以从当前行访问 下一行的数据。它从下一行返回输出。执行以下语句以更清楚地了解它:
SELECT Year, Product, Sale, 
LEAD(Sale,1) OVER(ORDER BY Year) AS Total_Sales 
FROM Sales;
输出
MySQL窗口函数
昵称: 邮箱:
Copyright © 2020 立地货 All Rights Reserved.
备案号:京ICP备14037608号-4