T-SQL - LAG 関数の使い方：前の行の値を取得する方法

LAG 関数の使い方：前の行の値を取得する方法

T-SQL でプログラミングをしていると、SELECT 文の中で、前の行の値と現在の行の値との差分をとりたいような時ありませんか？

以前はそんな時、同じテーブルや結果セットをずらして JOIN するなど、工夫しなければなりませんでした。

今は LAG 関数を使えば、前の行の値を参照することができます。

この記事では、LAG 関数の基本的な使い方から、実務で役立つ例などをご説明します。

LAG 関数の基本的な使い方

SQL Server の LAG 関数は、現在の行から「指定した行数分だけ前の値」を参照するウィンドウ関数です。

SQL Server 2012 以降で使用可能です。

LAG 関数の基本的な構文は以下の通りです。

LAG (列名 [, オフセット] [, デフォルト値])
OVER ( [PARTITION BY グループ] ORDER BY 並び順 )

[列名] には、LAG 関数でどの列のデータを取得するかを指定します。

[オフセット] はオプショナルの引数で、何行前のデータを取得するかで、指定しなかった場合のデフォルトは 1 です。

[デフォルト値] もオプショナルの引数で、オフセットの指定がスコープの外になってしまった時に返す値や式などを指定することができます。指定しなければ NULL が返ります。

[デフォルト値] は列名と同じに変換できるデータ型のものでなければなりません。

OVER ( [PARTITION BY グループ] ORDER BY 並び順 ) の部分は、ORDER BY 並び順 は必須で、どの順番で並べて、何行前の値をとるかを指定します。

[PARTITION BY グループ] の部分はオプショナルで、FROM 句によって生成された結果セットを、LAG 関数が適用されるパーティションに分割することができます。

[PARTITION BY グループ] を指定しなかった場合は、結果セット全部がひとつのグループとしてあつかわれます。

それでは、LAG 関数を使って、前の行の値を取得してみましょう。

次のような、StoreName（店舗名）、SalesDate（売上日）、SalesAmount（売上金額）が保存されている、Sales（売上）テーブルがあります。

SELECT  StoreName,
        SalesDate,
        SalesAmount
FROM    Sales;

[実行結果]

この Sales テーブルから、LAG 関数を使って Store A の前回の売上金額を取得するには、次のようにできます。

SELECT  StoreName,
        SalesDate,
        SalesAmount,
        LAG(SalesAmount) OVER (ORDER BY SalesDate) AS PreviousSales
FROM    Sales
WHERE   StoreName = 'Store A'
ORDER BY SalesDate;

[実行結果]

SalesDate で並べて、1 行前の SalesAmount が PreviousSales に表示されています。

1 行目は、デフォルト値を指定しておらず、前の行がスコープ外になるので、NULL が返っています。

PARTITION BY でグルーピングする

先ほどのクエリーから、WHERE StoreName = 'Store A' をとると、次のような結果セットが返ってきます。

SELECT  StoreName,
        SalesDate,
        SalesAmount,
        LAG(SalesAmount) OVER (ORDER BY SalesDate) AS PreviousSales
FROM    Sales
ORDER BY SalesDate;

[実行結果]

LAG 関数で前の行の SalesAmount は取得できていますが、Store A と Store B がまざって、あまり意味のない数字になってしまっています。

こんな時に便利なのが PARTITION BY です。

PARTITION BY で StoreName を指定することによって、店舗ごとにグルーピングされ、その中で SalesDate 順に並べられ、前の行の SalesAmount を取得することができます。

SELECT StoreName,
        SalesDate,
        SalesAmount,
        LAG(SalesAmount) OVER (PARTITION BY StoreName ORDER BY SalesDate) AS PreviousSales
FROM    Sales
ORDER BY StoreName,
         SalesDate;

[実行結果]

店舗ごとにグルーピングされて、SalesDate 順で前の行の SalesAmount が取得されているのがわかります。

1 行目と 4 行目はグループの 1 行目で、前の行がないので、PreviousSales が NULL になっています。

LAG 関数のデフォルト値を指定する

先ほどの結果セットのように LAG 関数は各グループの 1 行目は、前の行がないので NULL を返します。

第 3 引数にデフォルト値を設定することによって、NULL の代わりにその値を取得できます。

例えば、先ほどのクエリーで、デフォルト値を 0 に設定するには、次のようにできます。

SELECT StoreName,
        SalesDate,
        SalesAmount,
        LAG(SalesAmount, 1, 0) OVER (PARTITION BY StoreName ORDER BY SalesDate) AS PreviousSales
FROM    Sales
ORDER BY StoreName,
         SalesDate;

[実行結果]

第 3 引数のデフォルト値を指定するには、第 2 引数のオフセットも指定する必要があります。

LAG(SalesAmount, 1, 0) のように、オフセットは 1 を指定して、1 行前の値を取得するようにし、デフォルト値には 0 を指定しています。

実行結果を見ると、グループの 1 行目である、1 行目と 4 行目の PreviousSales が 0 になっていますね。

また、次のようにして LAG 関数を使って、売上の増減を取得することができます。

SELECT  StoreName,
        SalesDate,
        SalesAmount,
        LAG(SalesAmount, 1, 0) OVER (PARTITION BY StoreName ORDER BY SalesDate) AS PreviousSales,
        ISNULL(SalesAmount, 0) 
        - ISNULL(LAG(SalesAmount) OVER (PARTITION BY StoreName ORDER BY SalesDate), 0) AS SalesDifference
FROM    Sales
ORDER BY StoreName,
         SalesDate;

[実行結果]

SalesDifference を計算する際は、SalesAmount も PreviousSales も NULL の時は 0 として計算したいので、デフォルト値は指定せず、ISNULL 関数を使って、NULL を 0 として扱い、計算可能にしています。

SalesDifference として、売上の増減値が取得できていますね。

ここから、売上増減の比率を計算したり、売上が減った月だけを取得したり、データ分析に役立てることができます。

以上、LAG 関数の基本的な使い方から、実務で役立つ例などをご説明しました。

補足：LEAD 関数との違い

LEAD 関数については こちらの記事 をご覧ください。