0Pricing
SQL Academy · Урок

Обход дерева категорий

Полностью раскрывайте деревья «родитель — потомок»

«Обход дерева категорий» — бесплатный урок SQL Academy на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое дерево категорий

Во многих реальных наборах данных есть связь между родителем и потомком. В каталоге товаров могут быть категории Электроника → Телефоны → Смартфоны. Каждый узел имеет родителя, образуя древовидную структуру.

В SQL это обычно хранится в таблице, ссылающейся на саму себя: каждая строка содержит id и parent_id, указывающий на другую строку той же таблицы.

CREATE TABLE categories (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

Пример данных категорий

Заполним небольшое дерево категорий. У корневого узла значение parent_id = NULL, потому что у него нет родителя. Каждый другой узел указывает на своего родителя с помощью ненулевого значения parent_id.

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics',   NULL),
  (2, 'Phones',         1),
  (3, 'Laptops',        1),
  (4, 'Smartphones',    2),
  (5, 'Feature Phones', 2),
  (6, 'Gaming Laptops', 3),
  (7, 'Ultrabooks',     3);

Проблема простых запросов

Обычный SELECT может получить только один уровень за раз. Чтобы пройти три уровня в глубину, потребовались бы три отдельных запроса или три самосоединения, что становится неуправляемым по мере роста дерева.

WITH RECURSIVE решает эту проблему, позволяя запросу обращаться к собственному результату и проходить уровни один за другим, пока не перестанут находиться новые строки.

-- This only shows direct children of Electronics (level 1)
SELECT id, name
FROM   categories
WHERE  parent_id = 1;

Анатомия WITH RECURSIVE

Рекурсивный CTE состоит из двух частей, разделённых с помощью UNION ALL:

1. Якорный элемент — обычный SELECT, задающий начальные строки.

2. Рекурсивный элемент — SELECT, который присоединяет CTE к самому себе и на каждой итерации формирует следующий уровень.

Механизм повторяет рекурсивный элемент, пока тот не вернёт ноль строк.

WITH RECURSIVE cte AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive: join cte to base table
  SELECT ... FROM base_table JOIN cte ON ...
)
SELECT * FROM cte;

Обход всего дерева от корня

Начните с корня (где parent_id IS NULL) и спускайтесь до каждого потомка. Рекурсивный элемент присоединяет каждую накопленную строку обратно к categories по связи между родителем и потомком.

WITH RECURSIVE category_tree AS (
  -- Anchor: root nodes
  SELECT id, name, parent_id, 1 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories      c
  JOIN   category_tree   ct ON ct.id = c.parent_id
)
SELECT id, name, depth
FROM   category_tree
ORDER  BY depth, id;

Отслеживание пути

Полезно записывать полный путь от корня к каждому узлу. По мере углубления рекурсии можно формировать строку path, объединяя имена предков.

Так легко отображать цепочки навигации, например Электроника / Телефоны / Смартфоны.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id,
         name AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.path || ' / ' || c.name
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, path
FROM   category_tree
ORDER  BY path;

Начало с определённого узла

Вам не обязательно начинать с корня. Изменив предложение WHERE якорного элемента, можно обойти поддерево любого узла. Здесь мы начинаем с Телефонов (идентификатор = 2) и получаем всех их потомков.

WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  id = 2          -- start at Phones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree    s ON s.id = c.parent_id
)
SELECT id, name, depth
FROM   subtree
ORDER  BY depth, id;

Движение вверх: поиск всех предков

Дерево также можно обходить в обратном направлении — от листа к корню. Просто измените JOIN так, чтобы двигаться вверх по parent_id, а не вниз. Это полезно, когда нужно получить полную цепочку навигации для известного листового узла.

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM   categories
  WHERE  id = 4          -- start at Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM   categories c
  JOIN   ancestors  a ON a.parent_id = c.id
)
SELECT id, name
FROM   ancestors
ORDER  BY id;

Добавление отображения с отступами

Распространённый приём в интерфейсе пользователя — визуально выделять дочерние узлы отступом. Можно использовать REPEAT (или LPAD) вместе со столбцом depth, добавляя перед каждым именем пробелы и получая текстовое представление дерева.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('    ', depth) || name AS indented_name,
  depth
FROM   category_tree
ORDER  BY path;

Защита от бесконечных циклов

Если в данных есть цикл (A — родитель B, а B — родитель A), рекурсия будет выполняться бесконечно и приведёт к сбою. Защититься от этого можно, отслеживая посещённые идентификаторы в массиве и останавливаясь, если текущий идентификатор уже присутствует в нём.

WITH RECURSIVE safe_tree AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         st.visited || c.id
  FROM   categories c
  JOIN   safe_tree  st ON st.id = c.parent_id
  WHERE  c.id <> ALL(st.visited)   -- stop if already seen
)
SELECT id, name FROM safe_tree;

Подсчёт потомков для каждого узла

Получив полное дерево, можно агрегировать его данные. Здесь мы подсчитываем количество потомков каждого узла, группируя дочерние строки по списку предков. Это полезно для отображения количества элементов рядом с названиями категорий в меню навигации.

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

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.root_id
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  root_id,
  COUNT(*) - 1 AS descendant_count
FROM   category_tree
GROUP  BY root_id
ORDER  BY root_id;

Быстрая проверка

Проверьте, насколько хорошо Вы понимаете запросы для рекурсивных деревьев категорий.

Итоги урока

В этом уроке Вы научились обходить таблицу категорий со ссылками на саму себя с помощью WITH RECURSIVE.

Главные выводы:

- Якорный элемент выбирает начальные узлы (обычно корень).

- Рекурсивный элемент присоединяет CTE к исходной таблице, чтобы найти следующий уровень.

- Добавляйте столбец depth, чтобы отслеживать глубину каждого узла.

- Формируйте строку path для создания цепочек навигации.

- Двигайтесь вверх, следуя по parent_id в обратном направлении, чтобы найти всех предков.

- Используйте массив visited, чтобы защититься от циклов в некорректных данных.

Часто задаваемые вопросы

Урок «Обход дерева категорий» бесплатный?

Да — полный текст урока «Обход дерева категорий» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Обход дерева категорий»?

Полностью раскрывайте деревья «родитель — потомок» Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Обход дерева категорий»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Как работают рекурсивные CTE
  2. Обход дерева категорий
  3. Генерация рядов и последовательностей
  4. Как избежать бесконечных циклов
← Назад к SQL Academy