Lección 6.6: Aplicando CTEs Recursivas a Problemas del Mundo Real

En la lección anterior, exploramos CTEs regulares (no recursivas): una herramienta para organizar y estructurar consultas SQL. Ahora pasamos a su variante más poderosa: CTEs recursivas.

Las CTEs recursivas te permiten trabajar con estructuras de datos jerárquicas, en forma de árbol y de red. Resuelven muchos problemas del mundo real que tradicionalmente requerían código procedural o procedimientos almacenados complejos.

CTE Recursiva

¿Qué es una CTE Recursiva?

Una CTE recursiva es una CTE que se referencia a sí misma, permitiéndote recorrer estructuras jerárquicas nivel por nivel.

La estructura de una CTE recursiva:

WITH RECURSIVE cte_name AS (
    -- MIEMBRO ANCLA (caso base)
    SELECT ... 
    WHERE anchor_condition
    
    UNION ALL
    
    -- MIEMBRO RECURSIVO (cómo moverse al siguiente nivel)
    SELECT ...
    FROM cte_name
    WHERE stop_condition
)
SELECT * FROM cte_name;

Componentes clave:

Miembro ancla — el punto de partida de la recursión (generalmente registros raíz)
UNION ALL — combina resultados del ancla y la recursión
Miembro recursivo — cómo transitar de un nivel a otro
Condición de parada — cuándo terminar la recursión

Ejemplo 1: Jerarquía de Categorías

Una de las aplicaciones más comunes es trabajar con jerarquías de categorías de productos en comercio electrónico.

Estructura de la tabla:

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(100),
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

INSERT INTO categories VALUES
(1, NULL, 'Electrónica'),
(2, 1, 'Computadoras'),
(3, 1, 'Teléfonos Móviles'),
(4, 2, 'Portátiles'),
(5, 2, 'PC de Escritorio'),
(6, 4, 'Portátiles Dell'),
(7, 4, 'Portátiles HP'),
(8, 3, 'Smartphones'),
(9, 3, 'Tabletas');

Tarea: Obtener la jerarquía completa de categorías desde la raíz hasta las hojas

WITH RECURSIVE category_hierarchy AS (
    -- Miembro ancla: comenzar con categorías raíz
    SELECT
        category_id,
        parent_id,
        name,
        1 AS level,
        name AS full_path
    FROM
        categories
    WHERE
        parent_id IS NULL
    
    UNION ALL
    
    -- Miembro recursivo: agregar categorías hijas
    SELECT
        c.category_id,
        c.parent_id,
        c.name,
        ch.level + 1,
        CONCAT(ch.full_path, ' → ', c.name)
    FROM
        categories c
    JOIN
        category_hierarchy ch ON c.parent_id = ch.category_id
)
SELECT
    category_id,
    REPEAT('  ', level - 1) AS indent,
    name,
    level,
    full_path
FROM
    category_hierarchy
ORDER BY
    level,
    category_id;

Resultado:

category_id | indent | name              | level | full_path
1           |        | Electrónica       | 1     | Electrónica
2           |   | Computadoras       | 2     | Electrónica → Computadoras
4           |       | Portátiles       | 3     | Electrónica → Computadoras → Portátiles
6           |           | Portátiles Dell  | 4     | Electrónica → Computadoras → Portátiles → Portátiles Dell
7           |           | Portátiles HP    | 4     | Electrónica → Computadoras → Portátiles → Portátiles HP
5           |       | PC de Escritorio  | 3     | Electrónica → Computadoras → PC de Escritorio
3           |   | Teléfonos Móviles | 2     | Electrónica → Teléfonos Móviles
8           |       | Smartphones       | 3     | Electrónica → Teléfonos Móviles → Smartphones
9           |       | Tabletas         | 3     | Electrónica → Teléfonos Móviles → Tabletas

Qué sucede:

El miembro ancla encuentra solo Electrónica (parent_id IS NULL)
El miembro recursivo encuentra Computadoras y Teléfonos Móviles (hijos de Electrónica)
El proceso se repite hasta que se encuentran todas las hojas

Ejemplo 2: Organigrama

A menudo necesitas mostrar la estructura de una empresa con una cadena de mando.

Estructura de la tabla:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    manager_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES
(1, 'John Smith', 'Director Ejecutivo', NULL, 150000),
(2, 'Anna Johnson', 'Directora de Ventas', 1, 100000),
(3, 'Peter Williams', 'Director de TI', 1, 120000),
(4, 'Maria Brown', 'Gerente de Ventas', 2, 60000),
(5, 'Alex Davis', 'Gerente de Ventas', 2, 60000),
(6, 'Sergei Miller', 'Desarrollador Senior', 3, 90000),
(7, 'Olga Wilson', 'Desarrollador', 6, 70000),
(8, 'Dmitry Moore', 'Desarrollador', 6, 70000);

Tarea: Mostrar la estructura organizativa con la cadena de mando

WITH RECURSIVE org_chart AS (
    -- Ancla: Director Ejecutivo
    SELECT
        employee_id,
        name,
        position,
        manager_id,
        salary,
        1 AS level,
        name AS management_chain
    FROM
        employees
    WHERE
        manager_id IS NULL
    
    UNION ALL
    
    -- Recursión: agregar subordinados
    SELECT
        e.employee_id,
        e.name,
        e.position,
        e.manager_id,
        e.salary,
        oc.level + 1,
        CONCAT(oc.management_chain, ' → ', e.name)
    FROM
        employees e
    JOIN
        org_chart oc ON e.manager_id = oc.employee_id
    WHERE
        oc.level < 10  -- Protección contra recursión infinita
)
SELECT
    employee_id,
    REPEAT('│ ', level - 1) AS hierarchy,
    name,
    position,
    salary,
    management_chain
FROM
    org_chart
ORDER BY
    level,
    employee_id;

Resultado:

employee_id | hierarchy | name         | position                | salary | management_chain
1           |          | John Smith   | Director Ejecutivo       | 150000 | John Smith
2           | │        | Anna Johnson | Directora de Ventas     | 100000 | John Smith → Anna Johnson
4           | │ │      | Maria Brown  | Gerente de Ventas       | 60000  | John Smith → Anna Johnson → Maria Brown
5           | │ │      | Alex Davis   | Gerente de Ventas       | 60000  | John Smith → Anna Johnson → Alex Davis
3           | │        | Peter Williams| Director de TI          | 120000 | John Smith → Peter Williams
6           | │ │      | Sergei Miller| Desarrollador Senior    | 90000  | John Smith → Peter Williams → Sergei Miller
7           | │ │ │    | Olga Wilson  | Desarrollador           | 70000  | John Smith → Peter Williams → Sergei Miller → Olga Wilson
8           | │ │ │    | Dmitry Moore | Desarrollador           | 70000  | John Smith → Peter Williams → Sergei Miller → Dmitry Moore

Aplicación: Calcular el presupuesto del departamento

WITH RECURSIVE org_chart AS (
    SELECT
        employee_id,
        name,
        position,
        salary,
        1 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL
    
    UNION ALL
    
    SELECT
        e.employee_id,
        e.name,
        e.position,
        e.salary,
        oc.level + 1
    FROM
        employees e
    JOIN
        org_chart oc ON e.manager_id = oc.employee_id
)
SELECT
    name AS position,
    COUNT(*) AS number_of_employees,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS average_salary
FROM
    org_chart
GROUP BY
    employee_id,
    name
ORDER BY
    SUM(salary) DESC;

Ejemplo 3: Lista de Materiales (BOM)

En la fabricación, necesitas saber qué componentes componen un producto.

Estructura de la tabla:

CREATE TABLE bom (
    product_id INT,
    component_id INT,
    quantity INT,
    PRIMARY KEY (product_id, component_id)
);

INSERT INTO bom VALUES
(1, 2, 1),      -- El portátil consta de 1 placa base
(1, 3, 2),      -- y 2 módulos de RAM
(1, 4, 1),      -- y 1 disco duro
(2, 5, 1),      -- La placa base consta de 1 chipset
(2, 6, 20),     -- y 20 resistencias
(4, 7, 1);      -- El disco duro consta de 1 husillo

Tarea: Expandir BOM a la lista completa de componentes

WITH RECURSIVE bom_expanded AS (
    -- Ancla: productos terminados (no utilizados como componentes)
    SELECT
        product_id,
        product_id AS component_id,
        1 AS quantity,
        0 AS level,
        CAST(product_id AS CHAR(100)) AS path
    FROM
        (SELECT DISTINCT product_id FROM bom
         UNION
         SELECT DISTINCT component_id FROM bom) AS products
    
    UNION ALL
    
    -- Recursión: expandir cada componente
    SELECT
        be.product_id,
        b.component_id,
        be.quantity * b.quantity,
        be.level + 1,
        CONCAT(be.path, ' → ', b.component_id)
    FROM
        bom_expanded be
    JOIN
        bom b ON be.component_id = b.product_id
    WHERE
        be.level < 10
)
SELECT
    product_id,
    component_id,
    quantity,
    level,
    path
FROM
    bom_expanded
WHERE
    level > 0
ORDER BY
    product_id,
    level,
    component_id;

Ejemplo 4: Estructura de Menú (Árboles de Menú)

Los sitios de comercio electrónico y las aplicaciones web a menudo tienen menús de múltiples niveles.

Estructura de la tabla:

CREATE TABLE menu (
    menu_id INT PRIMARY KEY,
    parent_menu_id INT,
    title VARCHAR(100),
    url VARCHAR(255),
    sort_order INT,
    FOREIGN KEY (parent_menu_id) REFERENCES menu(menu_id)
);

INSERT INTO menu VALUES
(1, NULL, 'Inicio', '/', 1),
(2, NULL, 'Catálogo', '/catalog', 2),
(3, NULL, 'Acerca de Nosotros', '/about', 3),
(4, 2, 'Computadoras', '/catalog/computers', 1),
(5, 2, 'Accesorios', '/catalog/accessories', 2),
(6, 4, 'Portátiles', '/catalog/computers/laptops', 1),
(7, 4, 'PC de Escritorio', '/catalog/computers/desktops', 2),
(8, 5, 'Ratones', '/catalog/accessories/mice', 1),
(9, 5, 'Teclados', '/catalog/accessories/keyboards', 2),
(10, 3, 'Historia', '/about/history', 1),
(11, 3, 'Equipo', '/about/team', 2);

Tarea: Mostrar el menú como un árbol con sangrías

WITH RECURSIVE menu_tree AS (
    -- Ancla: elementos de menú principales
    SELECT
        menu_id,
        parent_menu_id,
        title,
        url,
        1 AS level,
        title AS breadcrumb
    FROM
        menu
    WHERE
        parent_menu_id IS NULL
    ORDER BY
        sort_order
    
    UNION ALL
    
    -- Recursión: submenús
    SELECT
        m.menu_id,
        m.parent_menu_id,
        m.title,
        m.url,
        mt.level + 1,
        CONCAT(mt.breadcrumb, ' > ', m.title)
    FROM
        menu m
    JOIN
        menu_tree mt ON m.parent_menu_id = mt.menu_id
)
SELECT
    menu_id,
    REPEAT('  ', level - 1) AS indent,
    title,
    url,
    breadcrumb
FROM
    menu_tree
ORDER BY
    level,
    menu_id;

Resultado:

menu_id | indent | title            | url                      | breadcrumb
1       |        | Inicio           | /                        | Inicio
2       |        | Catálogo         | /catalog                 | Catálogo
4       |   | Computadoras     | /catalog/computers       | Catálogo > Computadoras
6       |       | Portátiles       | /catalog/computers/laptops | Catálogo > Computadoras > Portátiles
7       |       | PC de Escritorio  | /catalog/computers/desktops | Catálogo > Computadoras > PC de Escritorio
5       |   | Accesorios       | /catalog/accessories     | Catálogo > Accesorios
8       |       | Ratones          | /catalog/accessories/mice | Catálogo > Accesorios > Ratones
9       |       | Teclados         | /catalog/accessories/keyboards | Catálogo > Accesorios > Teclados
3       |        | Acerca de Nosotros | /about                   | Acerca de Nosotros
10      |   | Historia         | /about/history           | Acerca de Nosotros > Historia
11      |   | Equipo           | /about/team              | Acerca de Nosotros > Equipo

Ejemplo 5: Búsqueda de Rutas en Grafos

Las CTEs recursivas se utilizan para encontrar todas las rutas entre dos nodos en un grafo (por ejemplo, rutas de entrega en un sistema logístico).

Estructura de la tabla:

CREATE TABLE cities (
    city_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE routes (
    from_city_id INT,
    to_city_id INT,
    distance INT,
    PRIMARY KEY (from_city_id, to_city_id),
    FOREIGN KEY (from_city_id) REFERENCES cities(city_id),
    FOREIGN KEY (to_city_id) REFERENCES cities(city_id)
);

INSERT INTO cities VALUES (1, 'Moscú'), (2, 'Tver'), (3, 'Smolensk'), (4, 'Bryansk');

INSERT INTO routes VALUES
(1, 2, 170),   -- Moscú → Tver
(2, 3, 220),   -- Tver → Smolensk
(1, 4, 380),   -- Moscú → Bryansk
(4, 3, 250);   -- Bryansk → Smolensk

Tarea: Encontrar todas las rutas de Moscú a Smolensk

WITH RECURSIVE routes_search AS (
    -- Ancla: comenzar desde Moscú (city_id = 1)
    SELECT
        from_city_id,
        to_city_id,
        distance,
        1 AS hops,
        CAST(to_city_id AS CHAR(1000)) AS path,
        distance AS total_distance
    FROM
        routes
    WHERE
        from_city_id = 1
    
    UNION ALL
    
    -- Recursión: continuar desde cada destino
    SELECT
        rs.from_city_id,
        r.to_city_id,
        r.distance,
        rs.hops + 1,
        CONCAT(rs.path, ',', r.to_city_id),
        rs.total_distance + r.distance
    FROM
        routes_search rs
    JOIN
        routes r ON rs.to_city_id = r.from_city_id
    WHERE
        rs.hops < 10  -- Prevenir ciclos
        AND rs.path NOT LIKE CONCAT('%,', r.to_city_id, '%')  -- Evitar bucles
)
SELECT
    from_city_id,
    to_city_id,
    hops,
    path,
    total_distance,
    ROUND(total_distance / hops, 2) AS average_distance_between_cities
FROM
    routes_search
WHERE
    to_city_id = 3  -- Destino: Smolensk
ORDER BY
    hops,
    total_distance;

Mejores Prácticas y Optimización

1. Siempre Define una Condición de Parada

La recursión sin una condición de parada llevará a bucles infinitos:

-- ❌ MALO: Puede llevar a recursión infinita
WITH RECURSIVE bad_recursion AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM bad_recursion
)
SELECT * FROM bad_recursion;

-- ✅ BUENO: Condición de parada incluida
WITH RECURSIVE good_recursion AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM good_recursion
    WHERE n < 1000
)
SELECT * FROM good_recursion;

2. Usa UNION ALL en lugar de UNION

UNION ALL no elimina duplicados y se ejecuta más rápido:

-- ❌ Más lento
WITH RECURSIVE cte AS (
    SELECT ...
    UNION  -- Elimina duplicados
    SELECT ...
)

-- ✅ Más rápido
WITH RECURSIVE cte AS (
    SELECT ...
    UNION ALL  -- No elimina duplicados
    SELECT ...
)

3. Evita Referencias Circulares

Usa verificación de ruta para prevenir ciclos:

WITH RECURSIVE safe_recursion AS (
    SELECT
        id,
        parent_id,
        CAST(id AS CHAR(1000)) AS path
    FROM
        table_name
    WHERE
        parent_id IS NULL
    
    UNION ALL
    
    SELECT
        t.id,
        t.parent_id,
        CONCAT(sr.path, ',', t.id)
    FROM
        table_name t
    JOIN
        safe_recursion sr ON t.parent_id = sr.id
    WHERE
        sr.path NOT LIKE CONCAT('%,', t.id, '%')  -- Verificación de ciclo
        AND sr.path NOT LIKE CONCAT(t.id, ',%')
)
SELECT * FROM safe_recursion;

4. Limita la Profundidad de la Recursión

Limita explícitamente la profundidad máxima de la recursión:

WITH RECURSIVE limited_recursion AS (
    SELECT
        id,
        parent_id,
        0 AS level
    FROM table_name
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT
        t.id,
        t.parent_id,
        lr.level + 1
    FROM
        table_name t
    JOIN
        limited_recursion lr ON t.parent_id = lr.id
    WHERE
        lr.level < 20  -- Máximo 20 niveles
)
SELECT * FROM limited_recursion;

Matriz de Aplicaciones para CTEs Recursivas

Tarea	Ejemplo	Complejidad
Jerarquía de categorías	Categorías de productos en una tienda	Baja
Organigrama	Estructura de la empresa, cadena de informes	Baja
BOM (Lista de Materiales)	Composición de productos manufacturados	Media
Estructura de menú	Árboles de navegación en sitios web	Baja
Búsqueda de rutas	Rutas de entrega, grafos de relaciones	Alta
Árboles de comentarios	Comentarios anidados en redes sociales	Media
Grafos de dependencia	Proyectos y subtareas	Media
Trazabilidad	Seguimiento del origen de materiales	Media

Conclusiones Clave

CTEs recursivas son herramientas poderosas para trabajar con datos jerárquicos
Estructura: miembro ancla + miembro recursivo + condición de parada
Miembro ancla define las filas de inicio
Miembro recursivo agrega nuevas filas basadas en las anteriores
Condición de parada previene bucles infinitos
Aplicaciones prácticas: categorías, estructuras organizativas, BOMs, menús, búsqueda de rutas
Rendimiento: Cuanto más simple sea la estructura, más rápida será la ejecución
Alternativas: Se puede usar código procedural, pero las CTEs recursivas son a menudo más simples y claras

Las CTEs recursivas transforman consultas jerárquicas complejas de rompecabezas en SQL comprensible. Son una herramienta indispensable para cualquier persona que trabaje con estructuras de datos en árbol y de red.

En lecciones posteriores, exploraremos técnicas avanzadas de optimización y funciones SQL especializadas.

Práctica

Lecciones

Entorno de Pruebas

Prueba

Lección 6.6: Aplicando CTEs Recursivas a Problemas del Mundo Real

¿Qué es una CTE Recursiva?

Ejemplo 1: Jerarquía de Categorías

Ejemplo 2: Organigrama

Ejemplo 3: Lista de Materiales (BOM)

Ejemplo 4: Estructura de Menú (Árboles de Menú)

Ejemplo 5: Búsqueda de Rutas en Grafos

Mejores Prácticas y Optimización

1. Siempre Define una Condición de Parada

2. Usa UNION ALL en lugar de UNION

3. Evita Referencias Circulares

4. Limita la Profundidad de la Recursión

Matriz de Aplicaciones para CTEs Recursivas

Conclusiones Clave

Iniciar sesión

Registrarse

Lección 6.6: Aplicando CTEs Recursivas a Problemas del Mundo Real

¿Qué es una CTE Recursiva?

Ejemplo 1: Jerarquía de Categorías

Ejemplo 2: Organigrama

Ejemplo 3: Lista de Materiales (BOM)

Ejemplo 4: Estructura de Menú (Árboles de Menú)

Ejemplo 5: Búsqueda de Rutas en Grafos

Mejores Prácticas y Optimización

1. Siempre Define una Condición de Parada

2. Usa UNION ALL en lugar de UNION

3. Evita Referencias Circulares

4. Limita la Profundidad de la Recursión

Matriz de Aplicaciones para CTEs Recursivas

Conclusiones Clave