PrepBro
← Назад к вопросам

Что такое рекурсия в запросе в БД?

1.0 Junior🔥 251 комментариев
#Базы данных (SQL)

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Рекурсия в запросах БД (CTE и рекурсивные запросы)

Рекурсия в запросах БД — это способ работы с иерархическими данными (дерево категорий, структура подразделений, графы). Реализуется через рекурсивные CTE (Common Table Expression) или рекурсивные запросы.

Что такое рекурсивный запрос?

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

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

WITH RECURSIVE cte_name AS (
    -- ЯКОРНАЯ ЧАСТЬ (базовый случай)
    SELECT ... FROM table WHERE condition
    
    UNION ALL
    
    -- РЕКУРСИВНАЯ ЧАСТЬ
    SELECT ... FROM table
    INNER JOIN cte_name ON condition
    WHERE condition  -- Условие выхода
)
SELECT * FROM cte_name;

Пример 1: Иерархия категорий

-- Таблица категорий с parent_id
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

INSERT INTO categories VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Laptops', 2),
    (4, 'Phones', 1),
    (5, 'Smartphones', 4);

-- РЕКУРСИВНЫЙ ЗАПРОС: получить всю иерархию
WITH RECURSIVE category_tree AS (
    -- Якорная часть: начинаем с корневых категорий
    SELECT id, name, parent_id, 0 as level, name as path
    FROM categories
    WHERE parent_id IS NULL  -- Корневые элементы
    
    UNION ALL
    
    -- Рекурсивная часть: находим детей
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        ct.level + 1,
        CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.level < 10  -- Защита от бесконечной рекурсии
)
SELECT id, name, level, path FROM category_tree
ORDER BY path;

-- Результат:
-- id | name       | level | path
-- 1  | Electronics| 0     | Electronics
-- 2  | Computers  | 1     | Electronics > Computers
-- 3  | Laptops    | 2     | Electronics > Computers > Laptops
-- 4  | Phones     | 1     | Electronics > Phones
-- 5  | Smartphones| 2     | Electronics > Phones > Smartphones

Пример 2: Генерация последовательности чисел

-- Простая рекурсия для генерации чисел от 1 до 5
WITH RECURSIVE numbers AS (
    -- Якорная часть
    SELECT 1 as num
    
    UNION ALL
    
    -- Рекурсивная часть
    SELECT num + 1
    FROM numbers
    WHERE num < 5  -- Условие выхода
)
SELECT * FROM numbers;

-- Результат: 1, 2, 3, 4, 5

Пример 3: Граф (организационная структура)

-- Таблица сотрудников
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'CEO John', NULL),
    (2, 'Manager Alice', 1),
    (3, 'Manager Bob', 1),
    (4, 'Developer Charlie', 2),
    (5, 'Developer Diana', 2),
    (6, 'Developer Eve', 3);

-- Найти всю цепочку подчинения
WITH RECURSIVE org_chart AS (
    -- Начинаем с CEO
    SELECT id, name, manager_id, 0 as depth
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Находим прямых подчиненных
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', depth) || name as organization
FROM org_chart
ORDER BY id;

Python пример с SQLAlchemy

from sqlalchemy import select, text, func, create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('categories.id'))

# Рекурсивный запрос в Python
def get_category_tree(session: Session, root_id: int):
    """
    Получает всю иерархию категорий
    """
    query = text("""
    WITH RECURSIVE category_tree AS (
        SELECT id, name, parent_id, 0 as level
        FROM categories
        WHERE parent_id = :root_id
        
        UNION ALL
        
        SELECT c.id, c.name, c.parent_id, ct.level + 1
        FROM categories c
        INNER JOIN category_tree ct ON c.parent_id = ct.id
        WHERE ct.level < 5
    )
    SELECT id, name, level FROM category_tree
    """)
    
    results = session.execute(query, {"root_id": root_id})
    return results.fetchall()

# Использование
engine = create_engine('postgresql://...')
with Session(engine) as session:
    tree = get_category_tree(session, 1)
    for cat_id, name, level in tree:
        print('  ' * level + name)

Рекурсия vs JOIN: когда использовать?

-- ПЛОХО: множество JOIN для глубокой иерархии
SELECT 
    c1.name as level1,
    c2.name as level2,
    c3.name as level3
FROM categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id
LEFT JOIN categories c3 ON c3.parent_id = c2.id;

-- ХОРОШО: рекурсивный CTE (один запрос, неограниченная глубина)
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Защита от бесконечной рекурсии

WITH RECURSIVE numbered_rows AS (
    SELECT 1 as n
    
    UNION ALL
    
    SELECT n + 1
    FROM numbered_rows
    WHERE n < 1000000  -- ОБЯЗАТЕЛЬНО! Условие выхода
)
SELECT * FROM numbered_rows;

Производительность рекурсивных запросов

Плюсы:

  • Один запрос вместо N
  • Работает для любой глубины иерархии
  • Понятная логика

Минусы:

  • Медленнее при больших объемах
  • Может использовать много памяти
  • Не все БД одинаково оптимизируют
# Оптимизация: используй LIMIT для ограничения рекурсии
query = text("""
WITH RECURSIVE tree AS (
    ...
    WHERE level < 10  -- Ограничь глубину
)
SELECT * FROM tree;
""")

Вывод

Рекурсивные запросы (CTE) — это мощный инструмент для работы с иерархическими и графовыми данными. Используй их когда:

  • Данные организованы в иерархию (категории, чины, регионы)
  • Нужно найти все связанные элементы
  • Глубина иерархии неизвестна заранее

Убедись, что:

  • Правильно определен якорный запрос
  • Есть условие выхода (WHERE)
  • Производительность приемлема для твоего случая
Что такое рекурсия в запросе в БД? | PrepBro