Lección 6.5: CTEs Recursivas para Datos Jerárquicos

Las CTEs recursivas son una de las características más poderosas en SQL, permitiéndote trabajar con datos jerárquicos y estructurados en forma de árbol. En esta lección, exploraremos cómo usar las Expresiones de Tabla Comunes recursivas para consultar datos que tienen relaciones de padre-hijo, como organigramas, árboles de categorías y listas de materiales.

¿Qué Son las CTEs Recursivas?

Una CTE Recursiva es una Expresión de Tabla Común que se referencia a sí misma, permitiendo recorrer estructuras de datos jerárquicas. A diferencia de las CTEs regulares que se ejecutan una vez, las CTEs recursivas se ejecutan de manera iterativa hasta que se cumple una condición de terminación.

Casos de uso comunes para las CTEs recursivas:

Jerarquías organizacionales: Relaciones entre empleados y gerentes
Árboles de categorías: Categorías de productos con subcategorías
Lista de Materiales (BOM): Relaciones entre partes y subpartes
Estructuras de sistemas de archivos: Carpetas y subcarpetas
Redes sociales: Relaciones de amigo de amigo
Jerarquías geográficas: Relaciones entre País > Estado > Ciudad

Sintaxis de CTE Recursiva

La sintaxis general para una CTE recursiva es:

WITH RECURSIVE cte_name AS (
    -- Miembro ancla (caso base)
    SELECT ...
    FROM table
    WHERE condition
    
    UNION ALL
    
    -- Miembro recursivo (caso recursivo)
    SELECT ...
    FROM table
    JOIN cte_name ON condition
)
SELECT * FROM cte_name;

Componentes:

WITH RECURSIVE: Palabra clave que introduce una CTE recursiva
Miembro ancla: La consulta inicial que devuelve las filas de inicio (caso base)
UNION ALL: Combina los miembros ancla y recursivos
Miembro recursivo: La consulta que referencia la CTE misma
Terminación: La recursión se detiene cuando el miembro recursivo no devuelve filas

Cómo Funcionan las CTEs Recursivas

El proceso de ejecución:

Ejecutar el miembro ancla: Obtiene el conjunto inicial de filas
Ejecutar el miembro recursivo: Usa los resultados del paso 1
Repetir el paso 2: Usa los resultados de la iteración anterior
Continuar hasta: El miembro recursivo no devuelve filas
Devolver todos los resultados: Resultados combinados de todas las iteraciones

Ejemplo Básico: Secuencia de Números

Comencemos con un ejemplo simple que genera una secuencia de números:

WITH RECURSIVE number_sequence AS (
    -- Miembro ancla: comienza con 1
    SELECT 1 AS n
    
    UNION ALL
    
    -- Miembro recursivo: suma 1 al valor anterior
    SELECT n + 1
    FROM number_sequence
    WHERE n < 10
)
SELECT n
FROM number_sequence;

Resultado:

Cómo funciona:

Ancla: Devuelve 1
Iteración 1: 1 + 1 = 2
Iteración 2: 2 + 1 = 3
... continúa hasta que n < 10 es falso
Iteración final: Devuelve 10, pero 10 < 10 es falso, por lo que la recursión se detiene

Ejemplo de Jerarquía de Empleados

Creamos una tabla que representa una estructura organizacional:

-- Tabla de empleados de ejemplo
CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT,
    title VARCHAR(100)
);

-- Datos de ejemplo
INSERT INTO employee VALUES
    (1, 'Alice Johnson', NULL, 'CEO'),
    (2, 'Bob Smith', 1, 'VP de Ingeniería'),
    (3, 'Carol White', 1, 'VP de Ventas'),
    (4, 'David Brown', 2, 'Gerente de Ingeniería'),
    (5, 'Eve Davis', 2, 'Gerente de Ingeniería'),
    (6, 'Frank Miller', 3, 'Gerente de Ventas'),
    (7, 'Grace Wilson', 4, 'Desarrollador Senior'),
    (8, 'Henry Moore', 4, 'Desarrollador'),
    (9, 'Ivy Taylor', 5, 'Desarrollador'),
    (10, 'Jack Anderson', 6, 'Representante de Ventas');

Encontrando Todos los Subordinados

Para encontrar todos los empleados que reportan a un gerente específico (directa o indirectamente):

WITH RECURSIVE subordinates AS (
    -- Ancla: Comienza con el gerente
    SELECT
        employee_id,
        employee_name,
        manager_id,
        title,
        0 AS level
    FROM
        employee
    WHERE
        employee_name = 'Bob Smith'
    
    UNION ALL
    
    -- Recursivo: Encuentra reportes directos
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        e.title,
        s.level + 1
    FROM
        employee e
    INNER JOIN
        subordinates s ON e.manager_id = s.employee_id
)
SELECT
    employee_id,
    employee_name,
    title,
    level
FROM
    subordinates
ORDER BY
    level, employee_name;

Resultado:

employee_id | employee_name   | title                | level
------------|-----------------|----------------------|------
2           | Bob Smith       | VP de Ingeniería     | 0
4           | David Brown     | Gerente de Ingeniería| 1
5           | Eve Davis       | Gerente de Ingeniería| 1
7           | Grace Wilson    | Desarrollador Senior | 2
8           | Henry Moore     | Desarrollador        | 2
9           | Ivy Taylor      | Desarrollador        | 2

Construyendo el Organigrama Completo

Para mostrar la jerarquía completa desde el CEO hacia abajo:

WITH RECURSIVE org_chart AS (
    -- Ancla: Comienza con el CEO (sin gerente)
    SELECT
        employee_id,
        employee_name,
        manager_id,
        title,
        0 AS level,
        CAST(employee_name AS VARCHAR(1000)) AS path
    FROM
        employee
    WHERE
        manager_id IS NULL
    
    UNION ALL
    
    -- Recursivo: Agrega cada nivel
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        e.title,
        oc.level + 1,
        CONCAT(oc.path, ' > ', e.employee_name)
    FROM
        employee e
    INNER JOIN
        org_chart oc ON e.manager_id = oc.employee_id
)
SELECT
    REPEAT('  ', level) || employee_name AS hierarchy,
    title,
    level,
    path
FROM
    org_chart
ORDER BY
    path;

Resultado:

hierarchy                      | title                | level | path
-------------------------------|----------------------|-------|---------------------------
Alice Johnson                  | CEO                  | 0     | Alice Johnson
  Bob Smith                    | VP de Ingeniería     | 1     | Alice Johnson > Bob Smith
    David Brown                | Gerente de Ingeniería| 2     | Alice Johnson > Bob Smith > David Brown
      Grace Wilson             | Desarrollador Senior | 3     | Alice Johnson > Bob Smith > David Brown > Grace Wilson
      Henry Moore              | Desarrollador        | 3     | Alice Johnson > Bob Smith > David Brown > Henry Moore
    Eve Davis                  | Gerente de Ingeniería| 2     | Alice Johnson > Bob Smith > Eve Davis
      Ivy Taylor               | Desarrollador        | 3     | Alice Johnson > Bob Smith > Eve Davis > Ivy Taylor
  Carol White                  | VP de Ventas         | 1     | Alice Johnson > Carol White
    Frank Miller               | Gerente de Ventas    | 2     | Alice Johnson > Carol White > Frank Miller
      Jack Anderson            | Representante de Ventas| 3     | Alice Johnson > Carol White > Frank Miller > Jack Anderson

Ejemplo de Árbol de Categorías

Trabajemos con una jerarquía de categorías de productos:

-- Tabla de categorías de ejemplo
CREATE TABLE category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100),
    parent_category_id INT
);

-- Datos de ejemplo
INSERT INTO category VALUES
    (1, 'Electrónica', NULL),
    (2, 'Computadoras', 1),
    (3, 'Teléfonos', 1),
    (4, 'Laptops', 2),
    (5, 'Escritorios', 2),
    (6, 'Laptops para Juegos', 4),
    (7, 'Laptops de Negocios', 4),
    (8, 'Smartphones', 3),
    (9, 'Teléfonos Básicos', 3);

Encontrando Todas las Subcategorías

Para encontrar todas las subcategorías bajo "Computadoras":

WITH RECURSIVE category_tree AS (
    -- Ancla: Comienza con Computadoras
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth,
        CAST(category_name AS VARCHAR(1000)) AS path
    FROM
        category
    WHERE
        category_name = 'Computadoras'
    
    UNION ALL
    
    -- Recursivo: Obtiene todas las subcategorías
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1,
        CONCAT(ct.path, ' > ', c.category_name)
    FROM
        category c
    INNER JOIN
        category_tree ct ON c.parent_category_id = ct.category_id
)
SELECT
    category_id,
    REPEAT('  ', depth) || category_name AS category_hierarchy,
    depth,
    path
FROM
    category_tree
ORDER BY
    path;

Resultado:

category_id | category_hierarchy    | depth | path
------------|----------------------|-------|--------------------------------
2           | Computadoras         | 0     | Computadoras
4           |   Laptops            | 1     | Computadoras > Laptops
6           |     Laptops para Juegos| 2     | Computadoras > Laptops > Laptops para Juegos
7           |     Laptops de Negocios| 2     | Computadoras > Laptops > Laptops de Negocios
5           |   Escritorios         | 1     | Computadoras > Escritorios

Encontrando Ancestros

Para encontrar todas las categorías padre de una categoría específica:

WITH RECURSIVE category_ancestors AS (
    -- Ancla: Comienza con Laptops para Juegos
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS level_up
    FROM
        category
    WHERE
        category_name = 'Laptops para Juegos'
    
    UNION ALL
    
    -- Recursivo: Obtiene las categorías padre
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ca.level_up + 1
    FROM
        category c
    INNER JOIN
        category_ancestors ca ON c.category_id = ca.parent_category_id
)
SELECT
    category_id,
    category_name,
    level_up
FROM
    category_ancestors
ORDER BY
    level_up;

Resultado:

category_id | category_name   | level_up
------------|-----------------|----------
6           | Laptops para Juegos| 0
4           | Laptops         | 1
2           | Computadoras     | 2
1           | Electrónica     | 3

Ejemplo de Lista de Materiales

Un caso de uso clásico para las CTEs recursivas es explorar listas de materiales (partes y subpartes):

-- Tabla de partes de ejemplo
CREATE TABLE parts (
    part_id INT PRIMARY KEY,
    part_name VARCHAR(100),
    quantity INT
);

-- Tabla de lista de materiales de ejemplo
CREATE TABLE bom (
    parent_part_id INT,
    child_part_id INT,
    quantity_needed INT,
    PRIMARY KEY (parent_part_id, child_part_id)
);

-- Datos de ejemplo
INSERT INTO parts VALUES
    (1, 'Bicicleta', 1),
    (2, 'Marco', 1),
    (3, 'Rueda', 2),
    (4, 'Neumático', 1),
    (5, 'Aro', 1),
    (6, 'Habillado', 36);

INSERT INTO bom VALUES
    (1, 2, 1),  -- La bicicleta necesita 1 marco
    (1, 3, 2),  -- La bicicleta necesita 2 ruedas
    (3, 4, 1),  -- La rueda necesita 1 neumático
    (3, 5, 1),  -- La rueda necesita 1 aro
    (5, 6, 36); -- El aro necesita 36 habillados

Calculando Total de Partes Necesarias

Para encontrar todas las partes necesarias para construir una bicicleta:

WITH RECURSIVE parts_explosion AS (
    -- Ancla: Comienza con el producto de nivel superior
    SELECT
        p.part_id,
        p.part_name,
        1 AS quantity,
        0 AS level,
        CAST(p.part_name AS VARCHAR(1000)) AS path
    FROM
        parts p
    WHERE
        p.part_name = 'Bicicleta'
    
    UNION ALL
    
    -- Recursivo: Explota la BOM
    SELECT
        p.part_id,
        p.part_name,
        pe.quantity * b.quantity_needed,
        pe.level + 1,
        CONCAT(pe.path, ' > ', p.part_name)
    FROM
        parts_explosion pe
    INNER JOIN
        bom b ON pe.part_id = b.parent_part_id
    INNER JOIN
        parts p ON b.child_part_id = p.part_id
)
SELECT
    part_id,
    REPEAT('  ', level) || part_name AS part_hierarchy,
    quantity,
    level,
    path
FROM
    parts_explosion
ORDER BY
    path;

Resultado:

part_id | part_hierarchy | quantity | level | path
--------|----------------|----------|-------|--------------------------------
1       | Bicicleta      | 1        | 0     | Bicicleta
2       |   Marco        | 1        | 1     | Bicicleta > Marco
3       |   Rueda        | 2        | 1     | Bicicleta > Rueda
4       |     Neumático  | 2        | 2     | Bicicleta > Rueda > Neumático
5       |     Aro        | 2        | 2     | Bicicleta > Rueda > Aro
6       |       Habillado| 72       | 3     | Bicicleta > Rueda > Aro > Habillado

Observa que necesitamos un total de 72 habillados: 2 ruedas × 1 aro por rueda × 36 habillados por aro = 72 habillados.

Previniendo Bucles Infinito

Las CTEs recursivas pueden crear bucles infinitos si hay referencias circulares en tus datos. Aquí hay estrategias para prevenir esto:

1. Limitar la Profundidad Máxima

WITH RECURSIVE limited_recursion AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth
    FROM
        category
    WHERE
        parent_category_id IS NULL
    
    UNION ALL
    
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        lr.depth + 1
    FROM
        category c
    INNER JOIN
        limited_recursion lr ON c.parent_category_id = lr.category_id
    WHERE
        lr.depth < 10  -- Límite de profundidad máxima
)
SELECT * FROM limited_recursion;

2. Rastrear Nodos Visitados

WITH RECURSIVE safe_traversal AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        ARRAY[category_id] AS visited_ids
    FROM
        category
    WHERE
        parent_category_id IS NULL
    
    UNION ALL
    
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        st.visited_ids || c.category_id
    FROM
        category c
    INNER JOIN
        safe_traversal st ON c.parent_category_id = st.category_id
    WHERE
        NOT (c.category_id = ANY(st.visited_ids))  -- Prevenir ciclos
)
SELECT * FROM safe_traversal;

Consideraciones de Rendimiento

1. Indexar Columnas Padre-Hijo

Siempre indexa las columnas utilizadas en uniones recursivas:

CREATE INDEX idx_employee_manager ON employee(manager_id);
CREATE INDEX idx_category_parent ON category(parent_category_id);

2. Limitar Conjuntos de Resultados

Usa cláusulas WHERE para limitar el alcance de la recursión:

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee
    WHERE employee_name = 'Bob Smith'
    
    UNION ALL
    
    SELECT e.employee_id, e.employee_name, e.manager_id, s.level + 1
    FROM employee e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
    WHERE s.level < 3  -- Solo ir 3 niveles de profundidad
)
SELECT * FROM subordinates;

3. Usar Tipos de Unión Apropiados

Usa INNER JOIN cuando solo quieras filas coincidentes
Usa LEFT JOIN cuando quieras incluir nodos hoja sin hijos

Caso de Uso Práctico: Sistema de Hilos/Comentarios

Un patrón común en aplicaciones web son los comentarios anidados o hilos de foros:

CREATE TABLE comments (
    comment_id INT PRIMARY KEY,
    parent_comment_id INT,
    user_name VARCHAR(100),
    comment_text TEXT,
    created_at TIMESTAMP
);

WITH RECURSIVE comment_thread AS (
    -- Ancla: Comentarios de nivel superior
    SELECT
        comment_id,
        parent_comment_id,
        user_name,
        comment_text,
        0 AS depth,
        CAST(comment_id AS VARCHAR(1000)) AS sort_path
    FROM
        comments
    WHERE
        parent_comment_id IS NULL
    
    UNION ALL
    
    -- Recursivo: Respuestas anidadas
    SELECT
        c.comment_id,
        c.parent_comment_id,
        c.user_name,
        c.comment_text,
        ct.depth + 1,
        CONCAT(ct.sort_path, '-', LPAD(c.comment_id::TEXT, 10, '0'))
    FROM
        comments c
    INNER JOIN
        comment_thread ct ON c.parent_comment_id = ct.comment_id
)
SELECT
    REPEAT('  ', depth) || user_name AS indented_user,
    comment_text,
    depth
FROM
    comment_thread
ORDER BY
    sort_path;

Conclusiones Clave

CTEs Recursivas permiten recorrer datos jerárquicos con relaciones de padre-hijo
La sintaxis WITH RECURSIVE incluye un miembro ancla y un miembro recursivo
El miembro ancla define el punto de partida (caso base)
El miembro recursivo referencia la CTE misma y procesa cada iteración
La terminación ocurre cuando el miembro recursivo no devuelve filas
Casos de uso comunes: Organigramas, árboles de categorías, listas de materiales, sistemas de archivos
Seguimiento de niveles: Incluye una columna de profundidad/nivel para entender la posición en la jerarquía
Construcción de rutas: Concatenar rutas para mostrar la línea completa
Prevención de bucles infinitos: Usar límites de profundidad o rastrear nodos visitados
Rendimiento: Indexar columnas padre y limitar la profundidad de la recursión cuando sea posible
Versátil: Funciona con cualquier estructura de tabla auto-referenciada

Las CTEs recursivas son una herramienta esencial para trabajar con datos estructurados en forma de árbol y jerárquicos en SQL. Transforman operaciones complejas de múltiples consultas en soluciones elegantes de una sola consulta.

En el próximo módulo, exploraremos Funciones de Ventana para análisis de datos avanzados.

Práctica

Lecciones

Entorno de Pruebas

Prueba

Lección 6.5: CTEs Recursivas para Datos Jerárquicos

¿Qué Son las CTEs Recursivas?

Sintaxis de CTE Recursiva

Cómo Funcionan las CTEs Recursivas

Ejemplo Básico: Secuencia de Números

Ejemplo de Jerarquía de Empleados

Encontrando Todos los Subordinados

Construyendo el Organigrama Completo

Ejemplo de Árbol de Categorías

Encontrando Todas las Subcategorías

Encontrando Ancestros

Ejemplo de Lista de Materiales

Calculando Total de Partes Necesarias

Previniendo Bucles Infinito

1. Limitar la Profundidad Máxima

2. Rastrear Nodos Visitados

Consideraciones de Rendimiento

1. Indexar Columnas Padre-Hijo

2. Limitar Conjuntos de Resultados

3. Usar Tipos de Unión Apropiados

Caso de Uso Práctico: Sistema de Hilos/Comentarios

Conclusiones Clave

Iniciar sesión

Registrarse

Lección 6.5: CTEs Recursivas para Datos Jerárquicos

¿Qué Son las CTEs Recursivas?

Sintaxis de CTE Recursiva

Cómo Funcionan las CTEs Recursivas

Ejemplo Básico: Secuencia de Números

Ejemplo de Jerarquía de Empleados

Encontrando Todos los Subordinados

Construyendo el Organigrama Completo

Ejemplo de Árbol de Categorías

Encontrando Todas las Subcategorías

Encontrando Ancestros

Ejemplo de Lista de Materiales

Calculando Total de Partes Necesarias

Previniendo Bucles Infinito

1. Limitar la Profundidad Máxima

2. Rastrear Nodos Visitados

Consideraciones de Rendimiento

1. Indexar Columnas Padre-Hijo

2. Limitar Conjuntos de Resultados

3. Usar Tipos de Unión Apropiados

Caso de Uso Práctico: Sistema de Hilos/Comentarios

Conclusiones Clave