SQL Server Lag()函数

在本教程中，将学习如何使用LAG()函数访问当前行之前的特定物理偏移量的行。

SQL Server LAG()函数简介

SQL Server LAG()是一个Window函数，它提供对当前行之前的指定物理偏移量的行的访问。

换句话说，通过使用LAG()函数，可以从当前行访问上一行的数据或上一行之前的行，依此类推。

LAG()函数对于将当前行的值与前一行的值进行比较非常有用。

以下是LAG()函数的语法：

LAG(return_value ,offset [,default]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

在上面语法中，

• return_value - 基于指定偏移量的前一行的返回值。 返回值必须求值为单个值，不能是另一个Window函数。
• offset - 从当前行返回的行数，用于访问数据。 offset可以是计算结果为正整数的表达式，子查询或列。如果未明确指定offset，则它的默认值为1。
• default - 是当offset超出分区范围时要返回的值。如果未指定，则默认为NULL。
• PARTITION BY子句将结果集的行分配到应用LAG()函数的分区。如果省略PARTITION BY子句，该函数会将整个结果集视为单个分区。
• ORDER BY子句指定应用LAG()函数的每个分区中行的逻辑顺序。

SQL Server LAG()函数示例

下面将重用在LEAD()函数教程中创建的视图sales.vw_netsales_brands进行演示。创建视图语句：

CREATE VIEW sales.vw_netsales_brands
AS
 SELECT 
 c.brand_name, 
 MONTH(o.order_date) month, 
 YEAR(o.order_date) year, 
 CONVERT(DEC(10, 0), SUM((i.list_price * i.quantity) * (1 - i.discount))) AS net_sales
 FROM sales.orders AS o
 INNER JOIN sales.order_items AS i ON i.order_id = o.order_id
 INNER JOIN production.products AS p ON p.product_id = i.product_id
 INNER JOIN production.brands AS c ON c.brand_id = p.brand_id
 GROUP BY c.brand_name, 
 MONTH(o.order_date), 
 YEAR(o.order_date);

以下查询显示sales.vw_netsales_brands视图中的数据：

SELECT 
    *
FROM 
    sales.vw_netsales_brands
ORDER BY 
    year, 
    month, 
    brand_name, 
    net_sales;

执行上面查询语句，得到以下结果：

1. SQL Server LAG()函数在结果集中使用示例
此示例使用LAG()函数返回2018年当月和上个月的净销售额：

WITH cte_netsales_2018 AS(
 SELECT 
 month, 
 SUM(net_sales) net_sales
 FROM 
 sales.vw_netsales_brands
 WHERE 
 year = 2018
 GROUP BY 
 month
)
SELECT 
 month,
 net_sales,
 LAG(net_sales,1) OVER (
 ORDER BY month
 ) previous_month_sales
FROM 
 cte_netsales_2018;

执行上面查询语句，得到以下结果：

在这个例子中：

• 首先，CTE返回按月汇总的净销售额。
• 然后，外部查询使用LAG()函数返回上个月的销售额。

2. SQL Server LAG()函数在分区示例上使用

以下语句中使用LAG()函数比较当前月份与2018年每个品牌的前一个月的销售额：

SELECT 
 month,
 brand_name,
 net_sales,
 LAG(net_sales,1) OVER (
 PARTITION BY brand_name
 ORDER BY month
 ) next_month_sales
FROM 
 sales.vw_netsales_brands
WHERE
 year = 2018;

执行上面查询语句，得到以下结果：

在这个例子中：

• PARTITION BY子句按行号将行划分为分区。
• 对于每个分区(或品牌名称)，ORDER BY子句按月对行进行排序。
• 对于每个分区中的每一行，LAG()函数返回上一行的净销售额。

要比较当前月份的销售额与2018年按品牌划分的上个月净销售额，请使用以下查询：

WITH cte_sales AS (
 SELECT 
 month,
 brand_name,
 net_sales,
 LAG(net_sales,1) OVER (
 PARTITION BY brand_name
 ORDER BY month
 ) previous_sales
 FROM 
 sales.vw_netsales_brands
 WHERE
 year = 2018
)
SELECT 
 month, 
 brand_name,
 net_sales, 
 previous_sales,
 FORMAT(
 (net_sales - previous_sales)  / previous_sales,
 'P'
 ) vs_previous_month
FROM
 cte_sales;

执行上面查询语句，得到以下结果：

在本教程中，学习了如何使用SQL Server LAG()函数访问当前行之后的特定物理偏移量的行。