T-SQL - LEAD 関数の使い方と活用例：次の行の値を取得する方法

LEAD 関数の使い方と活用例：次の行の値を取得する方法

T-SQL を使ってデータ分析をしていて、現在の行の値と次の行の値を比較したくなることありませんか？

例えば、売上が次の日に増えたかどうかや、ステータスが変わる次の値を確認したい時です。

そんなときに便利なのが LEAD 関数です。

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

LEAD 関数の基本的な使い方

SQL Server の LEAD 関数は、現在の行から「指定した行数分だけ次の値」を取得するウィンドウ関数です。

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

LEAD 関数の基本構文は以下の通りです。

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

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

[オフセット] はオプショナルの引数で、何行次のデータを取得するかを正の整数で指定します。

[オフセット] を指定しなかった場合のデフォルトは 1 で、1 行後の

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

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

[デフォルト値] 指定しなければ NULL になります。

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

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

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

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

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

SELECT  StoreName,
        SalesDate,
        SalesAmount
FROM    Sales;

[実行結果]

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

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

[実行結果]

SalesDate で並べて、1 行うしろの行の SalesAmount が NextSales に表示されています。

デフォルト値を指定しておらず、最後の行には「次の行」が存在しないため、NextSales が NULL になっています。

PARTITION BY でグルーピングする

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

SELECT  StoreName,
        SalesDate,
        SalesAmount,
        LEAD(SalesAmount) OVER (ORDER BY SalesDate) AS NextSales
FROM    Sales
ORDER BY SalesDate;

[実行結果]

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

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

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

SELECT StoreName,
        SalesDate,
        SalesAmount,
        LEAD(SalesAmount) OVER (PARTITION BY StoreName ORDER BY SalesDate) AS NextSales
FROM    Sales
ORDER BY StoreName,
         SalesDate;

[実行結果]

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

3 行目と 6 行目はグループの最後の行で、次の行がないので、NextSales が NULL になっています。

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

先ほどの例のように、各グループの最後の行で次の行が存在しない場合、LEAD 関数はデフォルトで NULL を返します。

NULL の代わりの値を指定したい場合、第 3 引数でデフォルト値を指定できます。

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

SELECT StoreName,
        SalesDate,
        SalesAmount,
        LEAD(SalesAmount, 1, SalesAmount) OVER (PARTITION BY StoreName ORDER BY SalesDate) AS NextSales
FROM    Sales
ORDER BY StoreName,
         SalesDate;

[実行結果]

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

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

実行結果を見ると、グループの最後の行である、3 行目と 6 行目の NextSales には、現在の行の SalesAmount が入っています。

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

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

[実行結果]

SalesDifference を計算する際は、SalesAmount も NextSales も NULL の時は 0 として計算したいので、ISNULL 関数を使って、NULL を 0 として扱い、計算可能にしています。

LEAD 関数 を使って、NextSales - SalesAmount = SalesDifference として、売上の増減値が取得できていますね。

デフォルト値を現在の行の SalesAmount にすることによって、グループの最後の行は、SalesDifference が 0 になるようにしています。

ここから、売上増減の比率を計算したり、売上の予測や傾向をみたり、データ分析に役立てることができます。

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

補足：LAG 関数との違い

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