0Pricing
SQL Interview Prep · Урок

Обход организационной структуры

Проходите иерархию сотрудник — руководитель на любую глубину.

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

Задача с организационной структурой

«Дана таблица employees со столбцами id, name и manager_id; перечислите всех, кто находится в подчинении у заданного руководителя, на любую глубину». Это один из самых распространённых вопросов о рекурсивном CTE на собеседовании.

Таблица ссылается сама на себя: manager_id указывает на id другой строки. В этом уроке Вы обойдёте её как вниз (по подчинённым), так и вверх (по цепочке руководителей).

Пример таблицы

Представьте такие данные. У CEO нет руководителя — в соответствующем поле стоит NULL. Все остальные подчиняются кому-то выше по цепочке.

  • 1 Ada (руководитель NULL)
  • 2 Ben (руководитель 1)
  • 3 Cleo (руководитель 1)
  • 4 Dan (руководитель 2)
  • 5 Eve (руководитель 4)

Итак, цепочка имеет вид: Ada → Ben → Dan → Eve. Помните об этом во время обхода.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees(id)
);

Обход вниз от руководителя

Чтобы перечислить всех подчинённых выбранного руководителя, якорная часть выбирает этого руководителя (или его непосредственных подчинённых), а рекурсивная часть следует по manager_id вниз.

Здесь мы начинаем с Ben (с идентификатором 2) и собираем всех, кто находится у него в подчинении.

WITH RECURSIVE subtree AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subtree s ON e.manager_id = s.id
)
SELECT name, depth FROM subtree ORDER BY depth;

Чтение результата

Приведённый выше запрос возвращает Ben на глубине 1, Dan — на глубине 2, а Eve — на глубине 3. Якорная часть добавила Ben; первая итерация нашла Dan (его руководитель — Ben); вторая итерация нашла Eve (её руководитель — Dan); третья итерация не нашла никого, поэтому рекурсия остановилась.

Если интервьюер спросит: «На сколько уровней ниже Ben находится Eve?», столбец depth сразу даёт ответ: 3 минус 1 — это 2 уровня.

Подъём по иерархии к CEO

Обратный вопрос встречается так же часто: «Покажите полную цепочку руководителей Eve до CEO». Измените направление соединения — теперь рекурсивная часть поднимается от manager_id текущей строки к родительской строке.

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl
    FROM employees WHERE id = 5
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM employees e
    JOIN chain c ON e.id = c.manager_id
)
SELECT name, lvl FROM chain ORDER BY lvl;

Вниз и вверх: меняется соединение

Единственное структурное различие между обходом вниз и обходом вверх — это условие соединения:

  • Вниз (поиск подчинённых): e.manager_id = cte.id — сопоставьте сотрудников, чей руководитель уже есть среди полученных строк.
  • Вверх (поиск руководителей): e.id = cte.manager_id — сопоставьте сотрудника, чей идентификатор указан как руководитель текущей строки.

Умение чётко объяснить это изменение направления впечатляет интервьюеров.

Построение дерева с отступами

Отточенный ответ форматирует результат в виде дерева с отступами, используя depth для повторения пробелов. Это показывает, что Вы умеете представлять результаты иерархии, а не только вычислять их.

WITH RECURSIVE org AS (
    SELECT id, name, 1 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', depth - 1) || name AS tree
FROM org
ORDER BY depth;

Накопление пути

Чтобы показать полный путь от CEO к каждому сотруднику, передавайте строку path. Это тот же приём, что и в предыдущем уроке, применённый к организационной структуре.

WITH RECURSIVE org AS (
    SELECT id, name, CAST(name AS VARCHAR(500)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.path || ' / ' || e.name
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, path FROM org ORDER BY path;

Подсчёт подчинённых каждого руководителя

Частый дополнительный вопрос: «Сколько человек, непосредственно или косвенно, подчиняется каждому руководителю?» Используйте рекурсивное поддерево для каждого руководителя, а затем агрегируйте результаты. Распространённый подход — запускать рекурсию один раз для каждого корня и группировать начального руководителя с помощью GROUP BY.

Здесь мы считаем всех косвенных подчинённых под Ada (CEO), проходя по всему дереву и подсчитывая строки ниже корня.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT COUNT(*) - 1 AS total_reports FROM org;

Распространённые ошибки

Следите за ловушками, которые устраивают интервьюеры:

  • Неверное направление соединения — использование e.manager_id = cte.id, когда нужно было подняться вверх, возвращает неправильный набор строк.
  • Забыто условие отбора якорной части — если опустить WHERE id = X, начальными строками станут все строки, и Вы получите весь лес.
  • Смещение глубины на единицу — решите, будет ли начальная строка иметь глубину 0 или 1, и придерживайтесь этого соглашения.

Почему не использовать простое самосоединение

Самосоединение позволяет получить фиксированное число уровней: одно соединение — для непосредственных подчинённых, два — для подчинённых второго уровня и так далее. Но глубину нужно знать заранее и писать отдельное соединение для каждого уровня.

Рекурсивный CTE обрабатывает произвольную, заранее неизвестную глубину в одном запросе. Когда интервьюер говорит: «В иерархии может быть любое число уровней», это исключает обычные самосоединения и указывает на рекурсию.

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

Убедитесь, что умеете менять направление обхода.

Итоги

Обход организационной структуры — это рекурсивный каркас, применяемый к таблице со ссылками на саму себя:

  • Вниз: задайте начального руководителя и выполните соединение e.manager_id = cte.id.
  • Вверх: задайте начального сотрудника и выполните соединение e.id = cte.manager_id.
  • Храните depth для отступов и path для полной цепочки.
  • Рекурсия обрабатывает любую неизвестную глубину, чего не может сделать самосоединение.

Далее: использование рекурсии для создания рядов чисел и дат.

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

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

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

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

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

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

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

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

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

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

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

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

  1. Якорная и рекурсивная части
  2. Обход организационной структуры
  3. Генерация последовательностей чисел и дат
  4. Предотвращение бесконечной рекурсии
← Назад к SQL Interview Prep