Modül 7 · Ders 4 ders 4Kurstaki 28/57~14 min

Modül dersleri (4/4)

Özyinelemeli CTE'ler: keyfi derinlikli hiyerarşiler

Self-JOIN (Modül 5), hiyerarşinin yalnızca bir düzeyini bulur: çocuk → ebeveyn. Ancak hiyerarşi 3, 5 veya n düzeyde derinse ne olur? ve kaç tane olduğunu önceden bilmiyor musun? özyinelemeli CTE'lere ihtiyacınız var: a Sonuçları yinelemeli olarak "genişletmeyi" ifade eden CTE.

ecommerce veri kümesinde, categories tablosunun 3 kökü vardır (Elettronica, Libri, Casa) ve her biri 2 çocuk — 2 seviyeli bir ağaç. Bu bir basit bir durum ama modeli öğrenmek için yeterli.

Sözdizimi

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;

Uygulamada nasıl çalışır:

PostgreSQL temel terimi bir kez çalıştırır.
özyinelemeli terimi şu şekilde üretilen satırları kullanarak çalıştırır: "giriş" (CTE'nin tamamı değil: yalnızca artış).
Özyinelemeli terim 0 yeni satır üretene kadar 2. adımı tekrarlar.
Nihai sonuç, tüm adımların UNION ALL'sidir.

Örnek 1: derinliği ve yolu olan ağaç

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;

İlk yineleme 3 satır üretir (seviye 1'deki kökler). İkinci yineleme 6 çocuğu bulur (seviye 2). Üçüncü yineleme bulur hiçbir şey → yapılmadı.

Örnek 2: Verilen bir düğümün kökünü bulun

"Yukarıdan aşağıya → aşağıdan yukarıya" bir değişken: kökün kimliğini aşağıya doğru yayıyoruz ağaç.

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;

Deneyin

Egzersiz#sql.m7.l4.e1

Denemeler: 0Yükleniyor…

Kategori hiyerarşisini düzleştirmek için 'ağaç' adlı özyinelemeli bir CTE kullanın. Kimliği, adı, derinliği (kökler için 1, çocuklar için 2 vb.) ve yolu (ör. 'Elettronica > Audio') gösterin. Yola göre sipariş verin.

Düzenleyici yükleniyor…

İpucu göster

Özyinelemeli terimdeki JOIN, c.parent_id'yi ağaçta (t.id) zaten mevcut olan satıra bağlar.

3 denemeden sonra çözüm mevcut

Alıştırmayı gözden geçirin

Egzersiz#sql.m7.l4.e2