Modul 7 · Pelajaran 4 dari 428/57 dalam kursus~14 min

Pelajaran modul (4/4)

CTE rekursif: hierarki mendalam yang sewenang-wenang

Self-JOIN (Modul 5) menemukan hanya satu tingkat hierarki: anak → orang tua. Namun apa yang terjadi jika hierarkinya memiliki kedalaman 3, 5, atau n, dan kamu tidak tahu sebelumnya berapa jumlahnya? Anda memerlukan CTE rekursif: a CTE yang merujuk pada dirinya sendiri untuk "memperluas" hasil secara berulang.

Pada dataset ecommerce, tabel categories memiliki 3 akar (Elettronica, Libri, Casa) dan masing-masing 2 anak — pohon 2 tingkat. Itu sebuah kasus sederhana tetapi cukup untuk mempelajari polanya.

Sintaksnya

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;

Cara kerjanya dalam praktik:

PostgreSQL mengeksekusi istilah dasar satu kali.
Ia mengeksekusi istilah rekursif menggunakan baris yang baru saja dihasilkan sebagai "input" (bukan keseluruhan CTE: hanya kenaikannya).
Ulangi langkah 2 hingga suku rekursif menghasilkan 0 baris baru.
Hasil akhirnya adalah UNION ALL dari seluruh langkah.

Contoh 1: pohon dengan kedalaman dan jalur

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;

Iterasi pertama menghasilkan 3 baris (akar pada level 1). Yang kedua iterasi menemukan 6 anak (level 2). Iterasi ketiga ditemukan tidak ada → selesai.

Contoh 2: diberi sebuah simpul, temukan akarnya

Varian "top-down → bottom-up": kita menyebarkan id root ke bawah pohon.

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;

Cobalah

Latihan#sql.m7.l4.e1

Upaya: 0Memuat…

Gunakan CTE rekursif bernama 'pohon' untuk meratakan hierarki kategori. Tampilkan id, nama, kedalaman (1 untuk akar, 2 untuk anak, dll.) dan jalur (misalnya 'Elettronica > Audio'). Pesan berdasarkan jalur.

Memuat editor…

Tunjukkan petunjuk

GABUNG dalam istilah rekursif menghubungkan c.parent_id ke baris yang sudah ada di pohon (t.id).

Solusi tersedia setelah 3 upaya

Tinjau latihan

Latihan#sql.m7.l4.e2