LAG (Transact-SQL)

發行項
10/31/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

從相同結果集的前一個資料列存取資料，而不使用從 SQL Server 2012 (11.x) 開始的自我聯結。 LAG 會提供對於目前資料列之前給定實體位移之資料列的存取。在 SELECT 陳述式中使用這個分析函數，比較目前資料列中的值與前一個資料列中的值。

Transact-SQL 語法慣例

語法

LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause )

引數

scalar_expression

根據指定的位移傳回數值。它是傳回單一 (純量) 值的任一類型運算式。 scalar_expression 不能是分析函數。

offset
從取得數值的目前資料列傳回資料列的數目。若未加以指定，預設為 1。 offset 可以是資料行、子查詢或其他運算式，能算出正整數或可以明確地轉換為 bigint。 offset 不能是負值或分析函數。

預設值
當 offset 超過資料分割範圍時會傳回的值。如果未指定預設值，會傳回 NULL。 default 可以是資料行、子查詢或其他運算式，但不能是分析函數。 default 的類型必須與 scalar_expression 相容。

[ 忽略 Null | 尊重 Null ]

適用於：SQL Server (從 SQL Server 2022 (16.x)) 開始、Azure SQL Database、Azure SQL 受控執行個體、Azure SQL Edge

IGNORE NULLS - 在計算分割區的第一個值時，忽略資料集中的 Null 值。

RESPECT NULLS - 在計算分割區的第一個值時，採用資料集中的 Null 值。 RESPECT NULLS 如果未指定 NULLS 選項，則為預設行為。

SQL Server 2022 CU4 中有與和 LEAD中 LAG IGNORE NULLS 相關的錯誤修正。

如需 Azure SQL Edge 中這個自變數的詳細資訊，請參閱插補遺漏值。

OVER （ [ partition_by_clause ] order_by_clause ）

partition_by_clause 會將 FROM 子句產生的結果集分割成函數所要套用的分割區。如未指定，此函數會將查詢結果集的所有資料列視為單一群組。在套用函數之前，order_by_clause 可指定資料順序。如果指定 partition_by_clause，它會決定分割區中的資料次序。 order_by_clause 為必要項目。如需詳細資訊，請參閱 OVER 子句 (Transact-SQL)。

傳回型別

已指定的 scalar_expression的資料類型。如果 scalar_expression 可以為 Null 或 default 設為 NULL，則會傳回 NULL。

一般備註

LAG 不具決定性。如需詳細資訊，請參閱決定性與非決定性函數。

範例

A. 比較不同年份的值

下列範例使用 LAG 函數傳回特定員工於前幾年之間的銷售配額差異。請注意，由於第一列沒有可用的 lag 值，所以會傳回預設值零 (0)。

USE AdventureWorks2022;  
GO  
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,   
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota  
FROM Sales.SalesPersonQuotaHistory  
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

結果集如下所示。

BusinessEntityID SalesYear   CurrentQuota          PreviousQuota  
---------------- ----------- --------------------- ---------------------  
275              2005        367000.00             0.00  
275              2005        556000.00             367000.00  
275              2006        502000.00             556000.00  
275              2006        550000.00             502000.00  
275              2006        1429000.00            550000.00  
275              2006        1324000.00            1429000.00

B. 比較分割區中的值

下列範例使用 LAG 函數比較年初至今各員工的銷售額。指定 PARTITION BY 子句可依照銷售領域分割結果集中的資料列。 LAG 函數會分別套用至每個分割區，並會針對每個分割區重新開始計算。 OVER 子句中的 ORDER BY 子句會將每個分割區中的資料列排序。 SELECT 陳述式中的 ORDER BY 子句會排序整個結果集的資料列。請注意，由於每個分割區的第一列沒有可用的 lag 值，所以會傳回預設值零 (0)。

USE AdventureWorks2022;  
GO  
SELECT TerritoryName, BusinessEntityID, SalesYTD,   
       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales  
FROM Sales.vSalesPerson  
WHERE TerritoryName IN (N'Northwest', N'Canada')   
ORDER BY TerritoryName;

結果集如下所示。

TerritoryName            BusinessEntityID SalesYTD              PrevRepSales  
-----------------------  ---------------- --------------------- ---------------------  
Canada                   282              2604540.7172          0.00  
Canada                   278              1453719.4653          2604540.7172  
Northwest                284              1576562.1966          0.00  
Northwest                283              1573012.9383          1576562.1966  
Northwest                280              1352577.1325          1573012.9383

C. 指定任意的運算式

下列範例示範指定各種任意表達式，並忽略 LAG 函式語法中的 NULL 值。

CREATE TABLE T (a INT, b INT, c INT);   
GO  
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);   
  
SELECT b, c,   
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) IGNORE NULLS OVER (ORDER BY a) AS i  
FROM T;

結果集如下所示。

b           c           i  
----------- ----------- -----------  
1           -3          1  
2           4           -2  
1           NULL        8  
3           1           -6  
2           NULL        8  
1           5           2

D. 使用 IGNORE NULLS 尋找非 NULL 值

下列範例查詢示範如何使用 IGNORE NULLS 自變數。

IGNORE NULLS 自變數同時與 LAG 和 LEAD 搭配使用，以示範先前或下一個非 NULL 值的 NULL 值替代。

如果上述數據列包含 NULL 與 LAG，則目前數據列會使用最新的非 NULL 值。
如果下一個數據列包含具有的 NULL LEAD，則目前的數據列會使用下一個可用的非 NULL 值。

DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO

INSERT INTO #test_ignore_nulls VALUES
    (1, 8),
    (2, 9),
    (3, NULL),
    (4, 10),
    (5, NULL),
    (6, NULL),
    (7, 11);

SELECT column_a, column_b,
      [Previous value for column_b] = LAG(column_b) IGNORE NULLS OVER (ORDER BY column_a),
      [Next value for column_b] = LEAD(column_b) IGNORE NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;

--cleanup
DROP TABLE #test_ignore_nulls;

column_a     column_b    Previous value for column_b    Next value for column_b
1            8           NULL                           9
2            9           8                              10
3            NULL        9                              10
4            10          9                              11
5            NULL        10                             11
6            NULL        10                             11
7            11          10                             NULL

E. 使用 RESPECT NULLS 保留 NULL 值

下列範例查詢示範如何使用 RESPECT NULLS 自變數，如果未指定，則為預設行為，而不是上一個範例中的 IGNORE NULLS 自變數。

如果上述數據列包含 NULL 與 LAG，則目前的數據列會使用最新的值。
如果下一個數據列包含具有的 NULL LEAD，則目前的數據列會使用下一個值。

DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO

INSERT INTO #test_ignore_nulls VALUES
    (1, 8),
    (2, 9),
    (3, NULL),
    (4, 10),
    (5, NULL),
    (6, NULL),
    (7, 11);

SELECT column_a, column_b,
      [Previous value for column_b] = LAG(column_b) RESPECT NULLS OVER (ORDER BY column_a),
      [Next value for column_b] = LEAD(column_b) RESPECT NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;

--Identical output
SELECT column_a, column_b,
      [Previous value for column_b] = LAG(column_b)  OVER (ORDER BY column_a),
      [Next value for column_b] = LEAD(column_b)  OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;

--cleanup
DROP TABLE #test_ignore_nulls;

column_a     column_b    Previous value for column_b    Next value for column_b
1            8           NULL                           9
2            9           8                              NULL
3            NULL        9                              10
4            10          NULL                           NULL
5            NULL        10                             NULL
6            NULL        NULL                           11
7            11          NULL                           NULL

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

A. 比較季之間的值

下列範例示範 LAG 函數。此查詢使用 LAG 函數傳回特定員工於前幾個日曆季之間的銷售配額差異。請注意，由於第一列沒有可用的 lag 值，所以會傳回預設值零 (0)。

-- Uses AdventureWorks  
  
SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,  
       LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,  
       SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff  
FROM dbo.FactSalesQuota  
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)  
ORDER BY CalendarYear, CalendarQuarter;

結果集如下所示。

Year Quarter  SalesQuota  PrevQuota  Diff  
---- -------  ----------  ---------  -------------  
2001 3        28000.0000      0.0000   28000.0000  
2001 4         7000.0000  28000.0000  -21000.0000  
2001 1        91000.0000   7000.0000   84000.0000  
2002 2       140000.0000  91000.0000   49000.0000  
2002 3         7000.0000 140000.0000  -70000.0000  
2002 4       154000.0000   7000.0000   84000.0000

共用方式為

LAG (Transact-SQL)

語法

引數

scalar_expression

[ 忽略 Null | 尊重 Null ]

OVER （ [ partition_by_clause ] order_by_clause ）

傳回型別

一般備註

範例

A. 比較不同年份的值

B. 比較分割區中的值

C. 指定任意的運算式

D. 使用 IGNORE NULLS 尋找非 NULL 值

E. 使用 RESPECT NULLS 保留 NULL 值

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

A. 比較季之間的值

下一步

意見反應

其他資源