SQL Interview Prep · レッスン

行番号の差分トリック

Q: 「行番号の差分トリック」で何を学びますか？

ROW_NUMBERを系列から引いて、連続する値を島としてグループ化します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

ROW_NUMBERを系列から引いて、連続する値を島としてグループ化します。

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

最も洗練されたアイランドキー

行番号の差分テクニックは、連続する整数または日付のアイランドに対して、面接官が最も見たい手法です。LAG も累積和も必要なく、一回の引き算だけでグループキーを作成できます。

考え方は単純です。値そのものから ROW_NUMBER を引きます。連続する値の並びでは、値と行番号がどちらも一歩ごとにちょうど 1 ずつ増えるため、差分が並び全体で一定になります。その一定の値がアイランドキーです。

差分が一定に保たれる理由

連続する並びの中の、隣り合う二つの行を考えてみましょう。一方から次の行へ進むと、値は 1 増え、行番号も 1 増えます。両者を引くと +1 同士が相殺されるため、value - row_number は変化しません。

しかし、ギャップが生じた瞬間、値は 1 より大きく増える一方で、行番号は 1 しか増えません。差分は新しい一定値に変わります。この変化が、あるアイランドと次のアイランドを正確に分けます。

データで確認する

ログインした日 1、2、3、7、8、10 を思い出してください。行番号と差分を横に並べると、次のようになります。

day 1、rn 1、diff 0
day 2、rn 2、diff 0
day 3、rn 3、diff 0
day 7、rn 4、diff 3
day 8、rn 5、diff 3
day 10、rn 6、diff 4

差分（0、0、0、3、3、4）によって、行は三つのアイランドに完全に分割されます。同じ差分なら同じアイランドです。

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

アイランドにまとめる

差分をグループキーにすれば、最後のクエリは標準的な集約になります。差分を CTE で囲み、それを GROUP BY します。

これにより、先ほどと同じ三つのアイランドが得られますが、SQL は LAG と累積和を使う方法より短く明快です。整数または等間隔の系列では、まずこの方法を使うとよいでしょう。

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

落とし穴：値は1ずつ増える必要があります

単純な差分のテクニックでは、シーケンスが1ステップごとにちょうど1ずつ増加することを前提とします。これは密な整数列や連続するカレンダー日付では成り立ちますが、値が別の一定量ずつ増える場合や重複がある場合には機能しません。

2,4,6,8のような偶数の値は、値から行番号を引く方法ではギャップのように見えます。
重複値があると、値が増えないのに行番号だけが増え続けるため、対応関係がずれます。

この制限と、その修正方法を理解することが、暗記した小技と本当の理解を分けます。

固定ステップのシーケンスを修正する

値が1ではなく既知の定数 k ずつ増える場合は、まず正規化します。値を k で割る（整数の場合は value / k を使う）ことで各ステップを再び1にし、その後で行番号を引きます。

たとえば、2ずつ増える偶数列には day_no / 2 - ROW_NUMBER() を使います。これで正規化した値は連続する各項目で1ずつ増えるため、一定差分の性質が復元されます。

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

日付に適用する

日付は、実務で最も一般的な形です。カレンダー日付は行番号から直接引けないため、まず日数に変換します。Postgresでは、固定の基準日を引いて日数を表す整数を取得し、同じテクニックを適用します。

連続するカレンダー日付の差は1なので、日数と行番号の差はアイランド内で再び一定になります。

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

SQL方言ごとの日付差分

日付を整数に変換する方法はデータベースエンジンによって異なるため、面接官は複数のSQL方言への対応力を評価します。

Postgres：日付リテラルを引きます。login_date - DATE '2000-01-01' は整数になります。
MySQL：DATEDIFF(login_date, '2000-01-01') を使います。
SQL Server：DATEDIFF(day, '2000-01-01', login_date) を使います。

一部のエンジンでは、さらに洗練された方法として、インターバル演算を使って日付から ROW_NUMBER 日分を直接引き、得られた基準日で GROUP BY することもできます。

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

グループごとのパーティションを追加する

ユーザーごとのアイランドを求めるには、グループ列で行番号をパーティション分割します。重要なのは、2人の異なるユーザーが偶然同じ差分値になる可能性があるため、グループキーにパーティション列も含める必要があることです。

つまり、user_id と計算した差分の両方で GROUP BY します。最後の GROUP BY に user_id を入れ忘れるのは、面接官が好んで見抜く微妙なバグです。

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

小技と LAG：どちらを使うか

これで、確かな2つのテクニックを使えるようになりました。目的に応じて意識的に選んでください。

行番号の差分：一定のステップで増える値（密な整数列や連続する日付）の連続区間には、最も短く明快です。隣接を「一定量の差」とみなせる場合の第一候補です。
LAG と累積和：隣接が一定の数値ステップではない場合に、より柔軟です。たとえば「前の行と同じステータス」や、不規則な独自ルールに向いています。

面接では、どちらを選んだかとその理由を説明してください。構文よりも、その判断の理由のほうが評価されます。

重複を考慮して堅牢に処理する

値が繰り返される可能性があり、それでも連続区間ごとに1つのアイランドを求めたい場合は、まず DISTINCT またはグループ化で重複を除去し、行番号と値が1対1に対応するようにします。別の方法として、ROW_NUMBER の代わりに DENSE_RANK を使い、同じ値に同じ順位を割り当てることもできます。

重複が発生する可能性があるか、必ず面接官に確認してください。適切な対策は、連続区間内の重複を区間の延長として扱うのか、無視するのかによって変わります。

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

確認問題

なぜこのテクニックが機能するのか理解していることを確認してください。

まとめ：差分テクニック

これで、最も明快なアイランドキーを使えるようになりました。

キーの式：value - ROW_NUMBER() OVER (ORDER BY value) は、連続区間ごとに一定です。
差分で GROUP BY してまとめると、開始値、終了値、長さを取得できます。
固定ステップのシーケンスでは、最初に正規化（ステップで割る）します。
日付では、使用するSQL方言の差分関数によって整数の日数に変換します。
グループごとに求める場合は、行番号を PARTITION BY し、最後の GROUP BY にグループ列を含めます。
重複には DISTINCT または DENSE_RANK で対処します。

次はアイランドから空白部分へ焦点を移し、ギャップを見つけます。

よくある質問

「行番号の差分トリック」レッスンは無料ですか？

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

「行番号の差分トリック」で何を学びますか？

ROW_NUMBERを系列から引いて、連続する値を島としてグループ化します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「行番号の差分トリック」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Interview Prepに戻る