LEAD (Transact-SQL)
適用于: Microsoft Fabric 中 Microsoft Fabric 倉儲中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) SQL 分析端點
在不使用開頭為 SQL Server 2012 (11.x) 的自我聯結下,於相同的資料集中,從後續的資料列存取資料。 LEAD 在跟隨目前資料列的已指定實體位移中,提供存取資料列。 在 SELECT 陳述式中使用這個分析函數,比較目前資料列中的值與下列資料列中的值。
Syntax
LEAD ( scalar_expression [ , offset ] , [ default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
注意
若要檢視 SQL Server 2014 (12.x) 和舊版的 Transact-SQL 語法,請參閱 舊版檔 。
引數
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。
LEAD 不具決定性。 如需詳細資訊,請參閱 決定性與非決定性函數。
範例
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 函數會分別套用至每個分割區,並會針對每個分割區重新開始計算。 OVER 子句中指定的 ORDER BY 子句,會在套用函數之前排序每個分割區中的資料列。 SELECT 陳述式中的 ORDER BY 子句,會排序整個結果集中的資料列。 請注意,因為每個分割區的最後一列沒有可用的前置值,所以傳回預設值零 (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 值替代。
- 如果上述資料列包含 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. 比較季之間的值
下列範例示範 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
下一步
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應