SQLのLAG・LEADとは?前の行・次の行の値、前月比、既定値をPARTITION BYで解説
LAGは現在行より前の行、LEADは後ろの行の値を同じ行に並べて取り出すウィンドウ関数です。自己結合を使わずに、前月との差や次の予定との比較を書けます。店舗別の月間売上で、基本、差分、既定値、グループ分け、変化の抽出まで進みましょう。
例で使う表
store_salesの1行は「1店舗・1か月」の売上です。銀座は1〜4月、渋谷は1〜3月のデータがあります。銀座の4月は3月と、渋谷の2月は1月と同額なので、変化の有無も確認できます。各例の末尾のORDER BYは、結果表の表示順を決めるために付けています。
| id | store | month_no | amount |
|---|---|---|---|
| 1 | 銀座 | 1 | 100 |
| 2 | 銀座 | 2 | 120 |
| 3 | 銀座 | 3 | 90 |
| 4 | 銀座 | 4 | 90 |
| 5 | 渋谷 | 1 | 80 |
| 6 | 渋谷 | 2 | 80 |
| 7 | 渋谷 | 3 | 110 |
LAG・LEADの基本構文
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を省くと、前後の意味が定まりません。
SELECT month_no, amount,
LAG(amount) OVER (ORDER BY month_no) AS prev_amount
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;| month_no | amount | prev_amount |
|---|---|---|
| 1 | 100 | NULL |
| 2 | 120 | 100 |
| 3 | 90 | 120 |
| 4 | 90 | 90 |
LEADで次の月の売上を同じ行に並べる
LEADはLAGと逆に、並び順で後ろの行の値を返します。銀座の各月に翌月の売上を添えると、最後の4月には次の行がないのでNULLになります。「今月の値と来月の予定を並べる」「次の変化までの距離を見る」場面で使います。LAGとLEADは向きが違うだけで、引数の書き方は同じです。
SELECT month_no, amount,
LEAD(amount) OVER (ORDER BY month_no) AS next_amount
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;| month_no | amount | next_amount |
|---|---|---|
| 1 | 100 | 120 |
| 2 | 120 | 90 |
| 3 | 90 | 90 |
| 4 | 90 | NULL |
LAGと引き算で前月との差分を求める
前月比の差は、amount - LAG(amount) OVER (...)と書きます。銀座は2月が+20、3月が-30、4月が0です。1月は前月がNULLなので、引き算の結果もNULLになります。NULLを0扱いにして差を出すのは、比較できない月を「変化した」と誤読させるので、先頭行はNULLのまま残すのが基本です。
SELECT month_no, amount,
amount - LAG(amount) OVER (ORDER BY month_no) AS diff
FROM store_sales
WHERE store = '銀座'
ORDER BY month_no;| month_no | amount | diff |
|---|---|---|
| 1 | 100 | NULL |
| 2 | 120 | 20 |
| 3 | 90 | -30 |
| 4 | 90 | 0 |
第2引数のオフセットと第3引数の既定値を使う
LAG(値, オフセット, 既定値)の第2引数は何行前か(省略時は1)、第3引数は該当行がないときに返す値(省略時はNULL)です。LAG(amount, 2, 0)なら2行前を見て、2行前がない1月と2月は0になります。オフセットは「行の数」であり、月数ではありません。既定値は値と互換性のある型で指定します。
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_no | amount | two_back |
|---|---|---|
| 1 | 100 | 0 |
| 2 | 120 | 0 |
| 3 | 90 | 100 |
| 4 | 90 | 120 |
PARTITION BYで店舗ごとに区切る
PARTITION BY storeを加えると、店舗ごとに別の並びとして前後を探します。渋谷の1月は渋谷の先頭行なので、直前の行が銀座の4月であってもNULLのままです。PARTITION BYを書かないと、全店の行が1本の並びとして扱われ、店舗をまたいだ値が混ざります。グループごとの前月比には、PARTITION BYとORDER BYの両方が必要です。
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;| id | store | month_no | amount | prev_amount |
|---|---|---|---|---|
| 1 | 銀座 | 1 | 100 | NULL |
| 2 | 銀座 | 2 | 120 | 100 |
| 3 | 銀座 | 3 | 90 | 120 |
| 4 | 銀座 | 4 | 90 | 90 |
| 5 | 渋谷 | 1 | 80 | NULL |
| 6 | 渋谷 | 2 | 80 | 80 |
| 7 | 渋谷 | 3 | 110 | 80 |
前月から変化があった行だけを抜き出す
LAGの結果はWHEREに直接書けません。ウィンドウ関数はWHEREより後に計算されるためです。サブクエリで前月の値を列として作り、外側のWHEREでamountと比べます。amount <> prev_amountなら、同額の銀座4月・渋谷2月と、前月がNULLの各店1月は残りません。NULLとの比較は真にならないためです。先頭行も残したいときは、IS DISTINCT FROMで比べます。
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;| id | store | month_no | amount | prev_amount |
|---|---|---|---|---|
| 2 | 銀座 | 2 | 120 | 100 |
| 3 | 銀座 | 3 | 90 | 120 |
| 7 | 渋谷 | 3 | 110 | 80 |
LAG・LEADでよくある間違い
PARTITION BYを忘れて店舗をまたぐ
グループ別の前月比でPARTITION BYを書かないと、渋谷の1月に銀座4月の値が前月として入ります。エラーにならず、もっともらしい数字が出るので気づきにくい誤りです。
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のままにします。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:ウィンドウ関数(lag・lead) — lag・leadの引数(offset・default)と、IGNORE NULLSが未実装であることを確認
- PostgreSQL公式:ウィンドウ関数チュートリアル — OVER・PARTITION BY・ORDER BYの基本