Рекурсивные CTE: WITH RECURSIVE — работа с иерархическими данными

Что такое рекурсивные CTE и зачем они нужны? 🔄

Рекурсивные CTE (Common Table Expressions) — это мощный инструмент SQL для работы с иерархическими данными. Они позволяют обрабатывать сложные структуры, такие как:

• Организационные структуры (подчинённые → начальник → директор)
• Деревья категорий (родительские → дочерние элементы)
• Графы маршрутов (точка A → точка B → точка C)

Без рекурсивных CTE такие запросы превращаются в кошмар из множества JOIN'ов!

Синтаксис рекурсивного CTE: просто, как дважды два ✌️

Базовый шаблон рекурсивного запроса:

WITH RECURSIVE recursion_name AS (
    -- Базовый случай (нерекурсивная часть)
    SELECT initial_columns FROM table WHERE condition

    UNION [ALL]

    -- Рекурсивный случай
    SELECT next_columns FROM recursion_name JOIN table ON condition
)
SELECT * FROM recursion_name;

Где:

• WITH RECURSIVE — объявление рекурсивного CTE
• Первая часть до UNION — стартовая точка (базовый случай)
• Вторая часть после UNION — рекурсивное продолжение

Практический пример: иерархия сотрудников 💼

Допустим, у нас есть таблица сотрудников с полями id, name и manager_id (ссылка на руководителя):

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

INSERT INTO employees VALUES
(1, 'Иван Петров', NULL),  -- Генеральный директор
(2, 'Мария Сидорова', 1),
(3, 'Алексей Иванов', 1),
(4, 'Елена Кузнецова', 2),
(5, 'Дмитрий Смирнов', 2),
(6, 'Ольга Васнецова', 3);

Задача: найти всех подчинённых конкретного руководителя (включая косвенных)

WITH RECURSIVE subordinates AS (
    -- Базовый случай: непосредственные подчинённые
    SELECT id, name, manager_id FROM employees WHERE manager_id = 2

    UNION

    -- Рекурсивный случай: подчинённые подчинённых
    SELECT e.id, e.name, e.manager_id 
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Результат покажет Марию Сидорову (ID=2) и всех, кто прямо или косвенно ей подчиняется.

Осторожно: рекурсия может быть опасной! ⚠️

Рекурсивные запросы требуют аккуратности:

1. База рекурсии — всегда указывайте условие остановки
2. Глубина рекурсии — СУБД обычно имеет лимит (например, PostgreSQL — 1000 итераций по умолчанию)
3. Производительность — рекурсивные CTE могут быть медленными на больших данных

Пример с ограничением глубины:

WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE id = 1  -- Начинаем с CEO

    UNION

    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
    WHERE h.level < 3  -- Ограничиваем глубину 3 уровнями
)
SELECT * FROM hierarchy;

Продвинутая техника: обход графов 🗺️

Рекурсивные CTE отлично подходят для работы с графами. Рассмотрим пример маршрутов между городами:

CREATE TABLE routes (
    from_city VARCHAR(100),
    to_city VARCHAR(100),
    distance_km INT
);

INSERT INTO routes VALUES
('Москва', 'Санкт-Петербург', 700),
('Москва', 'Казань', 800),
('Санкт-Петербург', 'Хельсинки', 300),
('Казань', 'Екатеринбург', 900),
('Екатеринбург', 'Новосибирск', 1500);

Найти все достижимые города из Москвы:

WITH RECURSIVE reachable_cities AS (
    SELECT to_city AS city, distance_km AS total_distance 
    FROM routes WHERE from_city = 'Москва'

    UNION

    SELECT r.to_city, rc.total_distance + r.distance_km
    FROM routes r
    JOIN reachable_cities rc ON r.from_city = rc.city
)
SELECT * FROM reachable_cities;

Этот запрос покажет все города, до которых можно добраться из Москвы прямо или через пересадки, с подсчётом общего расстояния.

Итоги: когда использовать рекурсивные CTE 🏆

1. Работа с древовидными структурами (категории, сотрудники)
2. Обход графов (маршруты, социальные связи)
3. Генерация последовательностей (даты, числа)
4. Решение задач с неопределённой глубиной вложенности

Главное преимущество — читаемость и простота по сравнению с альтернативами (хранимые процедуры, множественные JOIN'ы).

Попробуйте применить рекурсивные CTE к своим данным — вы удивитесь, насколько они могут упростить сложные запросы! 🚀