SQL Interview Prep · レッスン

1つのテーブル内で行を比較する

Q: 「1つのテーブル内で行を比較する」レッスンは無料ですか？

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

Q: 「1つのテーブル内で行を比較する」で何を学びますか？

ペア、重複、隣接レコードを見つけるSELF JOINパターンを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

Q: 「1つのテーブル内で行を比較する」レッスンにはどのくらい時間がかかりますか？

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

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

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

ペア、重複、隣接レコードを見つけるSELF JOINパターンを学びます。

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

行と行の比較に使う自己結合

階層構造以外では、自己結合のもう1つの主要な用途は、同じテーブルの行同士を比較することです。親子関係ではなく、任意の行を組み合わせて、重複、類似、隣接するレコードなどを見つけます。

パターンは同じです。テーブルに2つのエイリアスを付け、組み合わせたい2つの行の関係を表す ON 条件を記述します。

同じグループ内のペアを見つける

定番の質問：同じ部署で働く従業員のすべてのペアを見つけてください。部署が等しい条件でテーブルを自分自身に結合しますが、2つの行は別々のままにします。

単純な結合では、各従業員自身とのペアも作られ、各ペアが2回ずつ生成されます。次にこれを修正します。

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department;

自己ペアと鏡像の重複を除去する

同じグループのペアリングには2つの問題があります。行自身とマッチする（Alice と Alice）ことと、各ペアが2回（Alice-Bob と Bob-Alice）現れることです。

この2つを単一の不等号条件で解決できます。a.id < b.id とします。これにより2つの行が異なることが保証され、各ペアの順序も一方だけが残ります。

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.id < b.id;

なぜ a.id < b.id であり、a.id <> b.id ではないのか

a.id <> b.id を使うと自己ペアは除外できますが、両方の順序が返されるため、結果が2倍になります。a.id < b.id を使えば、自己ペアを除外し、鏡像関係の重複も一度に排除できます。

面接官は特に < と <> の選択に注目します。これは自己結合の組合せ論を理解していることを示します。

-- <>  keeps Alice-Bob AND Bob-Alice (duplicated)
-- <   keeps only Alice-Bob (correct unique pairs)

重複する行を見つける

キー列の値が一致するレコードを見つけるには、その列で自己結合し、主キーが異なることを条件にします。

ここでは、同じメールアドレスを持つ顧客を抽出します。a.id < b.id により、各重複ペアが1回だけ残ります。多くの場合、GROUP BY ... HAVING COUNT(*) > 1 のほうが簡潔ですが、自己結合を使うと問題のあるペアを実際に横並びで確認できます。

SELECT a.id, b.id, a.email
FROM customers a
JOIN customers b
  ON a.email = b.email
 AND a.id < b.id;

隣接するレコードを比較する

アナリストによくある作業として、各行を次の行と比較します。たとえば、各日の売上を前日と比較する場合です。自己結合で連続する行を組み合わせられます。

ここでは各日をちょうど1日前の行に結合して、差分を計算します。これは系列に欠損がない場合に機能します。

SELECT t.day, t.amount,
       t.amount - y.amount AS change_vs_prev
FROM daily_sales t
JOIN daily_sales y
  ON y.day = t.day - INTERVAL '1 day';

隣接行の自己結合で生じる欠損の問題

前のクエリは日付が欠けていると機能しません。ちょうど1日前の行が存在しないため、その行が（内部結合では）結果から抜け落ちるか、NULLを処理しなければなりません。

そのため、面接官は「前の行と比較する」場合に、ウィンドウ関数（LAG など）を使うよう促すことがよくあります。ウィンドウ関数は値の一致ではなく順序上の位置を使うため、欠損にも自然に対応できます。

-- LAG handles gaps; the self join assumed contiguous days
SELECT day, amount,
       amount - LAG(amount) OVER (ORDER BY day) AS change_vs_prev
FROM daily_sales;

自己結合とウィンドウ関数

トレードオフを理解しましょう。

自己結合は、値の関係（同じ部署、前の日付など）で行を比較します。柔軟ですが、行が増幅したり、欠損を適切に扱えなかったりすることがあります。
ウィンドウ関数は、順序付けされたパーティション内の順番で行を比較します。前後の行を扱う処理には、よりすっきりした方法です。

「隣接する行と比較する」場合は、LAG/LEADを優先してください。「条件に一致するすべてのペアを見つける」場合は、自己結合が自然な方法です。

同僚を上回る行を見つける

別のパターンとして、同じ部署の少なくとも1人の同僚より多く稼ぐ従業員を見つける方法があります。自己結合を使うと、これを直接表現できます。

各従業員を、同じ部署にいて給与がより低い他の従業員と結合し、結果に現れた従業員を重複なしで残します。英語の文章をほぼそのまま読んでいるようなクエリになります。

SELECT DISTINCT a.name, a.department, a.salary
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.salary > b.salary;

ファンアウトに注意

一意でない列で自己結合すると、行数が増加します。100人いる部署内で組み合わせを作ると、フィルタリングする前に約100 x 100個の候補ペアが生成されます。

重複を排除する述語（a.id < b.id）を必ず含め、すべてのペアではなく参加する行だけが必要な場合は、DISTINCTまたはグループ化も追加してください。面接では、この行数の増幅に対する意識にも触れましょう。

比較に使うツールを選ぶ

テーブル内比較の判断ガイドです。

一致するすべてのペア（重複、同じグループ内の組み合わせ）: a.id < b.idを指定した自己結合。
順序内の前後の行: ウィンドウ関数（LAG/LEAD）。
各行をグループ集計値と比較: 相関サブクエリまたはウィンドウ集計。

理解度チェック

同じカテゴリに属する商品の重複しないすべてのペアを取得したいとします。ただし、商品自身とのペアはなく、ペアの順序が重複してはいけません。

まとめ：1つのテーブル内で行を比較する

重要なポイントです。

テーブル自身を自己結合すると、自身の行同士をペアにして、重複の検出や同じグループ内の組み合わせを作れます。
a.id < b.idを使うと、自己ペアと順序を反転させた重複ペアを1つの述語で除外できます。
自己結合による隣接行の比較は、欠損があると機能しません。前後の行を扱う処理にはLAG/LEADを優先してください。
一意でない列を結合するときは、必ずファンアウトを考慮してください。

よくある質問