LEAD (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) 的自我聯結下，於相同的資料集中，從後續的資料列存取資料。 LEAD 提供在指定實體位移處存取目前數據列之後的數據列。在語句中使用 SELECT 這個分析函式，比較目前數據列中的值與下列數據列中的值。

Transact-SQL 語法慣例

語法

LEAD ( 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 的類型必須與 scalar_expression 相容。

[ 忽略 Null | 尊重 Null ]

適用於：SQL Server 2022 （16.x）和更新版本、Azure SQL 資料庫、Azure SQL 受控執行個體、Azure SQL Edge

IGNORE NULLS - 在計算分割區上的第一個值時，忽略 NULL 數據集中的值。

RESPECT NULLS - 在分割區上計算第一個值時，尊重 NULL 數據集中的值。 RESPECT NULLS 如果未 NULLS 指定選項，則為預設行為。

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

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

OVER （ [ partition_by_clause ] order_by_clause ）

partition_by_clause將子句所產生的FROM結果集分割成套用函式的數據分割。如未指定，此函數會將查詢結果集的所有資料列視為單一群組。
在套用函數之前，order_by_clause 可指定資料順序。

當指定 partition_by_clause 時，即決定每一個分割區的次序。 order_by_clause 為必要項目。如需詳細資訊，請參閱 SELECT - OVER 子句。

傳回類型

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

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

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 比較不同年份的值

查詢會使用函 LEAD 式傳回特定員工在後續幾年的銷售配額差異。因為最後一個數據列沒有可用的潛在客戶值，因此會傳回預設值零（0）。

USE AdventureWorks2022;
GO

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

結果集如下所示。

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

B. 比較分割區中的值

下列範例會使用函 LEAD 式來比較員工之間的年對日銷售。 PARTITION BY指定子句，以依銷售領域分割結果集中的數據列。函 LEAD 式會個別套用至每個分割區，並針對每個分割區重新啟動計算。 ORDER BY子句中指定的 OVER 子句會在套用函式之前，排序每個分割區中的數據列。 ORDER BY語句中的 SELECT 子句會排序整個結果集中的數據列。因為每個數據分割的最後一個數據列沒有可用的潛在客戶值，因此會傳回預設值零（0）。

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

結果集如下所示。

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

C. 指定任意表達式

下列範例示範如何在函式語法中LEAD指定各種任意表達式和忽略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,
    LEAD(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           5           -2
2           NULL        NULL
3           1           0
1           NULL        2
2           4           2
1           -3          8

D. 使用 IGNORE NULLS 尋找非 NULL 值

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

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

如果與包含NULLLAG的前一個數據列，則目前數據列會使用最新的非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. 比較季之間的值

下列範例示範函 LEAD 式。查詢會獲得指定員工在後續日曆季的銷售配額值中的差異。因為最後一個數據列之後沒有可用的潛在客戶值，因此會使用預設值零（0）。

-- Uses AdventureWorks

SELECT CalendarYear AS Year,
    CalendarQuarter AS Quarter,
    SalesAmountQuota AS SalesQuota,
    LEAD(SalesAmountQuota, 1, 0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
    SalesAmountQuota - LEAD(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  NextQuota  Diff
---- -------  ----------  ---------  -------------
2001 3        28000.0000   7000.0000   21000.0000
2001 4         7000.0000  91000.0000  -84000.0000
2001 1        91000.0000 140000.0000  -49000.0000
2002 2       140000.0000   7000.0000    7000.0000
2002 3         7000.0000 154000.0000   84000.0000
2002 4       154000.0000      0.0000  154000.0000

分享方式：

LEAD (Transact-SQL)

語法

引數

scalar_expression

offset

預設值

[ 忽略 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. 比較季之間的值

意見反映

更多資源

分享方式：

LEAD (Transact-SQL)

語法

引數

scalar_expression

offset

預設值

[ 忽略 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. 比較季之間的值

相關內容

意見反映

更多資源

E. 使用 RESPECT NULLS 來保留 `NULL` 值