Oracle SQLで前後の行を比較 — LAG/LEADで変化点を可視化

2025年6月3日2025年6月5日

URLをコピーしました！

ログや履歴データを扱っていて、「前回から何が変わったのか」を知りたいと思ったことはありませんか？

操作履歴の分析や異常検知など、隣接する行同士の比較が求められる場面は意外と多いものです。私自身もかつてはROWNUMや自己結合で苦戦していましたが、Oracle SQLのLAG() / LEAD()関数を知ってからは状況が一変しました（大げさかもですが）。

この記事では、前後の行を参照する方法やその実践的な使い方について、サンプルSQLとともに解説します。

この記事を読むとわかること

LAG() / LEAD()を使った前後行の比較方法
操作履歴や異常値の検出など、実務での活用パターン
ORDER BYの使い方やROWNUMとの違いに注意するポイント

Oracle SQLで前後の行を比較したい場面とは

ログや履歴データを扱っていると、隣接するレコード同士の比較が必要になる場面は案外多いです。とくに、前の操作から「何がどう変わったのか」を明確にしたいときには、前後の行の値を突き合わせる必要があります。

そうした場面で力を発揮するのが、Oracle SQLの分析関数です。

ログや履歴データの分析で出てくる典型的な要件

ユーザー操作のログや、商品状態の変化履歴などでは、前後の状態がどれくらい違うか、何が更新されたかを知ることが肝になります。たとえば以下のような要件です：

同一ユーザーによる操作の変遷を追いたい
ステータスが変化したポイントを抽出したい
突然の異常値の前後を特定したい

こうした比較には、単一レコードの値だけではなく、「ひとつ前の行」「次の行」のデータが必要になります。

「どうやって前の行を参照するのか」問題

SQLを書き慣れていない頃は、ROWNUMや自己結合などでなんとかならないかと苦戦しました。でも、並び順が安定しない・ロジックが複雑になるといった理由から、実用には耐えませんでした。

その後、LAG()やLEAD()といった分析関数を知り、行間比較が驚くほどシンプルに書けるとわかったのはちょっとした衝撃でした。以降、監査ログや履歴データでは頻繁に活用しています。

LAG・LEAD関数で実現する前後行の比較

LAG()とLEAD()は、「ある行の前・次の行の値を参照したい」という要望に特化した関数です。

順序が明確でないと意味を成さないため、OVER句で並び順を指定するのが必須です。

基本構文とOVER句の使い方

LAG/LEADの構文は以下のとおりです。

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

LAG(列名)：直前の行の値を取得します
LEAD(列名)：直後の行の値を取得します
オフセット：何行前・何行後を参照するか（省略時は1）
デフォルト値：行が存在しない場合に代入される値（省略可）

では、実際のSQLで確認してみましょう。

-- 各行に対して、前後のアクションを付与する
WITH logs AS (
  SELECT 'A001' AS user_id, TO_DATE('2024-01-01', 'YYYY-MM-DD') AS log_date, 'LOGIN' AS action FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-02', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-03', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-04', 'YYYY-MM-DD'), 'LOGOUT' FROM dual
)
SELECT
  user_id,
  log_date,
  action,
  LAG(action) OVER (PARTITION BY user_id ORDER BY log_date) AS prev_action,
  LEAD(action) OVER (PARTITION BY user_id ORDER BY log_date) AS next_action
FROM logs;

このSQLのポイント：

PARTITION BY user_id：ユーザーごとに比較する（別ユーザーがいても混ざらない）
ORDER BY log_date：日付順で並び替える
LAG(action)とLEAD(action)で前後のaction列を参照

結果は以下の通りです。

USER_ID	LOG_DATE	ACTION	PREV_ACTION	NEXT_ACTION
A001	2024-01-01	LOGIN	(null)	EDIT
A001	2024-01-02	EDIT	LOGIN	EDIT
A001	2024-01-03	EDIT	EDIT	LOGOUT
A001	2024-01-04	LOGOUT	EDIT	(null)

このように、今の行と前後の状態を並べることができるので、「どこで変化があったか」や「特定操作の前後は何だったか」といった分析がしやすくなります。

差分カラムの追加による変化点の可視化

前後の行を取得できるようになったら、次にやりたいのは「何か変わったのか？」の判定です。

LAG()を活用すれば、現在の値と前の値を比較し、変化があったかどうかを簡単にフラグ化できます。

-- 前のアクションと違う場合は変化としてフラグを立てる
WITH logs AS (
  SELECT 'A001' AS user_id, TO_DATE('2024-01-01', 'YYYY-MM-DD') AS log_date, 'LOGIN' AS action FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-02', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-03', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-04', 'YYYY-MM-DD'), 'LOGOUT' FROM dual
)
SELECT
  user_id,
  log_date,
  action,
  LAG(action) OVER (PARTITION BY user_id ORDER BY log_date) AS prev_action,
  CASE 
    WHEN action != LAG(action) OVER (PARTITION BY user_id ORDER BY log_date)
    THEN 'Y' ELSE 'N' 
  END AS is_changed
FROM logs;

このSQLでは、前回と違う操作があった場合に is_changed = 'Y' としてフラグが立ちます。

USER_ID	LOG_DATE	ACTION	PREV_ACTION	IS_CHANGED
A001	2024-01-01	LOGIN	(null)	N
A001	2024-01-02	EDIT	LOGIN	Y
A001	2024-01-03	EDIT	EDIT	N
A001	2024-01-04	LOGOUT	EDIT	Y

この形式で見せると、どこで変化があったのかが非常にわかりやすくなります。監査レポートやアラートの判定処理でも使える考え方です。

昇順と降順の並びで結果が変わることに注意

LAG・LEAD関数で重要なポイントは「並び順によって結果が変わる」という点です。

LAG()やLEAD()はORDER BYで指定した順序に基づいて“前”や“後”を判断するため、並びの方向が逆になれば、当然参照される行も変わってしまいます。

以下は降順にした例です。

-- 降順で比較：最新のログを上にしたいケース
WITH logs AS (
  SELECT 'A001' AS user_id, TO_DATE('2024-01-01', 'YYYY-MM-DD') AS log_date, 'LOGIN' AS action FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-02', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-03', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-04', 'YYYY-MM-DD'), 'LOGOUT' FROM dual
)
SELECT
  user_id,
  log_date,
  action,
  LAG(action) OVER (PARTITION BY user_id ORDER BY log_date DESC) AS prev_action,
  LEAD(action) OVER (PARTITION BY user_id ORDER BY log_date DESC) AS next_action
FROM logs;

結果は以下のようになります：

USER_ID	LOG_DATE	ACTION	PREV_ACTION	NEXT_ACTION
A001	2024-01-04	LOGOUT	(null)	EDIT
A001	2024-01-03	EDIT	LOGOUT	EDIT
A001	2024-01-02	EDIT	EDIT	LOGIN
A001	2024-01-01	LOGIN	EDIT	(null)

これを見ると、LAG()で参照される“前の行”が日付的には“次の日の操作”になっていることが分かります。

ログの時系列を逆順で表示したいときなどに使いますが、比較の意図によっては誤解を招くので、常にORDER BYを意識する必要があります。

ROW_NUMBERやPARTITIONでできることと限界

LAG()やLEAD()が「前後の行の値を直接参照する」のに対し、ROW_NUMBER()は「順序付きの一意な行番号」を振ることで、比較や抽出の補助になります。

こちらもOVER()句の中でPARTITION BYとORDER BYを指定することで、グループごとに“並びを意識した分析”が可能になります。

グループごとにリセットする行番号の使い道

たとえば、ユーザーごとに操作の順番を番号で振りたい場合は以下のように書きます。

WITH logs AS (
  SELECT 'A001' AS user_id, TO_DATE('2024-01-01', 'YYYY-MM-DD') AS log_date, 'LOGIN' AS action FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-02', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-03', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-04', 'YYYY-MM-DD'), 'LOGOUT' FROM dual
)
SELECT
  user_id,
  log_date,
  action,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn
FROM logs;

この結果は以下の通りです：

USER_ID	LOG_DATE	ACTION	RN
A001	2024-01-01	LOGIN	1
A001	2024-01-02	EDIT	2
A001	2024-01-03	EDIT	3
A001	2024-01-04	LOGOUT	4

このRN列を利用すれば、たとえば「前の行をJOINで結合して比較」するといった工夫も可能です（ただし、分析関数で済む場合はそちらを優先した方がスマートです）。

ROWNUMとROW_NUMBERの違いを実感した話

一見似ているROWNUMという擬似列がありますが、これは「結果セットの取得順」で付与される番号です。

よく使われる用途は「最初の1件だけ取得したい」といったときですが、ORDER BYよりも前に評価されるため、並び順が意図通りにならないことがよくあります。

たとえば、次のようなクエリでは、意図しない結果になる可能性があります。

-- 降順で1件…のつもりが、ORDER BYの前にROWNUMが評価されてしまう
SELECT *
FROM (
  SELECT * FROM logs ORDER BY log_date DESC
)
WHERE ROWNUM = 1;

正しくはこうです：

-- ROW_NUMBERで順位をつけてから1件抽出
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY log_date DESC) AS rn
  FROM logs
)
WHERE rn = 1;

分析関数で振った行番号はORDER BYの後に評価されるため、安定した結果が得られます。

この違い、実務で一度はひっかかるところだと思います。

実務で使えるパターン別SQLレシピ集

ここでは、実際の業務で使える比較パターンを紹介します。いずれも、前後の行比較をベースにした典型的な処理です。

連続値の変化をフラグで示す

まずはシンプルな差分検出です。。前回と同じ操作が続く場合は無視、異なる操作に変わったタイミングでフラグを立てます。

WITH logs AS (
  SELECT 'A001' AS user_id, TO_DATE('2024-01-01', 'YYYY-MM-DD') AS log_date, 'LOGIN' AS action FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-02', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-03', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-04', 'YYYY-MM-DD'), 'LOGOUT' FROM dual
)
SELECT
  user_id,
  log_date,
  action,
  CASE 
    WHEN action != LAG(action) OVER (PARTITION BY user_id ORDER BY log_date)
    THEN 1 ELSE 0 
  END AS changed
FROM logs;

USER_ID	LOG_DATE	ACTION	CHANGED
A001	2024-01-01	LOGIN	0
A001	2024-01-02	EDIT	1
A001	2024-01-03	EDIT	0
A001	2024-01-04	LOGOUT	1

このフラグをもとに、変化点だけを抽出したり、連続状態の継続・終了を追跡できます。

異常値の前後を抽出するSQL

次に、異常値が発生した際、その“前後の操作”も一緒に調べたいケースです。ログの文脈をつかむために前後の行を含めて確認します。

-- LOGOUT を異常とみなして、その直前と直後のアクションを確認
WITH logs AS (
  SELECT 'A001' AS user_id, TO_DATE('2024-01-01', 'YYYY-MM-DD') AS log_date, 'LOGIN' AS action FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-02', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-03', 'YYYY-MM-DD'), 'EDIT' FROM dual UNION ALL
  SELECT 'A001', TO_DATE('2024-01-04', 'YYYY-MM-DD'), 'LOGOUT' FROM dual
)
SELECT *
FROM (
  SELECT
    user_id,
    log_date,
    action,
    LAG(action) OVER (PARTITION BY user_id ORDER BY log_date) AS prev_action,
    LEAD(action) OVER (PARTITION BY user_id ORDER BY log_date) AS next_action
  FROM logs
) t
WHERE action = 'LOGOUT';

USER_ID	LOG_DATE	ACTION	PREV_ACTION	NEXT_ACTION
A001	2024-01-04	LOGOUT	EDIT	(null)

これを応用すれば、複数の異常アクションに対応したり、異常の「直前だけ」や「直後だけ」に絞ることも可能です。

まとめ：比較用の分析関数は“変化”を見るための武器になる

監査、異常検知、履歴の変化点確認。業務でこうした分析を任されることが増えてきた今、LAG()やLEAD()のような分析関数は欠かせません。

何が変わったのか。どのタイミングだったのか。そういった「差分の背景」を読み解くために、比較可能なデータの並びを自分で作れることが、ひとつの技術になっていると感じています。

この記事を書いた人

tomo

業務システムとWebアプリの開発に20年以上携わるフリーランスエンジニア。
製造業や物流業界のシステム保守・改修を中心に、要件定義から運用改善まで幅広く対応してきました。Laravelや業務改善、AI活用など、現場で実際に試し・使い続けている技術や設計の工夫を、トラブル対応の視点も交えてブログに記録しています。

日々の業務で直面した「困ったこと」をベースに、再現性のあるノウハウをシンプルな言葉で伝えることを意識しています。