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()函數訪問當前行之後的特定物理偏移量的行。