LEAD (Transact-SQL)
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric のSQL 分析エンドポイント Microsoft Fabric のウェアハウス
SQL Server 2012 (11.x) で開始する自己結合を使用せずに同じ結果セットの後の行からデータにアクセスします。 LEAD は、現在の行の後の特定の物理オフセットにある行へのアクセスを提供します。 現在の行の値を次の行の値と比較するには、 SELECT ステートメントでこの分析関数を使用します。
構文
LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
scalar_expression
指定されたオフセットに基づいて返される値。 これは、1 つの (スカラー) 値を返す任意の型の式です。 scalar_expression を分析関数にすることはできません。
offset
値を取得する現在の行よりも前にある行の数。 指定しない場合は、1 が既定値です。 offset は、列、サブクエリ、または正の整数と評価されたり、暗黙的に bigint に変換される可能性があるその他の式です。 offset を負の値または分析関数にすることはできません。
default
offset がパーティションの範囲外である場合に返される値。 既定値が指定されていない場合は、 NULL が返されます。 default には列、サブクエリ、またはその他の式を指定できますが、分析関数にすることはできません。 default には、scalar_expression の型との互換性が必要です。
[ IGNORE NULLS | RESPECT NULLS ]
適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL Database、Azure SQL Managed Instance、Azure SQL Edge
IGNORE NULLS - パーティションに対して最初の値を計算するときは、データセット内の NULL 値を無視します。
RESPECT NULLS - パーティションに対して最初の値を計算するときは、データセット内の NULL 値を考慮します。 RESPECT NULLS は、 NULLS オプションが指定されていない場合の既定の動作です。
SQL Server 2022 CU4 ではLAGとLEADのIGNORE NULLSに関連するバグ修正が行われました。
Azure SQL Edge のこの引数の詳細については、「欠損値の補完」を参照してください。
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause は、
FROM句によって生成された結果セットを、関数が適用されるパーティションに分割します。 指定しない場合、関数ではクエリ結果セットのすべての行を 1 つのグループとして扱います。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 サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。
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値の置換を示します。
- 前の行に
LAGNULL含まれている場合、現在の行では最新のNULL以外の値が使用されます。 - 次の行に
LEADを含むNULLが含まれている場合、現在の行は次に使用可能な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 値を保持する
次のサンプル クエリは、前の例のIGNORE NULLS引数とは対照的に、RESPECT NULLS引数を使用する方法を示しています。これは、指定されていない場合の既定の動作です。
- 前の行に
LAGNULL含まれている場合、現在の行は最新の値を使用します。 - 次の行に
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) 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
関連するコンテンツ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示