組織図をたどる
従業員と上司の階層を任意の深さまでたどります。
「組織図をたどる」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
組織図に関する質問
「id、name、manager_idを持つemployeesテーブルがあるとします。指定したmanagerの配下にいる全員を、任意の深さまで一覧にしてください。」これは、再帰CTEに関する面接で非常によく出る質問の1つです。
このテーブルは自己参照になっています。manager_idが、別の行のidを参照しているためです。このレッスンでは、下方向（部下）と上方向（指揮命令系統）の両方にたどります。
サンプルテーブル
次のようなデータを想像してください。CEOのmanagerはNULLです。それ以外の全員は、上位者へ連なる階層に属しています。
- 1 Ada（managerはNULL）
- 2 Ben（managerは1）
- 3 Cleo（managerは1）
- 4 Dan（managerは2）
- 5 Eve（managerは4）
したがって、階層はAda → Ben → Dan → Eveです。実際にたどる際は、この関係を覚えておいてください。
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT REFERENCES employees(id)
);managerから下方向にたどる
選択したmanagerの配下にいるすべての部下を一覧にするには、アンカーでそのmanager（または直属の部下）を選択し、再帰メンバーでmanager_idを下方向にたどります。
ここではBen（id 2）から開始し、その配下にいる全員を集めます。
WITH RECURSIVE subtree AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees WHERE id = 2
UNION ALL
SELECT e.id, e.name, e.manager_id, s.depth + 1
FROM employees e
JOIN subtree s ON e.manager_id = s.id
)
SELECT name, depth FROM subtree ORDER BY depth;出力を読み取る
上のクエリは、深さ1のBen、深さ2のDan、深さ3のEveを返します。アンカーでBenを起点とし、1回目の反復でDan（managerがBen）を見つけ、2回目の反復でEve（managerがDan）を見つけ、3回目の反復では誰も見つからないため、再帰が停止しました。
面接官から「EveはBenの何階層下にいますか」と聞かれた場合、depth列から直接答えられます。3から1を引いた2階層です。
CEOへ上方向にたどる
逆向きの質問も同じくらいよく出ます。「EveからCEOまでの指揮命令系統をすべて表示してください」という質問です。JOINの方向を反転し、再帰メンバーで現在の行のmanager_idをたどって親へ移動します。
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 1 AS lvl
FROM employees WHERE id = 5
UNION ALL
SELECT e.id, e.name, e.manager_id, c.lvl + 1
FROM employees e
JOIN chain c ON e.id = c.manager_id
)
SELECT name, lvl FROM chain ORDER BY lvl;下方向と上方向：JOINを反転する
下方向にたどる場合と上方向にたどる場合の構造上の違いは、JOIN条件だけです。
- 下方向（部下を探す）：
e.manager_id = cte.id— すでに取得した行をmanagerとして持つ従業員を照合します。 - 上方向（managerを探す）：
e.id = cte.manager_id— 現在の行がmanagerとして持つIDに一致する従業員を照合します。
この反転を明確に説明できると、面接官に好印象を与えられます。
インデント付きツリーを作る
洗練された回答では、depthを使ってスペースを繰り返し、出力をインデント付きのツリーとして整形します。これにより、階層の結果を計算するだけでなく、見やすく提示できることも示せます。
WITH RECURSIVE org AS (
SELECT id, name, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT REPEAT(' ', depth - 1) || name AS tree
FROM org
ORDER BY depth;パスを蓄積する
CEOから各従業員までの経路全体を表示するには、path文字列を持ち回ります。これは前のレッスンで扱った手法を、組織図に適用したものです。
WITH RECURSIVE org AS (
SELECT id, name, CAST(name AS VARCHAR(500)) AS path
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, o.path || ' / ' || e.name
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, path FROM org ORDER BY path;managerごとの部下数を数える
よくある追加質問に、「各managerに直属または間接的に報告する人は何人いますか」というものがあります。managerごとに再帰でサブツリーを取得し、その後で集計します。よく使われるパターンは、ルートごとに1回再帰処理を実行し、起点となるmanagerでGROUP BYする方法です。
ここでは、ツリー全体をたどり、ルートより下にある行を数えることで、CEOであるAdaの配下にいる間接的な部下をすべて数えます。
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT COUNT(*) - 1 AS total_reports FROM org;よくある間違い
面接官が仕掛ける、次の落とし穴に注意してください。
- JOINの方向を間違える — 上方向に進むつもりで
e.manager_id = cte.idを使うと、誤った集合が返されます。 - アンカーのフィルターを忘れる —
WHERE id = Xを省略すると、すべての行が起点となり、森林全体が返されます。 - 深さを1つずらす — 起点を深さ0とするか1とするかを決め、一貫して扱います。
自己結合だけではいけない理由
自己結合で取得できるのは固定された階層数です。直属の部下なら1回、孫世代の部下なら2回というように、階層ごとにJOINします。しかし、あらかじめ深さを知っておき、階層ごとにJOINを記述しなければなりません。
再帰CTEなら、任意で未知の深さを1つのクエリで処理できます。面接官が「階層数は任意です」と言った場合、通常の自己結合では対応できず、再帰が必要だという合図です。
クイックチェック
たどる方向を反転できることを確認しましょう。
まとめ
組織図の走査は、自己参照テーブルに適用する再帰の基本形です:
- 下方向: マネージャーを起点にし、
e.manager_id = cte.idで結合します。 - 上方向: 社員を起点にし、
e.id = cte.manager_idで結合します。 - インデントには
depthを、完全な階層チェーンにはpathを引き継ぎます。 - 再帰なら、自己結合では対応できない未知の深さも処理できます。
次は、再帰を使って数値系列と日付系列を生成します。
よくある質問
「組織図をたどる」レッスンは無料ですか？
はい。「組織図をたどる」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「組織図をたどる」で何を学びますか？
従業員と上司の階層を任意の深さまでたどります。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。
「組織図をたどる」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。