SQL Academy · レッスン

カテゴリーツリーをたどる

Q: 「カテゴリーツリーをたどる」で何を学びますか？

親子関係のツリーを完全に展開します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

親子関係のツリーを完全に展開します。

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

カテゴリツリーとは

現実の多くのデータセットには、親子関係があります。商品カタログには、電子機器 → 携帯電話 → スマートフォンのようなカテゴリがあるでしょう。各ノードが親を持つことで、ツリー構造が形成されます。

SQLでは通常、自己参照テーブルとして保存します。各行にidと、同じテーブル内の別の行を指すparent_idがあります。

CREATE TABLE categories (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

カテゴリデータの例

小さなカテゴリツリーを登録してみましょう。ルートノードには親がないため、parent_id = NULLになります。それ以外のすべてのノードは、NULLではないparent_idによって親を指します。

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics',   NULL),
  (2, 'Phones',         1),
  (3, 'Laptops',        1),
  (4, 'Smartphones',    2),
  (5, 'Feature Phones', 2),
  (6, 'Gaming Laptops', 3),
  (7, 'Ultrabooks',     3);

単純なクエリの問題点

通常のSELECTでは、一度に1つのレベルしか取得できません。3レベルの深さまで到達するには、3つの別々のクエリまたは3つの自己結合が必要になり、ツリーが大きくなるにつれて管理できなくなります。

WITH RECURSIVEを使うと、クエリが自身の出力を参照できるため、この問題を解決できます。新しい行が見つからなくなるまで、レベルごとにたどっていきます。

-- This only shows direct children of Electronics (level 1)
SELECT id, name
FROM   categories
WHERE  parent_id = 1;

WITH RECURSIVE の構成

再帰CTEは、UNION ALLで区切られた2つの部分で構成されます。

1. アンカー部 — 開始行を提供する通常のSELECTです。

2. 再帰部 — CTE自身を結合し、反復ごとに次の階層を生成するSELECTです。

エンジンは、再帰部が0行を返すまで再帰部を繰り返し実行します。

WITH RECURSIVE cte AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive: join cte to base table
  SELECT ... FROM base_table JOIN cte ON ...
)
SELECT * FROM cte;

ルートからツリー全体をたどる

ルート（parent_id IS NULLの行）から開始し、すべての子孫をたどります。再帰部では、蓄積された各行を親子関係に基づいてcategoriesへ再び結合します。

WITH RECURSIVE category_tree AS (
  -- Anchor: root nodes
  SELECT id, name, parent_id, 1 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories      c
  JOIN   category_tree   ct ON ct.id = c.parent_id
)
SELECT id, name, depth
FROM   category_tree
ORDER  BY depth, id;

パスを追跡する

ルートから各ノードまでの完全なパスを記録すると便利です。再帰を深く進めながら祖先の名前を連結することで、path文字列を作成できます。

これにより、電子機器 / 電話 / スマートフォンのようなパンくずリストを簡単に表示できます。

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id,
         name AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.path || ' / ' || c.name
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, path
FROM   category_tree
ORDER  BY path;

特定のノードから開始する

ルートから開始する必要はありません。アンカー部のWHERE句を変更すれば、任意のノードのサブツリーをたどれます。ここではPhones（id = 2）から開始し、そのすべての子孫を取得します。

WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  id = 2          -- start at Phones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree    s ON s.id = c.parent_id
)
SELECT id, name, depth
FROM   subtree
ORDER  BY depth, id;

上方向にたどる：すべての祖先を見つける

ツリーは逆方向に、つまり葉からルートに向かって上方向へたどることもできます。結合の向きを反転し、下方向ではなくparent_idを上方向にたどるだけです。既知の葉ノードについて完全なパンくずリストが必要な場合に便利です。

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM   categories
  WHERE  id = 4          -- start at Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM   categories c
  JOIN   ancestors  a ON a.parent_id = c.id
)
SELECT id, name
FROM   ancestors
ORDER  BY id;

インデント付きの表示を追加する

子ノードを視覚的にインデントするのは、よく使われるUIパターンです。REPEAT（またはLPAD）をdepth列と組み合わせ、各名前の前にスペースを付けることで、テキストベースのツリー表示を作成できます。

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('    ', depth) || name AS indented_name,
  depth
FROM   category_tree
ORDER  BY path;

無限ループを防ぐ

データに循環（AがBの親で、BがAの親）が含まれていると、再帰が永遠に続いてクラッシュします。訪問済みIDを配列で追跡し、現在のIDがすでに含まれている場合に停止することで、この問題を防げます。

WITH RECURSIVE safe_tree AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         st.visited || c.id
  FROM   categories c
  JOIN   safe_tree  st ON st.id = c.parent_id
  WHERE  c.id <> ALL(st.visited)   -- stop if already seen
)
SELECT id, name FROM safe_tree;

ノードごとの子孫数を数える

ツリー全体を取得できたら、集計できます。ここでは、子行を祖先の一覧に対してグループ化することで、各ノードの子孫数を数えます。ナビゲーションメニューでカテゴリ名の横に項目数を表示する場合などに便利です。

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, id AS root_id
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.root_id
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  root_id,
  COUNT(*) - 1 AS descendant_count
FROM   category_tree
GROUP  BY root_id
ORDER  BY root_id;

理解度チェック

カテゴリツリーに対する再帰クエリの理解度を確認しましょう。

レッスンのまとめ

このレッスンでは、WITH RECURSIVEを使用して自己参照するカテゴリテーブルをたどる方法を学びました。

重要なポイント：

- アンカー部は開始ノード（通常はルート）を選択します。

- 再帰部はCTEを基底テーブルに再び結合し、次の階層を見つけます。

- depth列を追加して、各ノードが何階層目にあるかを追跡します。

- path文字列を作成して、パンくずリストを生成します。

- parent_idを逆方向にたどって上方向に進み、すべての祖先を見つけます。

- visited配列を使用して、不完全なデータに含まれる循環から保護します。

よくある質問

「カテゴリーツリーをたどる」レッスンは無料ですか？

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

「カテゴリーツリーをたどる」で何を学びますか？

親子関係のツリーを完全に展開します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「カテゴリーツリーをたどる」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Academyに戻る