SQL Interview Prep · レッスン

LAGとLEADで隣接行を参照する

Q: 「LAGとLEADで隣接行を参照する」で何を学びますか？

SELF JOINを使わずに前後の行の値を取得します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SELF JOINを使わずに前後の行の値を取得します。

レッスン 1/413 ステップ

「LAGとLEADで隣接行を参照する」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

面接官が尋ねる質問

分析職の面接でよく出る質問の1つに、「自己結合を使わずに、各行を直前の行と比較してください」というものがあります。月ごとの売上の比較、ユーザーの直前のログイン、またはシーケンス内の次のイベントなどを考えてみてください。

簡潔な答えは、LAG と LEAD のウィンドウ関数です。これらを使うと、各行の詳細をすべて維持したまま、現在の行から隣接する行の値を参照できます。このレッスンでは、隣接する行をどのように移動するのか、正確なメンタルモデルを身に付けます。

LAGとLEADの役割

LAG(col) は直前の行にある col の値を返します。LEAD(col) は直後の行にある値を返します。「直前」と「直後」は、OVER 句内の ORDER BY によって完全に定義されます。

LAG は後ろ向きに参照します。
LEAD は前向きに参照します。

どちらもオフセットウィンドウ関数です。行をまとめることはなく、隣接する行の値を現在の行に付加するだけです。

LAGの基本構文

ここでは典型的な形を見てみましょう。month と revenue を持つ sales テーブルがあり、各行に前月の売上も表示したいとします。

OVER (ORDER BY month) は、「直前」をどのように定義するかをエンジンに伝えます。最初の行には前の行がないため、そこでは prev_revenue が NULL になります。

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

結果を読み取る

データが2024-01 = 100、2024-02 = 130、2024-03 = 120の場合、クエリは次を返します。

1月：revenue 100、prev_revenue NULL
2月：revenue 130、prev_revenue 100
3月：revenue 120、prev_revenue 130

各行は、並べ替えられた集合で直前にある行の値を取得しています。自己結合もサブクエリもなく、行が失われることもありません。

LEADは前方を参照する

LEAD は LAG と対になる関数です。次に何が来るかを行から確認したい場合に使います。たとえば、注文間の期間を計算するために次回の購入日を取得する場合です。

並べ替えられた集合の最後の行には後続の行がないため、その LEAD の結果は NULL になります。

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

オフセット引数

どちらの関数も省略可能な2番目の引数を受け取ります。これは何行分移動するかを指定します。LAG(col, 2) は2行前に戻り、LEAD(col, 3) は3行先に進みます。

面接官はこれを使って、「2か月前の売上」や「3行下の値」などを求めさせます。デフォルトのオフセットは 1 です。

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

デフォルト値の引数

3番目の引数を指定すると、隣接する行がなくて NULL になる場合に、代わりの値を返せます。シグネチャは LAG(col, offset, default) です。

これは、後続の計算で NULL を扱えない場合に便利です。たとえば、前の値がない場合を0として扱えば、差分を計算できます。

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BYでウィンドウをリセットする

実際のデータにグローバルな系列が1つだけということは、ほとんどありません。通常は顧客、商品、地域ごとに比較します。PARTITION BY を指定すると、各パーティションの先頭でLAG/LEADの計算が再開されます。

つまり、各パーティションの最初の行は LAG から NULL を受け取り、異なる顧客のデータとの境界を越えて値が引き継がれることはありません。

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

実例：注文間の日数

よくある処理の一つに、顧客の連続する注文間の間隔を測ることがあります。LAGで以前の注文日を取得してから、差し引きます。

各顧客の最初の注文では、差し引く対象となる以前の日付がないためNULLになります。このような顧客ごとの比較こそ、面接官がウィンドウ関数で解決することを期待する処理です。

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

自己結合ではなぜいけないのか

ウィンドウ関数が登場する前の解決方法は、相関サブクエリを使った自己結合でした。つまり、「この行より日付が小さい行のうち、最大の日付を持つ行」でテーブル自身を結合します。動作はしますが、記述が冗長で、同順位の値があるとエラーの原因になりやすく、処理も遅くなることがあります。

LAG/LEADなら意図を1行で表現できます。
順序付けされた1回の走査で評価されます。
同順位の値は指定したORDER BYによって決定的に処理されます。

「自己結合ではなくLAGを使います」と答えられると、実務的な理解度を示せます。

よくある落とし穴：ORDER BYの欠落

OVER句にORDER BYがない場合、「前の行」は定義されません。エラーにするデータベースもあれば、予測できない結果を返すデータベースもあります。ウィンドウには必ず順序を指定してください。

また、OVER内の順序は、クエリの外側にあるORDER BYとは独立していることにも注意してください。どの行を隣接行とするかを決めるのはウィンドウであり、外側の句が決めるのは表示順だけです。

確認問題

オフセットウィンドウ関数の理解度を確認しましょう。

対話的なレッスンで答える

まとめ

これで、次のオフセットウィンドウ関数を理解できました。

LAG(col)はORDER BYで定義された前の行を読み取り、LEAD(col)は次の行を読み取ります。
オプション引数としてLAG(col, offset, default)を指定できます。
PARTITION BYを使うとグループごとにナビゲーションがリセットされるため、境界の行はNULLになります。
隣接する行を比較する際の扱いにくい自己結合を置き換えられます。

次は、アナリスト面接で定番の質問である期間ごとの変化に適用します。

無料で開始

AI チューターと学ぶ SQL — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース: 30
レッスン: 120

無料で学習を始めるコース全体を表示

よくある質問

「LAGとLEADで隣接行を参照する」レッスンは無料ですか？

はい。「LAGとLEADで隣接行を参照する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「LAGとLEADで隣接行を参照する」で何を学びますか？

SELF JOINを使わずに前後の行の値を取得します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン1/4です。

「LAGとLEADで隣接行を参照する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Interview Prepに戻る