SQL練習室
← SQLガイド

SQLのLAG・LEADとは?前の行・次の行の値、前月比、既定値をPARTITION BYで解説

LAGは現在行より前の行、LEADは後ろの行の値を同じ行に並べて取り出すウィンドウ関数です。自己結合を使わずに、前月との差や次の予定との比較を書けます。店舗別の月間売上で、基本、差分、既定値、グループ分け、変化の抽出まで進みましょう。

例で使う表

store_salesの1行は「1店舗・1か月」の売上です。銀座は1〜4月、渋谷は1〜3月のデータがあります。銀座の4月は3月と、渋谷の2月は1月と同額なので、変化の有無も確認できます。各例の末尾のORDER BYは、結果表の表示順を決めるために付けています。

store_sales:店舗別の月間売上7件(amountは万円)
idstoremonth_noamount
1銀座1100
2銀座2120
3銀座390
4銀座490
5渋谷180
6渋谷280
7渋谷3110

LAG・LEADの基本構文

sql
SELECT store, month_no, amount,
  LAG(amount, 1, 0) OVER (PARTITION BY store ORDER BY month_no)
FROM store_sales;

LAGで前の月の売上を同じ行に並べる

LAG(値) OVER (ORDER BY 並び順)は、並び順で1つ前の行の値を返します。ここでは銀座だけに絞り、月順に前月の売上を添えます。1月には前の行がないので、結果はNULLです。この先頭行のNULLは「前月が0だった」のではなく「前月の行がない」ことを表します。OVER内のORDER BYを省くと、前後の意味が定まりません。

sql
SELECT month_no, amount,
  LAG(amount) OVER (ORDER BY month_no) AS prev_amount
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;
実行結果
month_noamountprev_amount
1100NULL
2120100
390120
49090

LEADで次の月の売上を同じ行に並べる

LEADはLAGと逆に、並び順で後ろの行の値を返します。銀座の各月に翌月の売上を添えると、最後の4月には次の行がないのでNULLになります。「今月の値と来月の予定を並べる」「次の変化までの距離を見る」場面で使います。LAGとLEADは向きが違うだけで、引数の書き方は同じです。

sql
SELECT month_no, amount,
  LEAD(amount) OVER (ORDER BY month_no) AS next_amount
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;
実行結果
month_noamountnext_amount
1100120
212090
39090
490NULL

LAGと引き算で前月との差分を求める

前月比の差は、amount - LAG(amount) OVER (...)と書きます。銀座は2月が+20、3月が-30、4月が0です。1月は前月がNULLなので、引き算の結果もNULLになります。NULLを0扱いにして差を出すのは、比較できない月を「変化した」と誤読させるので、先頭行はNULLのまま残すのが基本です。

sql
SELECT month_no, amount,
  amount - LAG(amount) OVER (ORDER BY month_no) AS diff
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;
実行結果
month_noamountdiff
1100NULL
212020
390-30
4900

第2引数のオフセットと第3引数の既定値を使う

LAG(値, オフセット, 既定値)の第2引数は何行前か(省略時は1)、第3引数は該当行がないときに返す値(省略時はNULL)です。LAG(amount, 2, 0)なら2行前を見て、2行前がない1月と2月は0になります。オフセットは「行の数」であり、月数ではありません。既定値は値と互換性のある型で指定します。

sql
SELECT month_no, amount,
  LAG(amount, 2, 0) OVER (ORDER BY month_no) AS two_back
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;
実行結果
month_noamounttwo_back
11000
21200
390100
490120

PARTITION BYで店舗ごとに区切る

PARTITION BY storeを加えると、店舗ごとに別の並びとして前後を探します。渋谷の1月は渋谷の先頭行なので、直前の行が銀座の4月であってもNULLのままです。PARTITION BYを書かないと、全店の行が1本の並びとして扱われ、店舗をまたいだ値が混ざります。グループごとの前月比には、PARTITION BYとORDER BYの両方が必要です。

sql
SELECT id, store, month_no, amount,
  LAG(amount) OVER (PARTITION BY store ORDER BY month_no) AS prev_amount
FROM store_sales
ORDER BY id;
実行結果
idstoremonth_noamountprev_amount
1銀座1100NULL
2銀座2120100
3銀座390120
4銀座49090
5渋谷180NULL
6渋谷28080
7渋谷311080

前月から変化があった行だけを抜き出す

LAGの結果はWHEREに直接書けません。ウィンドウ関数はWHEREより後に計算されるためです。サブクエリで前月の値を列として作り、外側のWHEREでamountと比べます。amount <> prev_amountなら、同額の銀座4月・渋谷2月と、前月がNULLの各店1月は残りません。NULLとの比較は真にならないためです。先頭行も残したいときは、IS DISTINCT FROMで比べます。

sql
SELECT id, store, month_no, amount, prev_amount
FROM (
  SELECT id, store, month_no, amount,
    LAG(amount) OVER (PARTITION BY store ORDER BY month_no) AS prev_amount
  FROM store_sales
) t
WHERE amount <> prev_amount
ORDER BY id;
実行結果
idstoremonth_noamountprev_amount
2銀座2120100
3銀座390120
7渋谷311080

LAG・LEADでよくある間違い

PARTITION BYを忘れて店舗をまたぐ

グループ別の前月比でPARTITION BYを書かないと、渋谷の1月に銀座4月の値が前月として入ります。エラーにならず、もっともらしい数字が出るので気づきにくい誤りです。

sql
SELECT id, store, month_no, amount,
  LAG(amount) OVER (ORDER BY id) AS prev_amount
FROM store_sales
ORDER BY id;

既定値0で先頭月が大きな増加に見える

差分を出すときにLAG(amount, 1, 0)とすると、各店の1月は「前月0から増加」になり、1月の売上そのものが差分として出ます。前月がない行と前月が0の行を区別したいときは、既定値を付けずNULLのままにします。

sql
SELECT id, store, month_no,
  amount - LAG(amount, 1, 0) OVER (PARTITION BY store ORDER BY month_no) AS diff
FROM store_sales
ORDER BY id;

実際に1問解いてみる

LAG

A店のmonth_no、amountに前月の売上を添え、月順で取得してください。前月がない行はNULLのままで、前月の列にはAS別名を付けずLAGの既定名を使います。

テーブル

読み込み中…

実行結果

関連構文

LAG・LEADについてよくある質問

LAGとLEADの違いは何ですか?
LAGは並び順で前の行、LEADは後ろの行の値を返します。引数は同じで、LEAD(amount) OVER (ORDER BY month_no)とLAG(amount) OVER (ORDER BY month_no DESC)は、並びの向きを変えると同じ値になります。
前月比の増減率はどう求めますか?
(amount - LAG(amount) OVER (...)) / LAG(amount) OVER (...)のように、差を前月の値で割ります。前月が0やNULLの行は割り算できないため、NULLIF(LAG(...), 0)で0除算を避けると安全です。
LAGをWHEREに直接書けますか?
書けません。ウィンドウ関数はSELECT句とORDER BY句だけで使えます。変化の抽出のように条件にしたいときは、サブクエリかWITH(CTE)で列として計算してから絞り込みます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。