SQLで「前の行と比べたい」「次の予定値も同じ結果セットで見たい」というときは、LAGとLEADが使えます。LAGは現在行より前、LEADは後の行の値を参照するウィンドウ関数です。自己結合を組まずに前回値・次回値を列として並べられるので、売上の前日差やログの前回時刻などを読みやすく書けます。ポイントは、どの行を前後とみなすかをOVER句のORDER BYで明確にすることです。
LAGは前の行、LEADは次の行を取る
SELECT
sales_date,
amount,
LAG(amount) OVER (ORDER BY sales_date) AS previous_amount,
LEAD(amount) OVER (ORDER BY sales_date) AS next_amount
FROM daily_sales
ORDER BY sales_date;LAG(amount)は1行前のamount、LEAD(amount)は1行後のamountを返します。先頭行には前の行がないためLAGはNULL、末尾行には次の行がないためLEADはNULLになります。
用語解説：ウィンドウ関数
結果行を集約して1行に潰さず、現在行と関連する行の集合を参照して計算するSQL関数です。LAG・LEADはOVER句と組み合わせて使います。
前回との差分をLAGで計算する
前日比や前回値との差を出すなら、取得した前行の値をそのまま計算式に使えます。
SELECT
sales_date,
amount,
LAG(amount) OVER (ORDER BY sales_date) AS previous_amount,
amount - LAG(amount) OVER (ORDER BY sales_date) AS difference
FROM daily_sales
ORDER BY sales_date;最初の行ではprevious_amountがNULLなのでdifferenceもNULLになります。0として扱うか、計算対象外とするかは集計仕様に合わせて決めてください。
ユーザー別・商品別ならPARTITION BYで区切る
複数ユーザーのログを一緒に並べる場合、ORDER BYだけでは別ユーザーの行を「前の行」として拾う可能性があります。グループごとに前後関係を分けたい場合はPARTITION BYを使います。
SELECT
user_id,
login_at,
LAG(login_at) OVER (
PARTITION BY user_id
ORDER BY login_at
) AS previous_login_at
FROM login_history
ORDER BY user_id, login_at;この書き方なら、各user_idの中だけで前回ログインを参照できます。商品・店舗・案件など、前後比較の単位がある場合も同じ考え方です。
2行前や範囲外の既定値も指定できる
LAGとLEADにはoffsetとdefaultを指定できます。offsetを2にすれば2行前・2行後を参照し、参照先が存在しないときはdefaultを返せます。
SELECT
sales_date,
amount,
LAG(amount, 2, 0) OVER (ORDER BY sales_date) AS two_rows_before
FROM daily_sales
ORDER BY sales_date;ただし、範囲外を0に置き換えると「データがない」と「値が0」を同じ意味にしてしまうことがあります。業務上の意味が異なるならNULLのまま扱うほうが安全です。
ORDER BYの決め方が結果を左右する
- 前後の基準にしたい列をOVER句のORDER BYへ指定する
- 同じ日時や同じ順位があり得る場合は、必要に応じて一意に順序を決められる列を追加する
- 表示用のORDER BYと、LAG・LEADのOVER句内ORDER BYは役割が異なることを意識する
- NULLを飛ばす挙動はDBMSごとに対応が異なるため、利用DBの公式仕様を確認する
LAG・LEADは「物理的に今見えている一つ上の行」を自動で選ぶのではありません。OVER句で定義した並び順に基づいて前後を決めます。想定外の結果になったら、まずORDER BYとPARTITION BYを確認してください。
自己結合と比べたときの使い分け
| 目的 | LAG・LEAD | 自己結合 |
|---|---|---|
| 同じ結果セットの直前・直後を参照 | 向いている | 書けるが条件が複雑になりやすい |
| 2行前・2行後 | offsetで指定しやすい | 結合条件の設計が必要 |
| 別テーブルの属性を結合 | 目的外 | JOINが適切 |
| 前後順序が重要 | OVERのORDER BYで明示 | 結合キーと条件で表現 |
前後行を参照する目的ならLAG・LEADが意図を表しやすい一方、別テーブルから属性を取る処理は通常のJOINの役割です。用途を混ぜずに選ぶとSQLを読みやすく保てます。
関連するSQLウィンドウ関数を確認する
行番号を振る用途はROW_NUMBER、重複行から1件を選ぶ用途はDISTINCT・GROUP BY・ROW_NUMBERの比較記事が参考になります。日付単位の集計や期間計算は日付関数の記事につながります。
（ROW_NUMBERで行に連番を付けるについては『MySQLのROW_NUMBER()ガイド｜最新データを1件だけ抽出する使い方』をご参照ください）
（重複除外でROW_NUMBERを使い分けるについては『SQL重複除外｜DISTINCT・GROUP BY・ROW_NUMBERの違い』をご参照ください）
（日付ベースの集計を組み合わせるについては『SQL日付関数ガイド｜現場で使えるテンプレと失敗回避』をご参照ください）
よくある疑問
LAGとLEADにORDER BYは必要？
どの行を前・次とみなすかを決めるため、OVER句では順序を明確にします。利用DBの構文要件も公式ドキュメントで確認してください。
最初の行でLAGがNULLになるのはなぜ？
参照する前の行が存在しないためです。必要ならdefault引数を指定できますが、NULLと0の意味を混同しないようにします。
LAG・LEADは自己結合より必ず速い？
性能を一律には断定できません。この記事では前後行を表す意図と可読性の観点で使い分けを説明しています。実データでは実行計画と計測で確認してください。
まとめ
LAGは前の行、LEADは次の行を参照します。差分計算ではまずORDER BYで前後の基準を決め、グループ単位ならPARTITION BYを追加します。先頭・末尾のNULLや同順位の並び順も確認すれば、自己結合を増やさず前回値・次回値を扱えます。
まず手元の売上やログデータで、LAGを使って前回値と差分を1列ずつ追加してみてください。
