Перейти к основному содержимому
Модуль 7 · Урок 4 из 428/57 в курсе~14 min
Уроки модуля (4/4)

Рекурсивные CTE: иерархии произвольной глубины

Самостоятельное ПРИСОЕДИНЕНИЕ (Модуль 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;

Как это работает на практике:

  1. PostgreSQL выполняет базовый термин один раз.
  2. Он выполняет рекурсивный термин, используя строки, только что созданные как «вход» (не весь CTE: только приращение).
  3. Шаг 2 повторяется до тех пор, пока рекурсивный термин не создаст 0 новых строк.
  4. Конечным результатом является 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
Попыток: 0Загрузка…

Для каждой категории укажите идентификатор, имя и root_name (имя корневой категории в верхней части дерева, к которому она принадлежит). Корни имеют root_name = name. Заказ по идентификатору.

Загрузка редактора…
Показать подсказку

Распространите root_name от родительского (t.root_name) к дочернему (c).

Решение доступно после 3 попыток