MySQL LEAD和LAG函数

LEAD 和 LAG 是 MySQL 中的一个窗口函数，用于从其分区内的当前行访问指定行的前后值。这些函数是一种非聚合函数。MySQL中的Window 函数用于对分区或窗口中的每一行执行操作或计算。这些函数产生的结果类似于使用聚合函数完成的计算。但是，与对整个表执行操作的聚合函数不同，窗口函数不会产生要分组到单行的结果。因此，每一行都保持唯一的身份。在窗口函数中，我们必须知道这些事情：

• 发生函数评估的行称为当前行。
• 窗口是与当前行相关的一组行，或者使用哪个函数对该行进行操作。

让我们详细了解这些功能。

一、MySQL LEAD 函数 语法

此函数允许我们向前查找行或后续行以从当前行获取/访问该行的值。这是计算同一输出中当前行和后续行之间的差异的一种非常有用的方法。

以下是在 MySQL 中使用 LEAD 函数的一般语法：

LEAD(expression, offset , default_value) OVER (  
    PARTITION BY (expr)  
    ORDER BY (expr)  
)  

参数说明

参数名称	说明
expression	它是一个列名或任何其值由函数返回的内置函数。
offset	它包含从当前行开始的行数。它应该是一个正整数值。如果为零，则该函数评估当前行的结果。如果我们省略它，该函数默认使用 1。
default_value	当我们没有当前行的后续行时，它将返回一个值。如果我们忽略它，该函数将返回null 值。
OVER	OVER 它负责将行划分为组。如果为空，则函数使用所有行执行操作。
PARTITION BY	它将结果集中的行拆分为应用函数的分区。如果我们没有指定这个子句，则所有行都被视为结果集中的单行。
ORDER BY	它在应用函数之前确定分区中的行序列。

二、MySQL LEAD 函数 示例

在这里，我们将了解 LEAD 函数如何与 MySQL 表一起使用。首先，我们需要使用以下语句创建一个名为sales_table的表。

CREATE TABLE sales_table (  
    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_table VALUES  
('Stephen', 2017, 'India', 'Laptop', 10000),    
('Stephen', 2018, 'India', 'Laptop', 15000),    
('Stephen', 2019, 'India', 'TV', 20000),    
('Bob', 2017, 'US', 'Computer', 15000),    
('Bob', 2018, 'US', 'Computer', 10000),    
('Bob', 2019, 'US', 'TV', 20000),    
('Mandy', 2017, 'Canada', 'Mobile', 20000),    
('Mandy', 2018, 'Canada', 'Calculator', 1500),    
('Mandy', 2019, 'Canada', 'Mobile', 25000);  

我们可以使用SELECT语句将记录验证到表中，它将给出如下输出：

以下语句查找每个员工的销售和下一个销售详细信息：

SELECT Year, Product, Sale,     
LEAD(Sale,1) OVER (  
PARTITION BY Year  
ORDER BY Country) AS Next_Sale    
FROM sales_table;  

此示例首先将结果集按年份划分为分区，然后使用国家列对每个分区进行排序。最后，我们在每个分区上应用 LEAD() 函数来获取下一个销售细节。下面的输出更清楚地解释了它：

在输出中，我们可以看到每个分区中的空值。当后续行跨越分区边界时，每个分区的最后一行中的下一个值始终为 NULL。

三、MySQL LAG 函数 语法

此函数允许我们查看有关后置行或前行的信息，以从当前行获取/访问前一行的值。这是计算同一结果集中当前行与上一行之间的差异的一种非常有用的方法。

以下是在 MySQL 中使用 LAG 函数的一般语法：

LAG (expression, offset , default_value) OVER (  
    PARTITION BY (expr)  
    ORDER BY (expr [ASC|DESC])  
)  

参数说明

参数名称	说明
expression	它是列名或任何内置函数。
offset	它包含当前行之前的行数。它应该是零或任何正整数值。如果为零，则该函数评估当前行的结果。如果我们省略它，该函数默认使用 1。
default_value	当我们没有当前行的前一行时，它将返回一个值。如果我们忽略它，该函数将返回null 值。

其他参数如OVER、PARTITION BY、ORDER BY 子句含义与LEAD 函数相同。

四、MySQL LAG 函数 语法

在这里，我们将了解 LAG 函数如何与 MySQL 表一起使用。我们可以使用上面名为sales_table的表进行演示。

以下语句查找每个员工的销售和以前的销售详细信息：

SELECT Year, Product, Sale,     
LAG(Sale, 1, 0) OVER (  
PARTITION BY Year  
ORDER BY Country) AS Previous_Sale_LAG  
FROM sales_table;  

此示例首先将结果集按年份划分为分区，然后使用国家列对每个分区进行排序。最后，我们在每个分区上应用 LAG() 函数来获取之前的销售细节。执行上述语句后，我们可以看到以下输出：

在输出中，我们可以看到每个分区中的 0.00 值。它指示表中不存在的行的值。如果我们没有提供默认值，它将变为 NULL。

注意： LEAD() 和 LAG() 函数是在 MySQL 8.0 版中引入的。所以我们不能在以前的版本中使用它。它们总是与 OVER() 子句一起使用。如果我们没有使用这个子句，它将引发错误。