Cómo escribir múltiples CTEs en SQL

Aproveche todo el potencial de las CTEs combinando dos o más de ellas en una sola consulta SQL.

Las expresiones comunes de tabla, o CTEs, pueden ser una poderosa herramienta SQL. Cuando se escriben dos (o incluso más) CTEs juntas, este poder se multiplica. En este artículo, te mostraré tres formas de escribir múltiples CTEs:

• Usando dos CTEs independientes en una consulta SQL.
• Usando dos CTEs donde el segundo CTE hace referencia al primero.
• Usando dos CTEs donde uno de ellos es recursivo.

Si no estás familiarizado con las CTEs, te recomiendo nuestro curso interactivo Common Table Expressions, que cubre todos los tipos de CTEs, incluyendo las recursivas, en 114 ejercicios prácticos. Puedes leer sobre el curso en un episodio de nuestra serie Curso del Mes.

Si quieres refrescar tus conocimientos rápidamente, estos artículos sobre las CTEs y cuándo debes usarlas son un buen comienzo.

Presentación de los datos

Te mostraré los ejemplos de CTE en un conjunto de datos que consta de dos tablas.

La primera tabla es cars. Contiene datos sobre los coches fabricados por Renault y Nissan. Sus columnas son:

• id - El ID del coche y la clave primaria (PK) de la tabla.
• car_make - El fabricante del coche.
• model - El modelo del coche.
• motor_type - Los detalles sobre el tipo de motor del modelo.
• year_of_production - El año de fabricación del coche.

Aquí hay un ejemplo de datos de la tabla:

La segunda tabla, car_sales, tiene estas columnas

• id - El ID de la información de la venta y la clave primaria (PK) de la tabla.
• report_period - La fecha del informe de ventas.
• sales - El número de coches vendidos.
• cars_id - La clave foránea (FK) que hace referencia a la tabla cars.

Algunos datos de ejemplo de la tabla:

Dos CTEs: Independiente

En este primer ejemplo, te mostraré cómo obtener el total de ventas en general y por marca con dos CTEs independientes. El código está aquí:

WITH sales_per_make AS (
	SELECT car_make,
		 SUM(sales) AS total_sales_per_make
	FROM cars c
JOIN car_sales cs
		ON c.id = cs.cars_id
GROUP BY car_make
),

sales_sum AS (
	SELECT SUM(sales) AS total_sales
	FROM car_sales
)
	
SELECT car_make,
	 total_sales_per_make,
	 total_sales
FROM sales_per_make, sales_sum ss;

Comienzo a escribir el primer CTE como si fuera el único CTE de mi consulta. El nombre del CTE, sales_per_makesigue a la palabra clave WITH, luego viene la palabra clave AS. Después, escribo lo que quiero que haga el CTE entre paréntesis.

En este caso, estoy utilizando la función agregada SUM() para encontrar las ventas por marca de coche. Para ello, tengo que unir las dos tablas de las que dispongo.

Después viene el segundo CTE. Lo principal aquí es que una coma debe separar los dos CTEs. Entonces, el segundo CTE no comienza con la palabra clave WITH sino inmediatamente con el nombre del segundo CTE. Yo lo he llamado sales_sum. La sentencia SELECT entre paréntesis calcula el total de ventas de todas las marcas de coches.

Estas dos CTEs son independientes porque la segunda CTE no hace referencia a la primera.

Para utilizar estas consultas, tengo que escribir una sentencia SELECT (la consulta principal) que haga referencia a ellas. Esto es lo mismo que cuando se escribe un solo CTE. La sentencia SELECT aquí une los resultados de los dos CTEs para devolver esta salida:

El resultado significa que los concesionarios han vendido 176.569 coches Renault y 185.359 coches Nissan, para un total de 361.928 coches.

Ya ves que escribir dos CTEs no es tan difícil. Sin embargo, existen ciertas dificultades cuando se utilizan varios CTEs en una consulta. Las principales cosas que debe tener en cuenta al escribir múltiples CTEs son:

• Utilizar sólo un WITH.
• Separe las CTEs con comas.
• No utilice una coma antes de la consulta principal.
• Sólo hay una consulta principal.

Tener múltiples CTEs funciona sólo si se escribe la palabra clave WITH una vez. Pero esto no está en cualquier lugar que desee. Tienes que escribirla antes del primer CTE. El segundo CTE y los siguientes comienzan con el nombre de los respectivos CTEs, a diferencia del primer CTE que comienza con la palabra clave WITH.

El primer CTE está separado del segundo por una coma. Lo mismo ocurre si se escriben más de dos CTEs: todos los CTEs están separados por una coma. Sin embargo, no importa cuántos CTEs tenga, no hay ninguna coma entre el último CTE y la consulta principal.

Por último, sólo hay una consulta principal. Lo que quiera calcular, sólo funciona si hay una consulta principal. Parece lógico porque puedes referenciar todos los CTEs que quieras unir como cualquier otra tabla. Esa es una de las ventajas de los CTEs, ¡así que aprovecha!

Dos CTEs: Una referenciando a la otra

En este cálculo ligeramente más complejo, vuelvo a utilizar dos CTEs. Esta vez, el segundo hace referencia al primero. Esta es la única opción si quieres que un CTE haga referencia a otro. Sólo puedes referenciar los CTEs anteriores al actual y no los CTEs que le siguen.

Los escribiré para calcular las ventas reales en 2022 (concretamente, en enero y febrero), presupuestar las ventas anuales de 2022 utilizando las ventas medias y, finalmente, encontrar las ventas que faltan por realizar en 2022.

Te guiaré paso a paso a través del código a continuación, para que entiendas lo que está sucediendo aquí:

WITH sales_per_car AS (
	SELECT c.id,
		 c.car_make,
	   	 c.model,
	       c.motor_type,
	       c.year_of_production,
	       AVG(cs.sales)::INT AS average_sales_2022
	FROM cars c JOIN car_sales cs ON c.id = cs.cars_id
	WHERE c.year_of_production = 2022
	GROUP BY c.id, c.car_make, c.model, c.motor_type, c.year_of_production
),

budget AS (
	SELECT *,
		 average_sales_2022 * 12 AS annual_planned_sales_2022
	FROM sales_per_car
)
	
SELECT b.car_make,
	 b.model,
	 b.motor_type,
	 b.year_of_production,
	 SUM(cs.sales) AS actual_ytd_sales_2022,
	 b.annual_planned_sales_2022,
	 b.annual_planned_sales_2022 - SUM(cs.sales) AS remaining_annual_sales_2022
FROM budget b
JOIN car_sales cs
ON b.id = cs.cars_id
GROUP BY b.car_make, b.model, b.motor_type, b.year_of_production, b.annual_planned_sales_2022;

La sintaxis es la misma que en el ejemplo anterior. El CTE sales_per_car devuelve algunas columnas de la tabla cars. También utilizo la función AVG() para calcular la media de ventas en 2022. Este número se convierte en un entero porque estoy hablando de coches, así que quiero ver la media como un número entero de coches.

Este CTE me da la media de ventas de cada modelo producido en 2022. También me indica por qué sé que las ventas son sólo para 2022: los coches producidos en 2022 no podrían venderse en 2021. Esa es la suposición aquí, al menos.

Una coma separa este CTE del segundo, que comienza con su nombre, budget. Este CTE ahora hace referencia al primer CTE como cualquier otra tabla. Puedes verlo en la cláusula FROM. Estoy usando la columna average_sales_2022 porque es el promedio de ventas mensuales reales para 2022. Digamos que el método para presupuestar aquí es multiplicar el promedio de ventas mensuales para ese año por 12 para obtener las ventas anuales planificadas. Eso es exactamente lo que está haciendo este segundo CTE, y esta es la razón por la que tiene que hacer referencia al primer CTE.

La consulta principal une el CTE budget y la tabla car_sales. Estoy utilizando esta consulta para encontrar las ventas reales por modelo en 2022. A continuación, muestro la columna annual_planned_sales_2022 del segundo CTE. Por último, calculando la diferencia entre estas dos columnas, obtengo el número de ventas que quedan por hacer en lo que queda de 2022.

Este es el informe que obtengo al ejecutar la consulta:

Dos CTEs: Uno de los CTEs es recursivo

Ahora pasaré de estos datos pero me mantendré dentro de estas marcas de coches. Imaginemos que Renault está considerando tres inversiones: comprar Jaguar por 2.300.000.000, Volvo por 1.548.470.000, o Alfa Romeo por 2.450.000.000. Está considerando hacerlo en solitario, junto con Nissan, o tanto con Nissan como con Citroën.

Este ejemplo es perfecto para escribir dos CTEs, siendo uno de ellos recursivo:

WITH RECURSIVE company_purchase AS (
	SELECT 2300000000 AS amount
UNION
	SELECT 1548470000 AS amount
UNION
	SELECT 2450000000 AS amount
),

per_buyer AS (
	SELECT  0 AS number_of_buyers,
		  0::DECIMAL AS purchase_amount,
		  0::DECIMAL AS amount_per_buyer
UNION
	SELECT  number_of_buyers + 1,
		  amount,
		  amount/(number_of_buyers + 1)::DECIMAL
	FROM company_purchase, per_buyer
	WHERE number_of_buyers <= 3)

SELECT *
FROM per_buyer
ORDER BY purchase_amount, number_of_buyers;

Siempre que quieras una CTE recursiva, tienes que empezar a escribir CTEs con WITH RECURSIVE. Siempre anuncias tu intención de escribir una CTE recursiva, tanto si esta consulta recursiva es la primera como la segunda CTE. En este caso, mi primera CTE es no recursiva.

Utilizo el company_purchase CTE para crear diferentes inversiones en Jaguar, Volvo o Alfa Romeo.

Luego viene la consulta recursiva. El principio es el mismo: separar los CTEs con una coma e iniciar el segundo CTE sin la palabra clave WITH. En esta segunda CTE, mi punto de partida es ninguna inversión y ningún comprador. Los valores en todas partes serán cero. A continuación, utilizo la recursividad y la consulta calcula el importe por comprador para uno, dos o tres inversores para la primera inversión. A continuación, la recursión repite el mismo cálculo para la segunda y la tercera inversión. Por supuesto, para hacerlo, tengo que unir la consulta recursiva con la no recursiva.

Por último, la consulta principal selecciona todos los datos del per_buyer CTE, con la siguiente salida:

¿Qué me dicen estos datos? Por ejemplo, si tres compradores (Renault, Nissan y Citroën) compran Volvo por 1.548.470.000, cada empresa debería invertir 516.156.666,67.

Se pueden encontrar más ejemplos en el artículo sobre las 5 mejores preguntas de la entrevista del CTE de SQL y en un artículo adicional que habla sobre el uso de dos CTE.

Multiplique más el poder de los CTEs

Estos tres son sólo ejemplos de lo que los CTEs pueden hacer, especialmente de lo que pueden hacer si se combinan múltiples CTEs de diferentes maneras. Este no es un concepto fácil y requiere mucha práctica.

En lugar de inventar sus propios datos y escenarios, tome nuestro curso interactivo de Expresiones de Tabla Común. Le ofrece todo eso y elimina todas las preocupaciones que tiene sobre cómo practicar las CTEs, ¡con más de 100 ejercicios prácticos!