SQLのウィンドウ関数（LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE）を使ってみよう！

2024.07.23 株式会社システムインテグレータ

　今回の初心者訓練所#17は、現在の行から前や後の行の値を取得したり、先頭行や最終行、または、指定した行の値を取得したりできる便利な5つのウィンドウ関数を説明していきます。基本的な構文は今迄紹介してきたウィンドウ関数と同様なので、是非、まとめて覚えてしまいましょう。データベースは、いつもの「SQLite」を使用していきますので、実践しながら確認していきましょう。

LAG

　LAG 関数は、現在の行から指定された位置（行数）だけ前の行の値を取得するために使用されます。これは、前のデータと比較したり、トレンドを分析したりする際に非常に便利です。例えば、前月の売上と現在の売上を比較するような場合に役立ちます。
構文は下記になります。

LAG(項目A , [指定位置] , [戻り値]) OVER ( [ PARTITION BY 項目B, [項目C], ...]
ORDER BY 項目D, [項目E], ... [ASC|DESC])
項目A：参照したい列名
指定位置：どの位前の行かを指定(省略した場合は、1)
戻り値：前の行が存在しない場合に返す値(省略した場合は、NULL値)
※[ ]内は省略可能です。

確認の準備として、月次売上テーブルを作成して、データを挿入します。

【月次売上テーブルのCREATE文とINSERT文】

CREATE TABLE tbl_monthly_sa (
sales_month varchar not null
, sales_amt integer default 0 not null,
PRIMARY KEY( sales_month ) );

NSERT INTO tbl_monthly_sa ( sales_month, sales_amt ) VALUES
('2024-01-01','100000')
,('2024-02-01','150000')
,('2024-03-01','130000')
,('2024-04-01','140000')
,('2024-05-01','200000')
,('2024-06-01','190000')
,('2024-07-01','180000');

blog17-01
出力フォーマットを調整して、SELECT文で登録内容を確認してみましょう。

.headers on (ヘッダ情報を出力)
.mode column (カラム毎に左揃え出力)

SELECT * FROM tbl_monthly_sa;

blog17-02

INSERT文で作成した、7件のデータを確認できます。

(1) それでは、１カ月前の売上金額(sales_amt)を取得してみましょう。
ORDER BY句には売上月(sales_month)を指定して、売上月の昇順でデータを取得します。
指定位置、戻り値は指定しないで実行してみます。

SELECT
sales_month,
sales_amt,
LAG(sales_amt) OVER (ORDER BY sales_month ASC) AS LAG
FROM tbl_monthly_sa;

blog17-03

2024-01-01のレコードは先頭になるので、１カ月前のレコードが存在しないため、LAGの値がNULLとなってます。それ以外のレコードは、それぞれ前月のレコードの売上金額(sales_amt)を取得していることが確認できます。

(2) 次は、2カ月前の売上金額を取得してみましょう。
指定位置に2を、戻り値に0を指定して実行してみます。

SELECT
sales_month,
sales_amt,
LAG(sales_amt , 2 , 0) OVER (ORDER BY sales_month ASC) AS LAG
FROM tbl_monthly_sa;

blog17-04

2024-01-01と2024-02-01のレコードの２カ月前のレコードが存在しないため、LAGの値が戻り値に指定した０となってます。それ以外のレコードは、それぞれ２カ月前のレコードの売上金額(sales_amt)を取得していることが確認できます。

LEAD

　LEAD関数は、現在の行から指定された位置（行数）だけ後の行の値を取得するために使用されます。LAG関数とは反対に、対象の月の売上と次月の売上を比較するような場合に便利です。
構文はLAG関数と同様で下記になります。

LEAD(項目A , [指定位置] , [戻り値]) OVER ( [ PARTITION BY 項目B, [項目C], ...]
ORDER BY 項目D, [項目E], ... [ASC|DESC])
項目A：参照したい列名
指定位置：どの位後の行かを指定(省略した場合は、1)
戻り値：後の行が存在しない場合に返す値(省略した場合は、NULL値)
※[ ]内は省略可能です。

(1) それでは、１カ月後の売上金額(sales_amt)を取得してみましょう。
先程と同様に、ORDER BY句には売上月(sales_month)を指定して売上月の昇順でデータを取得します。
指定位置、戻り値は指定しないで実行してみます。

SELECT
sales_month,
sales_amt,
LEAD(sales_amt) OVER (ORDER BY sales_month ASC) AS LEAD
FROM tbl_monthly_sa;

blog17-05

2024-07-01のレコードは最後になるので、１カ月後のレコードが存在しないため、LEADの値がNULLとなってます。それ以外のレコードは、それぞれ次月のレコードの売上金額(sales_amt)を取得していることが確認できます。

(2) 次は、3カ月後の売上金額を取得してみましょう。
指定位置に3を、戻り値に0を指定して実行してみます。

SELECT
sales_month,
sales_amt,
LEAD(sales_amt , 3 , 0) OVER (ORDER BY sales_month ASC) AS LEAD
FROM tbl_monthly_sa;

blog17-06

2024-07-01と2024-06-01と2024-05-01のレコードの3カ月後のレコードが存在しないため、LEADの値が戻り値に指定した０となってます。それ以外のレコードは、それぞれ3カ月後のレコードの売上金額(sales_amt)を取得していることが確認できます。

FIRST_VALUE

　FIRST_VALUE 関数は、指定されたウィンドウ枠内の最初の値を返します。この関数は、特定のグループ内で最初の値を取得したい場合や、特定の並び順で最初の値を確認したい場合に便利な関数です。
構文は下記になります。

FIRST_VALUE(項目A) OVER ([PARTITION BY 項目B, [項目C], ...]
ORDER BY 項目D, [項目E], ... [ASC|DESC]
[ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW])
ROWS BETWEEN：ウィンドウ枠を指定します。省略した場合、上記で記述されたウィンドウの開始から現在の行(UNBOUNDED PRECEDING AND CURRENT ROW)までの範囲が指定されます。
　　※[ ]内は省略可能です。
　　　ROWS BETWEENの詳細は、「初心者訓練所#17」を参照してください。

(1) それでは、各行に売上月を昇順にした先頭行の売上金額を表示してみましょう。
PARTITION BY 、ROWS BETWEEN は省略してみます。

SELECT
sales_month,
sales_amt,
FIRST_VALUE(sales_amt) OVER (ORDER BY sales_month ASC) AS FIRST
FROM tbl_monthly_sa;

blog17-07

各行に先頭行(2024-01-01)の売上金額が表示されていることが確認できます。

(2) 次は、売上月を降順にしてみましょう。

SELECT
sales_month,
sales_amt,
FIRST_VALUE(sales_amt) OVER (ORDER BY sales_month DESC) AS FIRST
FROM tbl_monthly_sa;

blog17-08

今度は、各行に売上月の降順の先頭行(2024-07-01)の売上金額が表示されていることが確認できます。

LAST_VALUE

　LAST_VALUE 関数は、指定されたウィンドウ枠内の最後の値を返します。この関数は、特定のグループ内で最後の値を取得したい場合や、特定の並び順で最後の値を確認したい場合に便利な関数です。
構文はFIRST_VALUEと同様に、下記になります。

LAST_VALUE(項目A) OVER ([PARTITION BY 項目B, [項目C], ...]
ORDER BY 項目D, [項目E], ... [ASC|DESC]
[ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW])
ROWS BETWEEN：ウィンドウ枠を指定します。省略した場合、上記で記述されたウィンドウの開始から現在の行(UNBOUNDED PRECEDING AND CURRENT ROW)までの範囲が指定されます。
　　※[ ]内は省略可能です。
　　　ROWS BETWEENの詳細は、「初心者訓練所#17」を参照してください。

(1) それでは、各行に売上月を昇順にした最終行の売上金額を表示してみましょう。
PARTITION BY 、ROWS BETWEEN は省略してみます。

SELECT
sales_month,
sales_amt,
LAST_VALUE(sales_amt) OVER (ORDER BY sales_month ASC) AS LAST
FROM tbl_monthly_sa;

blog17-09

想定した結果と違い、各行のLASTには各行の売上金額と同じ値が表示されています。
これは、ROWS BETWEENを省略すると、構文で記述されているように、ウィンドウの開始から現在の行までの範囲が指定されてしまいます。これを回避するためには、ウィンドウ枠の範囲をウィンドウの開始から最終行までを指定する必要があります。
最終行まで範囲指定するには、
「ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING」と記述します。LAST_VALUE関数を使用する場合は、ROWS BETWEENを指定しなければ、想定した結果が得られない場合がありますので注意してください。

(2) それでは、長文で大変ですが、上記のROWS BETWEENを記述して確認してみましょう。

SELECT
sales_month,
sales_amt,
LAST_VALUE(sales_amt) OVER (ORDER BY sales_month ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS LAST
FROM tbl_monthly_sa;

blog17-10

今度は、各行に売上月の最終行(2024-07-01)の売上金額が表示されていることが確認できます。

NTH_VALUE

　NTH_VALUE 関数は、指定されたウィンドウ枠内の n 番目の値を返します。この関数は、特定のグループ内で n 番目の値を取得したい場合や、特定の順序で n 番目の値を確認したい場合に便利な関数です。
構文はFIRST_VALUEと同様に、下記になります。

NTH_VALUE(項目A) OVER ([PARTITION BY 項目B, [項目C], ...]
ORDER BY 項目D, [項目E], ... [ASC|DESC]
[ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW])
ROWS BETWEEN：ウィンドウ枠を指定します。省略した場合、上記で記述されたウィンドウの開始から現在の行(UNBOUNDED PRECEDING AND CURRENT ROW)までの範囲が指定されます。
　　※[ ]内は省略可能です。
　　　ROWS BETWEENの詳細は、「初心者訓練所#17」を参照してください。

(1) それでは、各行に売上月を昇順にした3行目の売上金額を表示してみましょう。
PARTITION BY 、ROWS BETWEEN は省略してみます。

SELECT
sales_month,
sales_amt,
NTH_VALUE(sales_amt , 3) OVER (ORDER BY sales_month ASC) AS NTH
FROM tbl_monthly_sa;

blog17-11

こちらも、各行に3行目の売上金額の130,000が表示されると思いましたが、１行目と２行目の値がNULLとなっています。LAST_VALUE関数と同様に、ROWS BETWEENを省略すると、構文で記述されているように、ウィンドウの開始から現在の行までの範囲が指定されてしまいますので、3番目を指定すると1行目と2行目の場合は3行目を認識できないのでNULLとなってしまいます。認識できるようにするには、ROWS BETWEENでウィンドウの開始から最終行までの範囲を指定する必要があります。
NTH_VALUE関数を使用する場合も、ROWS BETWEENを指定しなければ、想定した結果が得られない場合がありますので注意してください。

(2) それでは、ROWS BETWEENで最終行まで対象にして確認してみましょう。

SELECT
sales_month,
sales_amt,
NTH_VALUE(sales_amt , 3) OVER (ORDER BY sales_month ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS NTH
FROM tbl_monthly_sa;

blog17-12

今度は、各行に3行目の売上金額の130,000が表示されていることが確認できます。

あとがき

　「初心者訓練所」の第17弾では、ポイントを指定して参照できる便利な5つのウィンドウ関数について説明してきました。これらの関数を使いこなすことで、データセットの中の特定のポイントを容易に参照することができ、複雑なデータ分析がより直感的かつ効率的に実施できるようになると考えます。本記事の内容が、皆様のデータ操作や分析に少しでも役に立つことを願っています。今後もSQLのさらなるテクニックや応用例について取り上げていく予定ですので、次回の記事も、どうぞお楽しみに！

(株)システムインテグレータ　TOPSICチーム　元鬼軍曹　久保　司