Cómo Escribir una CTE Recursiva en SQL Server

Una guía para entender y usar CTEs recursivas en SQL Server

SQL Server ofrece muchas herramientas potentes para trabajar con datos, incluidas las expresiones comunes de tabla (CTE). Una CTE es un conjunto de resultados temporal con nombre al que se puede hacer referencia dentro de una sentencia SELECT, INSERT, UPDATE o DELETE. Las CTE pueden dividir las consultas largas en partes lógicas más pequeñas y manejables, y hacerlas más legibles.

SQL Server ofrece expresiones de tabla comunes recursivas. Una expresión común de tabla (CTE) recursiva en SQL Server permite realizar consultas recursivas en estructuras de datos jerárquicas o basadas en gráficos, como organigramas, árboles genealógicos, redes de transporte, etc. Las consultas recursivas se utilizan para recorrer en bucle las relaciones entre los elementos de datos.

En este artículo, comenzaremos con la sintaxis CTE estándar y luego exploraremos cómo escribir CTE recursivas en SQL Server.

Una buena forma de profundizar en el conocimiento de las CTE recursivas es realizar nuestro curso sobre Consultas recursivas y expresiones de tabla comunes en MS SQL Server. Le enseñará a dominar el tipo de consulta más desafiante y a organizar eficazmente consultas SQL largas.

Fundamentos de las CTEs en SQL Server

Primero, revisemos los fundamentos de las CTEs. Una CTE comienza con la cláusula WITH, seguida por el nombre de la CTE y la sentencia SELECT que la define. La sintaxis de una CTE es la siguiente:

WITH cte_name AS (
   SELECT 
     column1,
     column2,
     …
   FROM table
   … 
) 
SELECT … 
FROM cte_name, …;

Empecemos por el principio. Lo primero que hacemos es darle un nombre a nuestra CTE, cte_name. Esta CTE selecciona algunas columnas (column1, column2, ...) de table. El resto de la consulta (la consulta externa) puede referirse al CTE y a sus columnas como si el CTE fuera una tabla normal.

Puede pensar en un CTE como una vista temporal a la que puede hacer referencia en la consulta externa, como estamos haciendo en el ejemplo anterior.

Supongamos que desea escribir una consulta que devuelva las compras gestionadas por los cinco empleados con el salario más alto del departamento de Ventas. Las tablas que utilizaremos en este ejemplo se llaman employees (incluye las columnas employee_id, employee_name, y salary) y purchase (contiene las columnas id, date, customer_id, y total_amount).

WITH top_5_sales_employees AS (
   SELECT TOP 5
     employee_id,
     employee_name 
   FROM employees
   WHERE department_id = ‘Sales’ 
   ORDER BY salary DESC
) 
SELECT 
  p.id AS purchase_id,
  p.date,
  p.total_amount,
  e.id AS employee_id
  e.employee_name 
FROM top_5_sales_employees e
JOIN purchase p
ON p.employee_id = e.id;

Este CTE comienza con la palabra clave WITH y el nombre del CTE, top_5_sales_employees. Entre paréntesis, seleccionamos las columnas que queremos incluir en la ETC y especificamos las condiciones de la consulta en la cláusula WHERE. Por último, utilizamos la sentencia SELECT para seleccionar las columnas apropiadas del CTE como si se tratara de una tabla normal.

Para obtener más información sobre qué son las CTE y cómo se escriben, consulte nuestro artículo ¿Qué es una CTE en SQL Server? Si busca más ejemplos de CTE, los encontrará en este artículo.

Cómo utilizar CTE recursivas en SQL Server

¿Qué es una CTE recursiva en SQL Server? Una CTE recursiva es un tipo de CTE que hace referencia a sí misma en la sentencia SELECT, creando un bucle. Las CTE recursivas se utilizan para recorrer estructuras de datos jerárquicas, como organigramas o redes.

Supongamos que tenemos una tabla llamada employees con columnas para el nombre del empleado, el departamento y el responsable. Esto se muestra en la siguiente tabla, donde cada registro incluye un empleado y a quién reporta dentro de la organización.

De un vistazo, es bastante fácil ver quién depende de quién y cómo es esta jerarquía organizativa. Sin embargo, si tuviéramos cientos de empleados, sería mucho más difícil dar sentido a los datos.

Podemos utilizar un CTE recursivo para generar un árbol jerárquico de los empleados de la empresa. Para ello, ejecutaríamos esta consulta:

WITH employee_manager_cte AS (
  SELECT 
    id, 
    name,
    department,
    manager_id,
    manager_name,
    1 AS level 
  FROM employees 
  WHERE manager_id IS NULL 
  UNION ALL 
  SELECT 
    e.id, 
    e.name,
    e.department,
    e.manager_id,
    e.manager_name,
    level + 1 
  FROM employees e 
  INNER JOIN employee_manager_cte r 
	ON e.manager_id = r.id 
) 
SELECT * 
FROM employee_manager_cte;

Desglosemos esta consulta paso a paso.

En primer lugar, definimos la ETC recursiva con el nombre employee_manager_cte. Seleccionamos las columnas que queremos incluir en la consulta: id, name, department, manager_id, manager_name, y level. La columna level se utiliza para seguir la profundidad del árbol. Empezaremos por el nivel 1; a medida que avancemos en el bucle, este número irá aumentando.

Esta sección antes de UNION ALL se denomina miembro ancla. En el miembro ancla, comenzamos nuestro bucle. En nuestro ejemplo, seleccionamos todos los empleados cuyo manager es NULL. En nuestro organigrama, serán los empleados de la parte superior. En este caso, sólo hay un empleado en este nivel: Sarah Smith, la directora general.

La parte que sigue a UNION ALL se denomina miembro recursivo. En el miembro recursivo, añadimos nuevas filas a las filas que ya se han calculado. En nuestro ejemplo, unimos la tabla employees con el CTE employee_manager_cte en la columna manager_id. Esto crea un bucle que recorre el árbol de arriba hacia abajo. Añadimos 1 a la columna level para registrar la profundidad de cada nodo.

Por último, seleccionamos todas las columnas de employee_manager_cte CTE.

Al ejecutar esta consulta, SQL Server procesa primero el miembro de anclaje, que selecciona a Sarah Smith como raíz del árbol. A continuación, procesa el miembro recursivo, que une a Sarah Smith con su informe directo (Jane Miller). A continuación, une a Jane Miller con su subordinado directo (John Doe) y a John Doe con su subordinado directo (ninguno). Como no hay más filas que añadir al conjunto de resultados, SQL Server deja de procesar la CTE y devuelve el resultado final.

Este es el aspecto del conjunto de resultados:

Otro gran artículo explicativo que puede consultar es ¿Qué es una CTE recursiva en SQL? Allí encontrarás más ejemplos prácticos de CTEs recursivas en SQL.

Consejos para Escribir CTE Recursivas en SQL Server

1. Empieza con el miembro ancla

El miembro ancla es el punto de inicio de la CTE recursiva. Es la parte de la consulta que define el caso base, o el primer conjunto de filas que se devolverán. En nuestro ejemplo de organigrama, se trata del nivel más alto de gestión. Asegúrese de que el miembro ancla devuelve todas las columnas que necesita en el conjunto de resultados final.

2. Asegúrese de que el número de columnas coincide

El miembro recursivo y los miembros ancla deben tener el mismo número de columnas y los mismos tipos de datos correspondientes a las columnas: En una CTE recursiva, el miembro recursivo hace referencia a la propia CTE, por lo que es importante asegurarse de que el miembro recursivo y el miembro ancla tienen el mismo número de columnas y los mismos tipos de datos en columnas correspondientes.

3. Siempre UNION ALL

En SQL Server sólo se puede utilizar UNION ALL para combinar un miembro ancla y un miembro recursivo: Cuando combine el miembro ancla y el miembro recursivo, debe utilizar UNION ALL, no UNION. UNION ALL conserva todas las filas, incluidos los duplicados, mientras que UNION elimina los duplicados. UNION no está permitido en consultas recursivas en SQL Server.

4. ¡Cuidado con los Bucles Infinitos!

Asegúrese de escribir una comprobación de terminación en su consulta. Una verificación de terminación es una condición que detiene el bucle indefinido de la CTE recursiva. Sin una comprobación de terminación, la consulta ejecutará por defecto un máximo de 100 recursividades y luego fallará.

La comprobación de terminación se incluye normalmente en la cláusula WHERE del miembro recursivo y especifica cuándo debe detenerse la recursión.

Siguiendo estos consejos, podrá escribir CTEs recursivas eficientes y efectivas que le ayudarán a resolver problemas jerárquicos complejos en SQL Server. Asegúrese de consultar este artículo sobre las mejores prácticas de CTE para obtener más detalles.

CTEs Recursivas en SQL Server - Más Ejemplos

Ejemplo 1: CTE Recursiva para una Red de Transporte

Otro ejemplo de uso de una CTE recursiva es para mostrar una red de transporte. Supongamos que tenemos una tabla llamada routes que almacena información sobre rutas de transporte entre ciudades. Incluye la ciudad de origen, la ciudad de destino y la distancia entre las ciudades. Queremos escribir una consulta que devuelva todas las ciudades a las que se puede llegar desde una ciudad de partida dada, junto con la distancia total a cada ciudad.

Nuestros datos se encuentran en una tabla llamada routes:

Esta es la consulta:

WITH recursive_cte AS (
  SELECT 
    source_city, 
    destination_city, 
    distance,
    source_city AS visited_cities 
   FROM routes 
   WHERE source_city = ‘New York’
   
   UNION ALL 
   
   SELECT 
     r.source_city, 
     r.destination_city, 
     r.distance + rc.distance,
     rc.visited_cities + ‘,’ + r.destination_city
   FROM routes r 
   INNER JOIN recursive_cte rc 
	ON r.source_city = rc.destination_city 
   WHERE rc.distance < 2000
AND CHARINDEX(',' + r.destination_city + ',', ',' +                                 rc.visited_cities + ',') = 0
) 
SELECT 
  destination_city, 
  distance
FROM recursive_cte

Esta ETC recursiva comienza con el miembro ancla, que selecciona todas las rutas que comienzan en Nueva York. En el miembro recursivo, unimos la tabla de rutas con el CTE recursive_cte en la columna source_city para encontrar todas las ciudades alcanzables desde Nueva York.

Hemos añadido una nueva columna llamada visited_cities que almacena la lista de ciudades visitadas como una cadena separada por comas. Inicializamos esta columna en el miembro ancla del CTE estableciéndola en la ciudad de origen. En el miembro recursivo, concatenamos la ciudad actual a la lista de ciudades visitadas y comprobamos si la ciudad de destino ya ha sido visitada utilizando la función CHARINDEX. Si la ciudad de destino no ha sido visitada, la añadimos a la lista de ciudades visitadas y continuamos la recursión.

Seguimos añadiendo filas al conjunto de resultados hasta que no haya más ciudades que añadir o hasta que lleguemos a nuestra comprobación de finalización. El conjunto de resultados muestra todas las ciudades a las que se puede llegar desde Nueva York y la distancia total a cada ciudad.

Ejemplo 2: Uso de CTE Recursivas para Dependencias de Tareas en un Proyecto

Otro escenario en el que podríamos utilizar una CTE recursiva sería para comprender las dependencias de tareas de un proyecto.

Supongamos que tenemos una tabla llamada tasks que almacena información sobre las tareas de un proyecto, incluyendo el task_id, el task_name, el ID de la tarea de la que depende (depends_on_task_id), y el time_required para completar la tarea. Queremos escribir una consulta que calcule el tiempo total necesario para completar una tarea dada, incluyendo todas sus tareas dependientes.

Los datos se muestran a continuación:

Escribamos una consulta que obtenga el tiempo total necesario para la tarea de desarrollo y sus dependencias.

WITH recursive_cte AS (
  SELECT 
    task_id, 
    task_name, 
    depends_on_task_id, 
    time_required
  FROM tasks 
  WHERE task_id = 2
  
  UNION ALL 

  SELECT 
    t.task_id, 
    t.task_name, 
    depends_on_task_id, 
    t.time_required + rc.time_required
  FROM tasks t 
  INNER JOIN recursive_cte rc 
	ON t.depends_on_task_id = rc.task_id
WHERE rc.total_time < 20) 
SELECT 
  task_name, 
  time_required as total_time
FROM recursive_cte
GROUP BY task_name;

Esta CTE recursiva comienza con el miembro de anclaje, que selecciona la fila de la tabla tasks con task_id = 2 (Desarrollo). En el miembro recursivo, unimos la tabla tasks con la ETC recursive_cte en las columnas depends_on_task_id y task_id para encontrar todas las tareas que dependen de Desarrollo. Calculamos el tiempo total necesario para cada tarea sumando el tiempo necesario para la tarea actual al tiempo total necesario para sus tareas anteriores.

Por último, consultamos los resultados por task_name y time_required para cada tarea. Estos son los resultados:

La columna task_name muestra el nombre de cada tarea, y la columna total_time muestra el tiempo total necesario para completar la tarea y todas sus tareas anteriores. Por ejemplo, la primera fila muestra que el tiempo total necesario para completar Desarrollo y todas sus dependencias es 15, que es la suma del tiempo necesario para Desarrollo (10) y Pruebas (5).

Las otras filas muestran el mismo concepto, con las columnas task_name y total_time indicando el nombre de cada tarea y su tiempo total requerido.

Más información sobre las CTE recursivas en SQL Server

Las CTEs Recursivas en SQL Server pueden ser útiles en una variedad de escenarios como:

• Jerarquías de empleados: Se puede utilizar una CTE recursiva para recorrer un árbol de empleados y sus responsables para calcular la compensación total de todos los empleados de una rama determinada.
• Categorías de productos: Un CTE recursivo puede utilizarse para recorrer un árbol de categorías de productos para encontrar todos los productos de una categoría dada y sus subcategorías.
• Redes sociales: Una CTE recursiva puede utilizarse para recorrer un grafo de conexiones de redes sociales para encontrar todos los amigos de un usuario dado y sus conexiones.
• Dependencias de tareas: Una CTE recursiva puede utilizarse para recorrer un árbol de dependencias de tareas para calcular el tiempo necesario para completar un proyecto.

En general, cualquier escenario en el que los datos estén organizados jerárquicamente o en una estructura gráfica puede beneficiarse potencialmente del uso de una ETC recursiva. Siguiendo las mejores prácticas, como empezar por el miembro ancla, asegurarse de que los miembros recursivo y ancla tienen el mismo número de columnas y tipos de datos, y escribir una comprobación de terminación, puede escribir consultas eficientes y precisas que atraviesen estructuras de datos jerárquicas y resuelvan problemas complejos.

Refuerce sus conocimientos sobre los CTE recursivos realizando nuestro curso Consultas recursivas y expresiones de tabla comunes en MS SQL Server. Sus 112 ejercicios interactivos le ayudarán a convertirse en un maestro de este tema avanzado.