モジュール 7 · レッスン 4 / 4コース内の 28/57~14 min

モジュールのレッスン (4/4)

再帰的 CTE: 任意の深さの階層

自己結合 (モジュール 5) は、階層の1 つのレベルのみ を見つけます: child → 親。しかし、階層が 3、5、または n レベルの深さである場合はどうなるでしょうか。事前に何人かわかりませんか？ 再帰的 CTE が必要です: それ自体を参照して結果を反復的に「拡張」する CTE。

ecommerce データセットの categories テーブルには 3 つのルートがあります (Elettronica、Libri、Casa) とそれぞれ 2 人の子供 - 2 レベルのツリー。それは単純なケースですが、パターンを学ぶには十分です。

構文

SQL

WITH RECURSIVE nome (col1, col2, …) AS (
  -- (1) Termine BASE: le righe di partenza
  SELECT …
  FROM   …
  WHERE  …

  UNION ALL

  -- (2) Termine RICORSIVO: si appoggia a "nome" stesso per espandere
  SELECT …
  FROM   tabella  t
  JOIN   nome    n  ON …
)
SELECT * FROM nome;

実際にどのように機能するか:

PostgreSQL は 基本用語を 1 回実行します。
生成されたばかりの行を使用して 再帰項 を実行します。「入力」(CTE 全体ではなく、増分のみ)。
再帰項によって新しい行が 0 個生成されるまで、ステップ 2 を繰り返します。
最終結果は、すべてのステップの UNION ALL です。

例 1: 深さとパスを含むツリー

SQL

WITH RECURSIVE tree AS (
  -- base: le radici (parent_id IS NULL)
  SELECT id,
         name,
         parent_id,
         1                 AS depth,
         name              AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- ricorsivo: per ogni categoria che ha come parent una riga già in tree
  SELECT c.id,
         c.name,
         c.parent_id,
         t.depth + 1,
         t.path || ' > ' || c.name
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, depth, path
FROM   tree
ORDER BY path;

最初の反復では 3 行 (レベル 1 のルート) が生成されます。 2番目反復により 6 つの子 (レベル 2) が見つかります。 3 回目の反復で次のことがわかります。何もない → 完了。

例 2: ノードが与えられた場合、そのルートを見つける

「トップダウン → ボトムアップ」の変形: ルートの ID を下に伝播します。木。

SQL

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

  UNION ALL

  SELECT c.id,
         c.name,
         c.parent_id,
         t.root_id,    -- propago lo stesso id_radice
         t.root_name   -- e nome
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, root_name
FROM   tree
ORDER BY id;

試してみてください

運動#sql.m7.l4.e1

試行回数: 0読み込み中…

「tree」という名前の再帰的 CTE を使用して、カテゴリ階層を平坦化します。 ID、名前、深さ (1 はルート、2 は子など)、およびパス (例: 「Elettronica > Audio」) を表示します。パスごとに注文します。

エディターを読み込み中…

ヒントを表示

再帰項の JOIN は、c.parent_id をツリー内にすでに存在する行 (t.id) にリンクします。

3 回の試行後に解決策が利用可能になります

復習演習

運動#sql.m7.l4.e2