LAG (Transact-SQL)

[アーティクル]
10/31/2024

Applies to: 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) で開始する自己結合を使用せずに同じ結果セットの前の行からデータにアクセスします。 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 は、負の値または分析関数にはなりません。

default
offset がパーティションの範囲外である場合に返される値。既定値を指定しない場合、NULL が返されます。 default には、列、サブクエリ、または式を指定できますが、分析関数は指定できません。 default には、scalar_expression の型との互換性が必要です。

[ IGNORE NULLS | RESPECT NULLS ]

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database、Azure SQL Managed Instance、Azure SQL Edge

IGNORE NULLS - パーティションの最初の値の計算時に、データセット内の null 値を無視します。

RESPECT NULLS - パーティションの最初の値の計算時に、データセット内の null 値を使用します。 RESPECT NULLS は、NULLS オプションが指定されていない場合の既定の動作です。

LAG と LEAD の IGNORE NULLS に関連するバグ修正が SQL Server 2022 CU4 にありました。

Azure SQL Edge のこの引数の詳細については、「欠損値の補完」を参照してください。

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause は、FROM 句で生成された結果セットをパーティションに分割します。このパーティションに関数が適用されます。指定しない場合、関数ではクエリ結果セットのすべての行を 1 つのグループとして扱います。 order_by_clause は、関数を適用する前にデータの順序を決定します。 partition_by_clause を指定した場合、この引数によってパーティション内のデータの順序が決定されます。 order_by_clause が必要です。詳細については、OVER 句 (Transact-SQL) に関する記事を参照してください。

戻り値の型

指定した scalar_expression のデータ型。 scalar_expression が NULL 値を許容するか default が NULL に設定されている場合は、NULL が返されます。

全般的な解説

LAG は非決定的です。詳細については、「決定的関数と非決定的関数」を参照してください。

例

A. 年間の値を比較する

次の例では、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 句によって、結果セット全体で行の並べ替えが行われます。各パーティションの最初の行に使用できるラグ値がないため、既定のゼロ (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 値に置き換えることを示します。

前の行に LAG の NULL が含まれていた場合、現在の行では最新の非 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 引数の使用を示しています。

前の行に LAG の 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) 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 関数を使用してカレンダー年の過去数四半期にわたる特定の従業員の販売ノルマの差を返します。最初の行に使用できるラグ値がないため、既定のゼロ (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

[ IGNORE NULLS | RESPECT NULLS ]

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. 四半期の値を比較する

次のステップ

フィードバック

その他のリソース