Работа с иерархическими данными: рекурсивные запросы в PostgreSQL

В этой статье мы разберем, как эффективно работать с древовидными структурами данных в PostgreSQL на примере организационной иерархии компании. Рекурсивные запросы — это инструмент, который позволяет обрабатывать вложенные связи без сложных программных конструкций.

Подготовка данных: создаем структуру подчинения

Начнем с расширения нашей таблицы сотрудников, добавив поле для хранения связей подчинения:

-- Добавляем поле для идентификатора руководителя
ALTER TABLE employees ADD COLUMN manager_id INTEGER;

-- Создаем индекс для оптимизации запросов по связям
CREATE INDEX idx_employees_manager_id ON employees(manager_id);

После подготовки структуры наполняем таблицу данными, отражающими реальную иерархию:

• Руководители высшего уровня (без manager_id или с NULL)

• Менеджеры среднего звена (указывают на вышестоящих руководителей)

• Рядовые сотрудники (указывают на своих менеджеров)

Пример данных после вставки:

Структура рекурсивного запроса

Рекурсивный запрос состоит из двух ключевых частей:

1. Якорная часть (Anchor) — определяет начальные записи (вершину иерархии)

2. Рекурсивная часть (Recursive Member) — описывает, как получать следующие уровни на основе предыдущих

Пример 1: Построение полной организационной иерархии

WITH RECURSIVE employee_hierarchy AS (
    -- Якорная часть: сотрудники без руководителей
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Рекурсивная часть: подчиненные
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1,
        eh.path || ' -> ' || e.name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy
ORDER BY path;

Результат показывает полную цепочку подчинения с указанием уровня вложенности и визуального пути от руководителя к сотруднику.

Пример 2: Находим всех подчиненных конкретного менеджера

Допустим, мы хотим увидеть всех сотрудников, которые прямо или косвенно подчиняются Алексею:

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Алексей'
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Этот запрос полезен для:

• Определения зоны ответственности руководителя

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

• Распределения задач в рамках команды

Пример 3: Определяем цепочку руководства для сотрудника

Теперь решим обратную задачу: найдем всех руководителей конкретного сотрудника вплоть до самого верха:

WITH RECURSIVE management_chain AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Екатерина'
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN management_chain mc ON e.id = mc.manager_id
)
SELECT * FROM management_chain;

Такой подход позволяет:

• Определять путь принятия решений

• Строить матрицы согласований

• Анализировать структуру коммуникаций

Практическое применение рекурсивных запросов

Рекурсивные запросы выходят далеко за рамки организационных структур. Вот несколько практических сценариев их использования:

1. Каталоги товаров — отображение вложенных категорий и подкатегорий

2. Навигация сайта — построение многоуровневых меню

3. Социальные графы — поиск связей между пользователями

4. Маршрутизация — анализ сетевых подключений и путей

5. Обработка комментариев — отображение древовидных обсуждений

Важные моменты при работе с рекурсией

• Убедитесь, что в данных нет циклических ссылок

• Используйте ограничение глубины рекурсии при необходимости

• Создавайте индексы на полях, участвующих в рекурсивных связях

• Тестируйте запросы на разных объемах данных

Заключение

Рекурсивные запросы в PostgreSQL открывают элегантный способ работы с иерархическими данными. Они заменяют сложные многоуровневые JOIN-операции на простые и читаемые конструкции.

Если вы еще не применяли рекурсивные запросы в своих проектах, обязательно попробуйте этот подход для обработки древовидных структур. А для более наглядного знакомства с темой, включая пошаговое выполнение каждого запроса, посмотрите наше видео, где мы детально разбираем каждый пример с визуализацией результатов.

Статья подготовлена по материалам обучающего видео для вашего удобства.