Cómo usar 2 CTEs en una sola consulta SQL

¿Te has preguntado alguna vez cómo utilizar varias CTEs en una consulta SQL? Lea este artículo y descubra los CTEs recursivos.

Después de aprender las expresiones comunes de tabla o CTEs, una pregunta natural es "¿Puedo usar varias CTEs en una consulta?" Sí, se puede. Y puedes hacerlo con bastante facilidad, especialmente si ya tienes algunos conocimientos básicos sobre las CTEs. Tanto si conoces un poco los CTEs como si eres completamente nuevo en el mundo de los CTEs, leer sobre qué es un CTE es siempre un buen comienzo.

Lo que aprenderás en este artículo puede ser practicado en el curso LearnSQL.es Consultas Recursivas, que fue nuestro curso del mes de agosto.

Comenzaré explicando cómo utilizar dos CTEs en una consulta. Luego te enseñaré a usar CTEs donde el segundo CTE hace referencia al primero. Para terminar este artículo, te mostraré dos consultas con CTEs, una de las cuales es recursiva.

2 CTEs, 1 consulta SQL, 0 problemas

Imaginemos que hay una tabla llamada logins que almacena login datos. Contiene las columnas:

• id - El ID del inicio de sesión.
• username - El usuario que inició la sesión.
• login_date - La fecha de ese inicio de sesión.
• login_start - Cuando el usuario inició la sesión.
• login_end - Cuando el usuario se desconectó.

Si observas la tabla, verás que todos los nombres de usuario aparecen al menos una vez.

Si tu tarea es calcular el tiempo medio (en minutos) que ha pasado cada usuario conectado, ¿cómo lo harías utilizando CTEs?

Necesitarás dos CTEs, con el siguiente código:

WITH distinct_user AS (
	SELECT DISTINCT username
FROM logins),

minutes_logged AS (
	SELECT	username,
			DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins)

SELECT	u.username,
		AVG (minutes) AS avg_time_logged
FROM distinct_user AS u 
JOIN minutes_logged AS m 
ON u.username = m.username
GROUP BY u.username;

Primero, mencionaré que tienes que tener nombres de usuario distintos para que esto funcione. Ahora, comencemos a escribir el primer CTE. Nada inusual aquí - como con cada CTE, lo defines por WITH; ¡no se llaman WITH queries por nada!

Lo que sigue es el nombre del CTE; en este caso, el primer CTE se llama distinct_user. Le sigue la palabra clave AS, y luego simplemente define la sentencia regular SELECT dentro de los paréntesis. Esta sentencia SELECT simplemente selecciona los nombres de usuario únicos de la tabla logins.

Una vez que tengas los distintos nombres de usuario, tienes que calcular el tiempo que el usuario estuvo conectado. Esta es la parte crítica: escribir el segundo CTE. Mi segundo CTE se llama minutes_logged.

Notarás que no hay WITH antes del segundo CTE. Esto es muy importante. Después de definir el primer CTE, está separado del segundo sólo por la coma, es decir, se escribe WITH sólo una vez. Después, no importa cuántos CTEs definas; sólo es importante que los separes con una coma y que empieces cada CTE con su nombre.

Analicemos ahora lo que hace el CTE minutes_logged. Primero toma la columna nombre de usuario de la tabla logins. Luego calcula la diferencia entre login_start y login_end en minutos. Este nuevo valor se mostrará en la columna minutes.

Tendrás que calcular el tiempo medio (en minutos) que cada usuario ha estado conectado. Para esa parte del cálculo, he utilizado la consulta externa. Selecciona el nombre de usuario del CTE distinct_user y luego calcula el promedio de minutos de conexión utilizando la columna minutes del CTE minutes_logged.

Estos dos CTEs se unen utilizando la columna nombre de usuario. Finalmente, el resultado se agrupa por la misma columna, ya que queremos el resultado a nivel de usuario.

¿Pero puedo usar un CTE dentro de otro CTE?

Una pregunta sencilla merece una respuesta sencilla: sí, se puede. Ahora que sabe cómo utilizar múltiples CTEs, escribir un CTE que haga referencia a otro CTE es sólo una variación de lo que ha aprendido. Como es una variación, creo que lo mejor es mostrarte cómo hacerlo utilizando un ejemplo con el que ya estás familiarizado.

Volvamos al ejemplo anterior. Modificaré un poco el código para mostrarte cómo escribir un CTE que haga referencia al primer CTE. Esta es la nueva versión de nuestro código anterior:

WITH difference AS (
	SELECT 	username,
		 	DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins),

average_logged AS (
SELECT	username,
			AVG (minutes) AS average
FROM difference
GROUP BY username)

SELECT DISTINCT 		username,
				average
FROM average_logged;

La lógica es la misma que en el primer ejemplo, sólo que los pasos están en un orden different. El primer CTE, diferencia, calcula los minutos que se han gastado con cada inicio de sesión; esto es lo mismo que en el primer ejemplo. Ahora que tengo los minutos, tengo que calcular el tiempo medio (en minutos) empleado por cada usuario. Para eso usaré el promedio del CTE. Como recuerdas, ponemos una coma entre los dos CTEs y empezamos el segundo por su nombre - ¡no hace falta volver a usar WITH! Este segundo CTE calcula el tiempo medio de cada inicio de sesión; para ello, utiliza la columna minutes del primer CTE y almacena el resultado en la columna average.

Para referenciar el CTE difference en el segundo CTE, lo trata como una tabla: FROM difference. Por último, se agrupa el resultado por el nombre de usuario, ya que no se desea obtener la media de todos los usuarios.

La consulta externa simplemente selecciona las columnas username y el promedio del CTE average_logged. De este modo, se obtiene el mismo resultado que en el primer ejemplo:

Si estos ejemplos no son suficientes para ti, hay un artículo que te da varios ejemplos más para practicar. Esto incluye el uso de más de un CTE también.

Uso de CTEs Recursivos con CTEs No Recursivos

Hay una razón por la que los CTEs se enseñan en nuestro Consultas recursivas y expresiones de tabla comunes curso. Es porque los CTEs también pueden ser recursivos. Esta es la parte más complicada de aprender CTEs. (Asumo que ya sabes qué es una CTE recursiva y cómo escribirla. Si no es así, vea Consulta SQL Larga vs. Consulta SQL Recursiva para una introducción y Hazlo en SQL: Recorrido de Árbol SQL Recursivo para un ejemplo en profundidad. ) Sin embargo, tal vez no te hayas dado cuenta de que puedes utilizar más de un CTE, incluso si uno de ellos es recursivo. Veamos cómo se hace.

Para este ejemplo, imaginemos que quieres comprar una empresa con tus amigos. Hay cuatro opciones para la inversión, cada una de las cuales requiere una cantidad diferente de dinero. Todavía estás en las primeras conversaciones. No estás seguro de cuántos amigos participarán; su participación depende de la cantidad de dinero que tengan para invertir. Esta cantidad depende del número total de inversores que participen y de la opción de inversión elegida.

Para ayudarles a decidir, has decidido calcular la cantidad necesaria por inversor para las cuatro opciones de inversión con un número de inversores de uno a diez.

En este ejemplo, tenemos primero una ETC no recursiva seguida de una ETC recursiva:

WITH RECURSIVE investment AS (
	SELECT amount 5897645 AS investment_amount
	UNION 
SELECT 4536841 AS investment_amount
	UNION 
SELECT 3852457 AS investment_amount
	UNION 
SELECT 3452115 AS investment_amount
),

per_investor AS (
	SELECT	0 AS investors_number,
			0 AS amount,
			0 AS individual_amount
	UNION 
	SELECT	investors_number + 1,
			investment_amount,
			investment_amount/(investors_number + 1)
	FROM investment, per_investor
	WHERE investors_number < 10)

SELECT *
FROM per_investor
ORDER BY  amount, investors_number;

La principal distinción es evidente desde el principio. Al escribir consultas recursivas, tienes que empezar tu código usando WITH RECURSIVE. Como notarás, no tienes que empezar con el CTE recursivo en sí; en este ejemplo, un CTE no recursivo viene primero, aunque usamos WITH RECURSIVE inmediatamente antes de definirlo. Puedes escribir las CTEs en el orden que quieras; la recursiva puede ser la primera o la última. Pero es importante recordar que si vas a tener al menos una consulta recursiva, escribir WITH RECURSIVE es obligatorio.

En el ejemplo actual, mi primer CTE (no recursivo) se llama inversión. Como no tengo la tabla con todos los importes de inversión, he decidido utilizar el CTE como tabla temporal. Como puede ver, los posibles importes de inversión son los siguientes

• 5,897,645
• 4,536,841
• 3,852,457
• 3,452,115

Al utilizar UNION con SELECT en el primer CTE, estoy creando prácticamente una tabla que contiene esas cuatro posibilidades de inversión. Se mostrarán en la columna investment_amount. La tabla resultante para este CTE es:

El segundo CTE es recursivo. Sin embargo, esto no cambia nada en comparación con la escritura de dos CTEs no recursivas: la coma vuelve a separar las CTEs.

Analicemos un poco el segundo CTE. La primera sentencia SELECT define tres columnas que tienen el valor 0: investors_number, cantidad y individual_amount. Como he dicho, el CTE recursivo se refiere a sí mismo, es decir, se requiere el operador UNION.

El operador UNION va seguido de una o más sentencias SELECT, que realizará la operación deseada en la primera sentencia SELECT. Esto significa que añadirá 1 a la columna investors_number. Luego pondrá investment_amount de la primera sentencia CTE en la columna cantidad. Dividirá el importe de la inversión con el número de inversores implicados; el resultado se muestra en la columna individual_amount. Realizará esta operación para los cuatro importes de inversión hasta llegar a diez inversores.

La consulta externa simplemente selecciona todos los datos del CTE per_investor, con los datos ordenados por las columnas importe y investors_number. Ejecute esta consulta y disfrute de todas las posibilidades que ha calculado:

Impresionante, ¿verdad?

La combinación de dos o más CTEs realmente funciona

Las expresiones comunes de tabla de SQL son una herramienta poderosa. Son útiles si quieres que tu código sea más legible, pero su valor no es sólo cosmético; sus posibilidades reales brillan si sabes cómo usar varias CTEs en una consulta o incluso escribir una CTE recursiva en SQL.

Como has visto, no es difícil combinar dos o más CTEs. Una vez que sepas eso, realmente se abren las posibilidades de usar CTEs en varias situaciones. Ahora depende de ti encontrar los casos en los que tendrás que usar CTEs. ¡Y practica, practica, practica! Para ello, siempre es recomendable utilizar nuestro curso Consultas recursivas y expresiones de tabla comunes . Te dará tablas para practicar, así que no tendrás que preocuparte de crear escenarios tú mismo.

No dudes en compartir tu experiencia con los CTEs de SQL en nuestra sección de comentarios.