¿Qué es una CTE recursiva en SQL?

El artículo que le mostrará ejemplos prácticos del uso de CTEs recursivas en SQL.

Si has oído hablar de las CTEs recursivas de SQL pero nunca las has utilizado, este artículo es para ti. También es para ti si nunca te cansas de los ejemplos de CTE recursivas.

Antes de profundizar en la recursión, te recordaré qué son las CTEs y cuál es su sintaxis. Luego haré lo mismo con las CTEs recursivas. Después, te mostraré cómo funcionan las CTEs recursivas en tres ejemplos.

¿Qué son las CTEs?

La CTE (expresión de tabla común), también conocida como cláusula WITH, es una función de SQL que devuelve un conjunto de datos temporal que puede ser utilizado por otra consulta. Como es un resultado temporal, no se almacena en ningún sitio, pero se puede referenciar como se haría con cualquier otra tabla.

Hay dos tipos de CTEs, los no recursivos y los recursivos.

Aquí hay un buen artículo que te mostrará qué son las CTEs y cómo funcionan.

Sintaxis de las CTEs no recursivas

La sintaxis general de una CTE no recursiva tiene el siguiente aspecto:

WITH cte_name AS (cte_query_definition)

SELECT *
FROM   cte_name;

La primera parte de la sintaxis es el CTE. Comienza con la palabra clave WITH. Luego se le da un nombre al CTE. Después de la palabra clave AS, puede definir el CTE en los paréntesis.

La segunda parte de la sintaxis es una simple sentencia SELECT. Se escribe inmediatamente después de la CTE recursiva, sin ninguna coma, punto y coma o marcas similares. Como dije antes, el CTE se utiliza en otra consulta como cualquier otra tabla. Esto es exactamente lo que hace la sentencia SELECT.

Aquí está el artículo que puede ayudarte adicionalmente con la sintaxis CTE y sus reglas. Y si necesitas más ejemplos de CTE, este artículo es para ti.

Sintaxis CTE Recursiva

Una CTE recursiva hace referencia a sí misma. Devuelve el subconjunto de resultados, luego se referencia a sí misma repetidamente (recursivamente), y se detiene cuando devuelve todos los resultados.

La sintaxis de un CTE recursivo no es muy diferente de la de un CTE no recursivo:

WITH RECURSIVE cte_name AS (
    cte_query_definition (the anchor member)

    UNION ALL

    cte_query_definition (the recursive member)
)


SELECT *
FROM   cte_name;

De nuevo, al principio de tu CTE está la cláusula WITH. Sin embargo, si quieres que tu CTE sea recursiva, entonces después de WITH escribes la palabra clave RECURSIVE. A continuación, todo sigue como de costumbre: a AS le siguen los paréntesis con la definición de la consulta CTE. Esta primera definición de consulta se llama miembro ancla.

Para conectar el miembro ancla con el miembro recursivo, es necesario utilizar el comando UNION o UNION ALL. El miembro recursivo es, obviamente, la parte recursiva del CTE que hará referencia al propio CTE. Verás cómo funciona en un ejemplo muy pronto.

Los CTEs recursivos se utilizan principalmente cuando se desea consultar datos jerárquicos o gráficos. Puede tratarse de la estructura organizativa de una empresa, un árbol genealógico, el menú de un restaurante o varias rutas entre ciudades. Consulte estos artículos para entender cómo funcionan los CTEs con estructuras jer árquicas y cómo consultar datos de gráficos.

Ahora que entendemos cómo funcionan las CTEs recursivas, veamos algunos ejemplos.

Ejemplo 1 - Encontrar jefes y nivel jerárquico para todos los empleados

Para este problema, utilizaré los datos de la tabla employeesque tiene las siguientes columnas:

• id: El ID del empleado.
• first_name: El nombre del empleado.
• last_name: Los apellidos del empleado.
• boss_id: La identificación del jefe del empleado.

Este es el aspecto de los datos:

No es demasiado complicado. Por ejemplo, el jefe de Domenic Leaver es el empleado con el ID de 5; es decir, Hermie Comsty. El mismo principio funciona para todos los demás empleados, excepto Roxanna Fairlie. Ella no tiene jefe; hay un valor NULL en la columna boss_id. Podemos concluir que Roxanna es la presidenta o propietaria de la empresa.

Ahora escribamos el CTE recursivo para listar todos los empleados y sus jefes directos.

WITH RECURSIVE company_hierarchy AS (
  SELECT	id,
    		first_name,
    		last_name,
    		boss_id,
		0 AS hierarchy_level
  FROM employees
  WHERE boss_id IS NULL

  UNION ALL 
  
  SELECT	e.id,
    		e.first_name,
    		e.last_name,
    		e.boss_id, 
		hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.boss_id = ch.id
)

SELECT   ch.first_name AS employee_first_name,
	   ch.last_name AS employee_last_name,
	   e.first_name AS boss_first_name,
	   e.last_name AS boss_last_name,
	   hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e
ON ch.boss_id = e.id
ORDER BY ch.hierarchy_level, ch.boss_id;

¿Qué hace esta consulta? Es una consulta recursiva, por lo que comienza con WITH RECURSIVE. El nombre del CTE es company_hierarchy. Después de AS, la definición del CTE está entre paréntesis.

La primera sentencia SELECT selecciona todas las employee tabla donde la columna boss_id es NULL. En resumen, seleccionará a Roxanna Fairlie, porque sólo ella tiene un valor NULL en esa columna. Aún más corto: estoy comenzando la recursión desde la parte superior de la estructura organizativa. También hay una columna hierarchy_level con el valor 0. Eso significa que el nivel del propietario/presidente es 0 - están en la cima de la jerarquía.

He utilizado el UNION ALL para conectar esta sentencia SELECT con la segunda, es decir, con el miembro recursivo. En el miembro recursivo, estoy seleccionando todas las columnas de la tabla employees y el CTE company_hierarchy donde la columna boss_id es igual a la columna id. Fíjate en la parte hierarchy_level + 1. Esto significa que con cada recursión, el CTE sumará 1 al nivel jerárquico anterior, y lo hará hasta llegar al final de la jerarquía. Observe también que estoy tratando esta CTE como cualquier otra tabla. Para terminar de definir el CTE, simplemente cierre los paréntesis.

Finalmente, hay una tercera sentencia SELECT, fuera del CTE. Selecciona las columnas que mostrarán los empleados, los nombres de sus jefes y el nivel jerárquico. Los datos se toman del CTE y de la tabla employees. He unido esos dos con un LEFT JOIN, ya que quiero todos los datos del CTE - incluyendo a Roxanna Fairlie, que tiene el valor NULL en la columna boss_id. El resultado se mostrará en orden ascendente: primero por el nivel de jerarquía, luego por el ID del jefe. Así es como se ve:

Roxanna Fairlie es la jefa final; eso ya lo sabías. Hay dos empleados en el nivel 1. Esto significa que Bobbe Blakeway y Hermie Comsty son los subordinados directos de Roxanna Fairlie. En el nivel 2, hay empleados cuyos jefes directos son Bobbe Blakeway y Hermie Comsty. También hay un tercer nivel en la jerarquía. Se trata de empleados cuyo jefe directo es Domenic Leaver.

Ejemplo 2 - Hallar el importe de la inversión por inversor

En este ejemplo, utilizaré la tabla investment:

• id: El ID de la inversión.
• investment_amount: El importe de la inversión.

Los datos de la tabla tienen este aspecto:

Estos son los importes de las tres posibles opciones de inversión. Serán considerados por los tres inversores, que dividirán el importe total de la inversión en partes iguales. Su tarea consiste en calcular el importe por inversor en función de su número, es decir, si invierten uno, dos, tres o ningún inversor en cada inversión.

La consulta que resuelve este problema es:

WITH RECURSIVE per_investor_amount AS (
	SELECT	0 AS investors_number,
			0.00 AS investment_amount,
			0.00 AS individual_amount
	UNION 

	SELECT	investors_number + 1,
			i.investment_amount,
			i.investment_amount / (investors_number + 1)
	FROM investment i, per_investor_amount pia
	WHERE investors_number << 3
)

SELECT *
FROM per_investor_amount
ORDER BY  investment_amount, investors_number;

Una vez más, el CTE comienza con WITH RECURSIVE, seguido de su nombre y la definición de la consulta. Esta vez, utilizaré el miembro ancla de la consulta recursiva para crear algunos datos. Las columnas son investors_number, investment_amount, y individual_amount. Este es el punto desde el que quiero que empiece la recursión (igual que en el ejemplo anterior, con hierarchy_level = 0).

Luego viene el UNION y el miembro recursivo. Esta parte de la consulta incrementará la columna investors_number en uno con cada recursión. Lo hará para cada investment_amount. La tercera columna calculará el importe de esa inversión por inversor, en función del número de inversores que participen. La recursión se realizará para un máximo de tres inversores (es decir, hasta alcanzar la condición WHERE investors_number < 3).

Después viene la simple sentencia SELECT que devolverá todas las columnas del CTE. Y aquí está el resultado:

No es difícil de analizar. Si no hay inversores, el importe de la inversión es cero, y también el importe individual. Si la inversión es de 5.322.146,00 y sólo hay un inversor, el importe por inversor será de 5.322.146,00. Si hay dos inversores con el mismo importe, entonces cada uno de ellos tendrá que pagar 2.661.073,00. Si los tres inversores deciden invertir, cada uno pagará 1.774.048,67. Las otras dos cantidades de inversión siguen el mismo patrón, como se puede ver en la tabla.

Ejemplo 3 - Encontrar rutas entre ciudades

En el tercer ejemplo, utilizaré la tabla cities_routeque contiene datos sobre las ciudades holandesas:

• city_from: La ciudad de salida.
• city_to: La ciudad de destino.
• distance: La distancia entre dos ciudades, en kilómetros.

Utilice esta tabla para encontrar todas las rutas posibles de Groningen a Haarlem, mostrando las ciudades en la ruta y la distancia total.

Esta es la consulta para resolver este problema:

WITH RECURSIVE possible_route AS (
	SELECT	cr.city_to,
       		cr.city_from || '->' ||cr.city_to AS route,
       		cr.distance
      FROM cities_route cr
      WHERE cr.city_from = 'Groningen'

UNION ALL

SELECT 	cr.city_to,
       		pr.route || '->' || cr.city_to AS route,
        		CAST((pr.distance + cr.distance) AS DECIMAL(10, 2))
      FROM possible_route pr
INNER JOIN cities_route cr
      		ON cr.city_from = pr.city_to
)

SELECT 	pr.route,
		pr.distance
FROM possible_route pr
WHERE pr.city_to = 'Haarlem'
ORDER BY pr.distance;

Veamos qué hace esta consulta. La primera sentencia SELECT de la definición del CTE seleccionará las columnas de la tabla cities_route donde la ciudad de salida es Groningen. Observa que también hay una nueva columna llamada ruta, que utilizaré para concatenar las ciudades de la ruta.

El UNION ALL conecta esto con el miembro recursivo. Esta sentencia SELECT seleccionará la ciudad de llegada, concatenará las ciudades de la ruta y, finalmente, sumará las distancias entre estas ciudades al total de la ruta entre Groningen y Haarlem. Para conseguir todo eso, he unido el CTE con la tabla cities_route.

Luego viene la sentencia SELECT que extrae los datos del CTE. Seleccionará la ruta y la distancia cuando la ciudad de llegada sea Haarlem, con los datos ordenados por distancia en orden ascendente.

El resultado de la consulta tiene el siguiente aspecto:

No es difícil entender esta tabla. Hay dos rutas de Groningen a Haarlem. Incluyen diferentes ciudades entre ellas y tienen una longitud de 222,6 km y 246,5 km, respectivamente.

Si quieres seguir aprendiendo, comprueba cómo puedes utilizar una CTE recursiva en lugar de una consulta SQL larga. Y después de abordar ese tema, diviértete un poco dibujando algo utilizando una CTE recursiva.

Seguir practicando las CTEs Recursivas

Estos tres ejemplos han demostrado las posibilidades de las CTEs recursivas en SQL. Ahora es el momento de ampliar lo que has aprendido aquí.

Probablemente, la mejor opción es consultar nuestro curso Consultas recursivas y expresiones de tabla comunes . Te ofrece un montón de ejemplos, explicaciones y oportunidades de práctica. El curso forma parte de la pista del curso SQL avanzado , donde puedes aprender sobre otros temas avanzados de SQL como las funciones de ventana, las extensiones GROUP BY y las consultas recursivas.

¡Diviértete!