Модуль 7 · Урок 4 із 428/57 у курсі~14 min

Модульні уроки (4/4)

Рекурсивні CTE: ієрархії довільної глибини

Self-JOIN (модуль 5) знаходить тільки один рівень ієрархії: дитина → батькові. Але що станеться, якщо ієрархія має 3, 5 або n рівнів, а ти наперед не знаєш скільки? Вам потрібні рекурсивні CTE: a 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 виконує базовий термін один раз.
Він виконує рекурсивний термін, використовуючи щойно створені рядки "вхід" (не весь КТР: лише приріст).
Він повторює крок 2, доки рекурсивний термін не створить 0 нових рядків.
Кінцевим результатом є 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). другий ітерація знаходить 6 дітей (рівень 2). Третя ітерація знаходить нічого → зроблено.

Приклад 2: дано вузол, знайти його корінь

Варіант «зверху вниз → знизу вгору»: ми поширюємо ідентифікатор кореня вниз дерево.

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Завантаження…

Використовуйте рекурсивний CTE під назвою «дерево», щоб вирівняти ієрархію категорій. Показати ідентифікатор, назву, глибину (1 для коренів, 2 для дітей тощо) і шлях (наприклад, «Elettronica > Audio»). Порядок за шляхом.

Завантаження редактора…

Показати підказку

JOIN у рекурсивному терміні пов’язує c.parent_id з рядком, який уже є в дереві (t.id).

Рішення доступне після 3 спроб

Повторна вправа

вправи#sql.m7.l4.e2