SQL Recursivo: Consultas con CTEs Recursivas para Jerarquías y Grafos 🌳

Las bases de datos relacionales son excelentes para organizar información, pero a menudo nos encontramos con estructuras de datos que van más allá de las relaciones simples de uno a muchos. Hablamos de jerarquías (como organigramas, categorías de productos anidadas, árboles de comentarios) y grafos (como redes sociales, rutas de transporte, dependencias de proyectos). Aquí es donde las CTEs Recursivas (Common Table Expressions Recursivas) brillan con luz propia. ✨

Este tutorial te sumergirá en el fascinante mundo del SQL recursivo, proporcionándote las herramientas para navegar, analizar y manipular este tipo de estructuras complejas con facilidad.

¿Qué es una CTE Recursiva? 🤔

Una CTE (Common Table Expression) es un conjunto de resultados temporal y nombrado que puedes referenciar dentro de una única sentencia SELECT, INSERT, UPDATE o DELETE. Las CTEs ayudan a mejorar la legibilidad y la organización de consultas complejas. Una CTE se vuelve recursiva cuando se refiere a sí misma dentro de su propia definición.

Imagina que quieres listar todos los empleados de una empresa, incluyendo sus subordinados directos, los subordinados de sus subordinados, y así sucesivamente, hasta el nivel más bajo. O quizás quieras encontrar todas las posibles rutas entre dos puntos en una red de transporte. Estos son los escenarios perfectos para las CTEs recursivas.

Anatomía de una CTE Recursiva 🧬

Una CTE recursiva se compone de dos partes fundamentales, unidas por el operador UNION ALL o UNION:

1. Miembro Ancla (Anchor Member): Es la consulta base que inicializa la recursión. Define el punto de partida o los elementos iniciales de la jerarquía o grafo. Esta parte no hace referencia a la CTE misma.
2. Miembro Recursivo (Recursive Member): Es la consulta que se refiere a la CTE para generar los siguientes niveles de la jerarquía o explorar las siguientes conexiones del grafo. Se ejecuta repetidamente hasta que no se encuentran más filas.

La sintaxis general es la siguiente:

WITH RECURSIVE nombre_cte (columna1, columna2, ...)
AS
(
    -- Miembro Ancla (Consulta inicial)
    SELECT columna1, columna2, ...
    FROM tabla_base
    WHERE condicion_inicial

    UNION ALL -- o UNION

    -- Miembro Recursivo (Consulta que se refiere a 'nombre_cte')
    SELECT t.columna1, t.columna2, ...
    FROM tabla_base t
    INNER JOIN nombre_cte cte ON t.condicion_union = cte.columna_recursiva
    WHERE condicion_recursiva
)
-- Consulta final que usa la CTE recursiva
SELECT * FROM nombre_cte;

Diferencia entre UNION ALL y UNION en CTEs Recursivas 🔄

• UNION ALL: Incluye todas las filas resultantes de ambas consultas, incluso si hay duplicados. Es más eficiente porque no necesita ordenar y eliminar duplicados.
• UNION: Elimina las filas duplicadas del conjunto de resultados final. Útil cuando no quieres procesar la misma entidad o ruta más de una vez, pero puede ser menos eficiente.

En la mayoría de los casos de recursión, UNION ALL es preferible a menos que necesites explícitamente eliminar duplicados, lo cual podría indicar un problema en el diseño de tu lógica recursiva o que estás explorando un grafo donde los ciclos deben ser manejados de forma específica.

Caso de Uso 1: Organigramas (Jerarquías Padre-Hijo) 👨‍👩‍👧‍👦

Un organigrama es un ejemplo clásico de una estructura jerárquica, donde un empleado reporta a un gerente, quien a su vez reporta a otro gerente, y así sucesivamente hasta la cima. Vamos a construir una tabla de ejemplo y una CTE recursiva para explorar esto.

Configuración de la Base de Datos 🛠️

Crearemos una tabla Empleados con la siguiente estructura:

CREATE TABLE Empleados (
    EmpleadoID INT PRIMARY KEY,
    Nombre VARCHAR(50),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Empleados(EmpleadoID)
);

INSERT INTO Empleados (EmpleadoID, Nombre, ManagerID) VALUES
(1, 'CEO Alejandro', NULL), -- El CEO no tiene ManagerID
(2, 'Gerente General Laura', 1),
(3, 'Gerente de Ventas Marcos', 2),
(4, 'Gerente de Marketing Sofía', 2),
(5, 'Representante Ventas 1 Pedro', 3),
(6, 'Representante Ventas 2 Ana', 3),
(7, 'Analista Marketing 1 Luis', 4),
(8, 'Analista Marketing 2 Marta', 4),
(9, 'Especialista Soporte Carlos', 2); -- Carlos reporta a Laura también

Consulta Recursiva para un Organigrama 📊

Queremos listar todos los empleados que reportan, directa o indirectamente, al 'Gerente General Laura' (EmpleadoID = 2), y también mostrar su nivel en la jerarquía.

WITH RECURSIVE Subordinados_Laura AS (
    -- Miembro Ancla: Seleccionar a Laura (el punto de partida)
    SELECT
        EmpleadoID,
        Nombre,
        ManagerID,
        1 AS Nivel_Jerarquia
    FROM Empleados
    WHERE EmpleadoID = 2 -- Laura es el punto de partida (ManagerID NULL es el CEO)

    UNION ALL

    -- Miembro Recursivo: Encontrar subordinados de los subordinados
    SELECT
        e.EmpleadoID,
        e.Nombre,
        e.ManagerID,
        sl.Nivel_Jerarquia + 1 AS Nivel_Jerarquia
    FROM Empleados e
    INNER JOIN Subordinados_Laura sl ON e.ManagerID = sl.EmpleadoID
)
SELECT
    EmpleadoID,
    Nombre,
    ManagerID,
    Nivel_Jerarquia
FROM Subordinados_Laura
ORDER BY Nivel_Jerarquia, Nombre;

Explicación:

1. Miembro Ancla: Selecciona al empleado con EmpleadoID = 2 (Laura) y le asigna un Nivel_Jerarquia de 1.
2. Miembro Recursivo: En cada iteración, une la tabla Empleados con la Subordinados_Laura (la CTE actual) basándose en e.ManagerID = sl.EmpleadoID. Esto significa: "Encuentra a los empleados cuyo ManagerID sea el EmpleadoID de alguien ya encontrado en Subordinados_Laura". El Nivel_Jerarquia se incrementa en 1 en cada paso.

Resultado esperado:

Caso de Uso 2: Nodos de Red y Rutas (Grafos) 🌐

Otro uso potente de las CTEs recursivas es el recorrido de grafos, como la búsqueda de rutas en una red de ciudades, dependencias de proyectos, o conexiones en una red social. Consideremos una tabla de vuelos entre ciudades.

Configuración de la Base de Datos ✈️

Crearemos una tabla Vuelos para representar las conexiones directas entre ciudades:

CREATE TABLE Vuelos (
    Origen VARCHAR(50),
    Destino VARCHAR(50),
    DuracionMinutos INT,
    PRIMARY KEY (Origen, Destino)
);

INSERT INTO Vuelos (Origen, Destino, DuracionMinutos) VALUES
('Madrid', 'Barcelona', 90),
('Barcelona', 'Palma', 45),
('Barcelona', 'París', 120),
('Madrid', 'Londres', 150),
('Londres', 'Nueva York', 480),
('París', 'Roma', 100),
('Roma', 'Berlín', 110),
('Berlín', 'Madrid', 180);

Consulta Recursiva para Encontrar Rutas 🗺️

Queremos encontrar todas las posibles rutas desde 'Madrid' a cualquier otra ciudad, incluyendo la secuencia de ciudades y la duración total. Para evitar ciclos infinitos, necesitamos mantener un historial de las ciudades visitadas en la ruta actual.

WITH RECURSIVE RutasAereas AS (
    -- Miembro Ancla: Vuelos directos desde Madrid
    SELECT
        Origen,
        Destino,
        DuracionMinutos AS DuracionTotal,
        CAST(Origen || ' -> ' || Destino AS VARCHAR(MAX)) AS Ruta,
        CAST(',' || Origen || ',' || Destino || ',' AS VARCHAR(MAX)) AS CiudadesVisitadas -- Para detectar ciclos
    FROM Vuelos
    WHERE Origen = 'Madrid'

    UNION ALL

    -- Miembro Recursivo: Encontrar conexiones a partir de rutas existentes
    SELECT
        ra.Origen,
        v.Destino,
        ra.DuracionTotal + v.DuracionMinutos AS DuracionTotal,
        CAST(ra.Ruta || ' -> ' || v.Destino AS VARCHAR(MAX)) AS Ruta,
        CAST(ra.CiudadesVisitadas || v.Destino || ',' AS VARCHAR(MAX)) AS CiudadesVisitadas
    FROM Vuelos v
    INNER JOIN RutasAereas ra ON v.Origen = ra.Destino
    WHERE ra.CiudadesVisitadas NOT LIKE '%,' || v.Destino || ',%' -- Evitar ciclos
)
SELECT
    Origen,
    Destino,
    DuracionTotal,
    Ruta
FROM RutasAereas
ORDER BY Origen, Destino, DuracionTotal;

Explicación:

1. Miembro Ancla: Selecciona todos los vuelos que parten de 'Madrid'. Inicializa Ruta y CiudadesVisitadas. CiudadesVisitadas es una cadena concatenada con delimitadores para facilitar la búsqueda de ciclos.
2. Miembro Recursivo: Une la tabla Vuelos con la RutasAereas (la CTE actual) donde el Origen del vuelo es el Destino de una ruta ya encontrada. La clave aquí es la condición ra.CiudadesVisitadas NOT LIKE '%,' || v.Destino || ',%', que previene que la recursión entre en un ciclo infinito al no visitar una ciudad que ya está en la ruta actual.

Resultado (ejemplo parcial):

Consideraciones y Mejores Prácticas 💡

Las CTEs recursivas son poderosas, pero como cualquier herramienta avanzada, requieren un uso cuidadoso:

• Rendimiento: Las CTEs recursivas pueden ser costosas en términos de rendimiento, especialmente en tablas grandes o con jerarquías muy profundas. Es crucial optimizar las condiciones de unión y filtrado.
• Terminación: Siempre asegúrate de tener una condición de terminación clara para evitar bucles infinitos. En jerarquías, suele ser cuando no hay más hijos. En grafos, detectando ciclos (como hicimos con CiudadesVisitadas).
• Profundidad Máxima: Algunos sistemas de gestión de bases de datos (SGBD) tienen un límite en la profundidad de recursión (por ejemplo, SQL Server tiene MAXRECURSION). Si tu jerarquía es extremadamente profunda, podrías necesitar ajustar este límite o considerar enfoques alternativos.
• Columnas en la CTE: Solo incluye las columnas necesarias en la CTE para evitar sobrecargar la memoria y el procesamiento.
• Depuración: Es útil seleccionar el Nivel_Jerarquia o una columna similar en tu CTE para entender cómo progresa la recursión y facilitar la depuración.

Ejemplos Adicionales Rápidos ✨

1. Generar una Serie de Fechas 🗓️

Quieres generar una lista de fechas para los próximos 7 días a partir de hoy.

WITH RECURSIVE FechasSerie AS (
    -- Miembro Ancla: Fecha inicial
    SELECT CAST(GETDATE() AS DATE) AS FechaGenerada

    UNION ALL

    -- Miembro Recursivo: Sumar un día
    SELECT DATEADD(day, 1, FechaGenerada)
    FROM FechasSerie
    WHERE DATEADD(day, 1, FechaGenerada) <= DATEADD(day, 7, CAST(GETDATE() AS DATE))
)
SELECT FechaGenerada FROM FechasSerie;

Nota: GETDATE() y DATEADD() son funciones específicas de SQL Server. En PostgreSQL sería CURRENT_DATE y FechaGenerada + INTERVAL '1 day'. En MySQL CURDATE() y DATE_ADD(FechaGenerada, INTERVAL 1 DAY).

2. Contar Descendientes en una Jerarquía de Categorías 📦

Imagina una tabla Categorias con CategoriaID, Nombre y CategoriaPadreID. Puedes usar una CTE recursiva para contar todos los subcategorías de una categoría específica.

CREATE TABLE Categorias (
    CategoriaID INT PRIMARY KEY,
    Nombre VARCHAR(50),
    CategoriaPadreID INT,
    FOREIGN KEY (CategoriaPadreID) REFERENCES Categorias(CategoriaID)
);

INSERT INTO Categorias (CategoriaID, Nombre, CategoriaPadreID) VALUES
(1, 'Electrónica', NULL),
(2, 'TV y Video', 1),
(3, 'Smartphones', 1),
(4, 'Accesorios', 3),
(5, 'Cargadores', 4),
(6, 'Auriculares', 4),
(7, 'Monitores', 2);

WITH RECURSIVE DescendientesCategoria AS (
    -- Miembro Ancla: La categoría inicial (por ejemplo, 'Electrónica')
    SELECT
        CategoriaID,
        Nombre,
        CategoriaPadreID
    FROM Categorias
    WHERE CategoriaID = 1 -- ID de Electrónica

    UNION ALL

    -- Miembro Recursivo: Buscar subcategorías
    SELECT
        c.CategoriaID,
        c.Nombre,
        c.CategoriaPadreID
    FROM Categorias c
    INNER JOIN DescendientesCategoria dc ON c.CategoriaPadreID = dc.CategoriaID
)
SELECT COUNT(*) - 1 AS NumeroDeSubcategorias -- Restar la categoría inicial
FROM DescendientesCategoria;

Este ejemplo nos diría cuántas subcategorías tiene 'Electrónica' (incluyendo sus sub-subcategorías, etc.). El -1 es porque la categoría inicial también se cuenta a sí misma en la CTE.

Conclusión ✅

Las CTEs recursivas son una herramienta increíblemente potente en tu arsenal de SQL. Te permiten abordar problemas complejos de datos, como la navegación de jerarquías y grafos, de una manera elegante y conforme al estándar ANSI SQL. Dominar esta técnica abrirá nuevas posibilidades para tus análisis y manipulaciones de datos.

Recuerda siempre prestar atención a la lógica de terminación para evitar bucles infinitos y considerar el impacto en el rendimiento en grandes conjuntos de datos. Con práctica y comprensión, el SQL recursivo se convertirá en uno de tus recursos más valiosos.

¡Anímate a experimentar con tus propias estructuras de datos jerárquicas y de grafos! La satisfacción de resolver un problema complejo con una consulta recursiva bien construida es inmensa. ¡Feliz codificación! 🚀