¡Hola, chicos!
¿Cómo estás?

En este artículo me gustaría demostrarle cómo crear consultas recursivas con CTE (Expresiones de tabla comunes) en SQL Server. La función CTE ha estado disponible desde la versión 2005 y, hasta el día de hoy, muchas personas no conocen esta función interesante y útil en su vida cotidiana.

Introducción

Un CTE es muy similar en uso a una subconsulta o tabla derivada, con la ventaja de que el conjunto de datos se puede utilizar más de una vez en la consulta, ganando rendimiento (en esta situación) y también mejorando la legibilidad del código. Por estos motivos, se ha generalizado el uso de CTE como sustituto de otras soluciones mencionadas.

Ejemplo de consulta con CTE:

;WITH CTE1
AS (
    SELECT
        Id_Bairro,
        Nm_Cliente,
        COUNT(Id_Pedido) AS Qt_Pedidos
    FROM
        Clientes
        LEFT JOIN Pedidos ON Clientes.Id_Cliente = Pedidos.Id_Pedido
    GROUP BY
        Id_Bairro,
        Nm_Cliente 
),
CTE2 AS ( 
    SELECT
        Nm_Bairro,
        COUNT(Nm_Cliente) AS Qt_Clientes,
        SUM(Qt_Pedidos) AS Qt_Pedidos
    FROM
        Bairro
        LEFT JOIN CTE1 ON Bairro.Id_Bairro = CTE1.Id_Bairro
    GROUP BY
        Nm_Bairro 
)
SELECT
    Nm_Bairro,
    Qt_Clientes,
    Qt_Pedidos,
    CAST(Qt_Pedidos AS NUMERIC(15, 2)) / Qt_Clientes AS Media
FROM
    CTE2;

Ejemplo de consulta con Subconsulta:

SELECT
    Nm_Bairro,
    Qt_Clientes,
    Qt_Pedidos,
    CAST(Qt_Pedidos AS NUMERIC(15, 2)) / Qt_Clientes AS Media
FROM
(
    SELECT
        Nm_Bairro,
        COUNT(Nm_Cliente) AS Qt_Clientes,
        SUM(Qt_Pedidos) AS Qt_Pedidos
    FROM
        Bairro
        LEFT JOIN
        (
            SELECT
                Id_Bairro,
                Nm_Cliente,
                COUNT(Id_Pedido) AS Qt_Pedidos
            FROM
                Clientes
                LEFT JOIN Pedidos ON Clientes.Id_Cliente = Pedidos.Id_Pedido
            GROUP BY
                Id_Bairro,
                Nm_Cliente
        ) AS Q1 ON Bairro.Id_Bairro = Q1.Id_Bairro
    GROUP BY
        Nm_Bairro
) AS Q2

Además, un CTE proporciona la importante ventaja de poder autorreferenciarse, creando así un CTE recursivo, que se ejecuta repetidamente para devolver subconjuntos de datos hasta que se obtiene el resultado completo.

El CTE recursivo se usa ampliamente para devolver datos jerárquicos, por ejemplo, mostrar empleados en un organigrama, que puede tener varios niveles jerárquicos y demostraré aquí un ejemplo de cómo funciona el CTE recursivo.

Ejemplos de CTE recursivo

Ejemplo de CTE recursivo usando una secuencia de números:

WITH CTE_Numerico (Nivel, Numero) 
AS
(
    -- Âncora (nível 1)
    SELECT 1 AS Nivel, 1 AS Numero
    
    UNION ALL

    -- Níveis recursivos (Níveis N)
    SELECT Nivel + 1, Numero + Numero 
    FROM CTE_Numerico
    WHERE Numero < 2048
 )
SELECT *
FROM CTE_Numerico

Resultado:

Ejemplo de CTE recursivo usando jerarquía:

Creación de la masa de prueba – Empleados de la empresa CLR Corporation

IF (OBJECT_ID('tempdb..#Funcionarios') IS NOT NULL) DROP TABLE #Funcionarios
CREATE TABLE #Funcionarios (
    Id_Empregado INT IDENTITY(1, 1) NOT NULL,
    Nm_Empregado VARCHAR(60) NOT NULL,
    Id_Superior INT NULL
)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Edvaldo Neves', NULL), ('Fabricio Amante', 1), ('Caio Lima', 1), ('Tiago Castro', 2)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Reginaldo Oliveira', 3), ('Fábio Merazzi', 3), ('Dirceu Resende', 2), ('Luiz Vitor Neves', 2)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Vithor Lima', 1), ('Edimar Lellis', 9), ('Lucas Fardim', 9), ('Aquila Loureiro', 9)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES ('Rodrigo Almeida', 9), ('Flávio Castro', NULL), ('Raul Farias', 14), ('Logan Castro', 1)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES('Fábio Amante', 3), ('Ariel Neves', 3), ('Leandro Galon', 7), ('Lucas Keller', 7)

INSERT INTO #Funcionarios(Nm_Empregado, Id_Superior)
VALUES('Richardson Folha', 19), ('Rafaela Giugliet', 20)

SELECT * FROM #Funcionarios

Estructura de la empresa ficticia

Dada la estructura anterior, quiero crear los niveles jerárquicos de estos empleados. ¿Cómo haríamos esto sin usar CTE?

Ejemplo con jerarquía manual

Una forma muy común de crear una jerarquía de datos en SQL Server para algunos niveles es mediante inserciones manuales.

Aunque es muy simple (básicamente, Ctrl+C y Ctrl+V), cuando necesitamos automatizar este trabajo o tenemos muchos niveles en nuestra jerarquía (que incluso puede ser variable), esta solución termina no sirviéndonos muy bien, llevándonos a buscar una solución más robusta.

Ejemplo de creación de una jerarquía manual:

IF (OBJECT_ID('tempdb..#Hiearquia') IS NOT NULL) DROP TABLE #Hiearquia
CREATE TABLE [#Hiearquia]
(
    [Id_Empregado] INT NOT NULL,
    [Nm_Empregado] VARCHAR(60) NOT NULL,
    [Id_Superior] INT,
    [Nivel] INT
)

-- Nível 1
INSERT INTO #Hiearquia
SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
FROM #Funcionarios
WHERE Id_Superior IS NULL

-- Nível 2
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 2 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

-- Nível 3
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 3 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

-- Nível 4
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 4 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

-- Nível 5
INSERT INTO #Hiearquia
SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, 5 AS Nivel
FROM #Funcionarios A
JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)


SELECT * FROM #Hiearquia

Resultado:

Ejemplo con LOOP MIENTRAS

Con el objetivo de crear uno más robusto que el anterior y que funcione con N niveles de jerarquía, podemos usar un LOOP WHILE para esto, que creará una iteración en cada nivel comenzando desde el ancla (primer nivel) hasta que el inserto no encuentre más registros para insertar.

Ejemplo de inserción usando LOOP:

IF (OBJECT_ID('tempdb..#Hiearquia') IS NOT NULL) DROP TABLE #Hiearquia
CREATE TABLE [#Hiearquia]
(
    [Id_Empregado] INT NOT NULL,
    [Nm_Empregado] VARCHAR(60) NOT NULL,
    [Id_Superior] INT,
    [Nivel] INT
)

-- Nível 1
INSERT INTO #Hiearquia
SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
FROM #Funcionarios
WHERE Id_Superior IS NULL


DECLARE @Nivel INT = 2

WHILE(1=1)
BEGIN

    INSERT INTO #Hiearquia
    SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, @Nivel AS Nivel
    FROM #Funcionarios A
    JOIN #Hiearquia B ON A.Id_Superior = B.Id_Empregado
    WHERE NOT EXISTS(SELECT NULL FROM #Hiearquia WHERE Id_Empregado = A.Id_Empregado)

    IF (@@ROWCOUNT <= 0) BREAK

    SET @Nivel += 1

END

SELECT * FROM #Hiearquia

Resultado:

Como puedes ver, utilicé un bucle while para consultar la tabla misma y así encadenar los datos y crear la jerarquía.

Pero ¿qué pasa con el CTE recursivo? ¿Podría devolver el mismo resultado con solo 1 SELECT?
R: ¡Por supuesto! Con esta función, podemos crear una consulta que devolverá el resultado esperado con una sola consulta.

Ejemplo con CTE recursivo

;WITH CTE_Recursivo AS (
    -- Nível 1 (Âncora)
    SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
    FROM #Funcionarios
    WHERE Id_Superior IS NULL

    UNION ALL

    -- Níveis 2-N
    SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, B.Nivel + 1 AS Nivel
    FROM #Funcionarios A
    JOIN CTE_Recursivo B ON A.Id_Superior = B.Id_Empregado
)
SELECT *
FROM CTE_Recursivo

Resultado:

Controlando el grado de recursividad con MAXRECURSION

Ahora que ha visto cómo utilizar el CTE recursivo y lo práctico que puede ser, es hora de comprender el riesgo de crear un CTE recursivo en un "bucle infinito". Esto sucede cuando no programas bien la recursividad de tu consulta y puede terminar alcanzando N niveles e incluso colapsar tu instancia y consumir tantos recursos al intentar resolver la consulta.

Para evitar este tipo de situaciones, existe la sugerencia de consulta MAXRECURSION, que le permite especificar el nivel máximo de recursividad. Al alcanzar este nivel, SQL abortará el resto de la ejecución y mostrará un mensaje de error con gravedad 16, como se muestra a continuación:

;WITH CTE_Recursivo AS (
    -- Nível 1 (Âncora)
    SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
    FROM Dacasa.dbo.Funcionarios_Teste
    WHERE Id_Superior IS NULL

    UNION ALL

    -- Níveis 2-N
    SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, B.Nivel + 1 AS Nivel
    FROM Dacasa.dbo.Funcionarios_Teste A
    JOIN CTE_Recursivo B ON A.Id_Superior = B.Id_Empregado
)
SELECT *
FROM CTE_Recursivo
OPTION(MAXRECURSION 1)

Mensaje de error:

Mensaje 530, Nivel 16, Estado 1, Línea 1
Se da por terminada la declaración. La recursividad máxima 1 se ha agotado antes de que se complete la declaración.

Resultado:

Tenga en cuenta que al utilizar la sugerencia OPCIÓN (MAXRECURSION 1), solo se devolvió el nivel primario y 1 nivel de jerarquía más (niveles 1 y 2). Si usáramos el valor MAXRECURSION 2, se devolvería el nivel primario y 2 niveles de jerarquía más (nivel 1 al nivel 3).

Como la excepción generada por la sugerencia MAXRECURSION es de gravedad 16, provoca una interrupción en el resto de la rutina y si es un trabajo, este trabajo devolverá un error. Si no desea este comportamiento y solo desea limitar el nivel de recursividad, pero sin causar un error en la rutina, simplemente encapsule su CTE en un bloque TRY..CATCH:

BEGIN TRY

    ;WITH CTE_Recursivo AS (
        -- Nível 1 (Âncora)
        SELECT Id_Empregado, Nm_Empregado, Id_Superior, 1 AS Nivel
        FROM Dacasa.dbo.Funcionarios_Teste
        WHERE Id_Superior IS NULL

        UNION ALL

        -- Níveis 2-N
        SELECT A.Id_Empregado, A.Nm_Empregado, A.Id_Superior, B.Nivel + 1 AS Nivel
        FROM Dacasa.dbo.Funcionarios_Teste A
        JOIN CTE_Recursivo B ON A.Id_Superior = B.Id_Empregado
    )
    SELECT *
    FROM CTE_Recursivo
    OPTION(MAXRECURSION 1)

END TRY
BEGIN CATCH
END CATCH

Nota: El parámetro de sugerencia MAXRECURSION admite valores del 1 al 32 767. Si desea utilizar un nivel de recursividad superior a este valor, simplemente no utilice esta sugerencia ni especifique el valor 0, lo que significa que no hay límite de recursividad.

Otra forma de demostrar un uso muy interesante del CTE recursivo es generar secuencias de números:

Ejemplo de CTE recursivo para generar una secuencia de 200 números:

;WITH CTE_Numerico AS
(
    SELECT 1 AS Numero
    UNION ALL
    SELECT Numero + 1 FROM CTE_Numerico
 )
SELECT * FROM CTE_Numerico
OPTION (MAXRECURSION 199)

Resultado:

Usando este código anterior, fue posible limitar 200 registros usando la sugerencia MAXRECURSION(199), limitando 199 niveles de recursividad. Sin embargo, es posible garantizar esta limitación de otra forma (y sin generar una excepción):

WITH CTE_Numerico AS
(
    SELECT 1 AS Numero
    UNION ALL
    SELECT Numero + 1 FROM CTE_Numerico
    WHERE Numero < 200
 )
SELECT * FROM CTE_Numerico
OPTION (MAXRECURSION 249)

Resultado:

Entonces observe que, aunque especificamos que el nivel de recursividad es 249, es decir, el nivel máximo de recursividad es 250 registros (y de hecho, se deberían haber generado 250 registros), como limité la cantidad a 200 registros dentro de la consulta CTE, fue posible devolver solo 200 registros, haciendo que la sugerencia MAXRECURSION sea innecesaria en este caso.

Con esto se pudo demostrar que es posible limitar los registros de un CTE recursivo (en ciertas situaciones) incluso sin utilizar la sugerencia MAXRECURSION.

Conclusión

En este artículo pude demostrar qué es un CTE, en qué situaciones podemos usarlo, cómo está construido y algunas de las ventajas que tiene sobre otras soluciones, como tablas derivadas y subconsultas.

Además, se pudo explicar cómo funciona un CTE recursivo, cómo se puede montar y algunos ejemplos donde puede resultar especialmente útil.

¡Espero que hayas disfrutado este artículo y que te haya resultado útil!
Un abrazo y ¡hasta la próxima!