6 Ejemplos Útiles de CTEs en SQL Server

¿Cómo puedes utilizar las CTEs en SQL Server en tu día a día como profesional de los datos? Responderemos a esta pregunta dándote seis ejemplos.

CTE es la abreviatura de Common Table Expression. Se trata de una característica relativamente nueva de SQL Server que se puso a disposición con SQL Server 2005.

Una CTE es un resultado temporal con nombre. Este resultado sólo está disponible para la consulta que lo ejecuta. No se almacena, por lo que no ocupa espacio en disco. Una CTE es similar a una tabla temporal y puede utilizarse como cualquier otra tabla. Las CTEs se utilizan más a menudo con una sentencia SELECT, pero también se pueden utilizar con INSERT, UPDATE, y DELETE.

Las CTEs son uno de los conceptos más desafiantes en SQL Server. Para aprovechar sus beneficios, su aprendizaje debe ser cuidadosamente estructurado y no apresurado. Nuestro curso Consultas recursivas y expresiones de tabla comunes en MS SQL Server le mostrará cómo escribir una CTE sencilla en SQL Server para empezar. Después aprenderá a escribir múltiples CTEs, anidarlos y utilizarlos dentro de las sentencias SELECT, INSERT, UPDATE, y DELETE. Por último, aprenderá sobre estructuras de datos jerárquicas y gráficas y cómo utilizar CTEs recursivas en SQL Server para consultar dichos datos. Para asegurarte de que practicas lo suficiente, hay 112 ejercicios interactivos para completar en el curso.

Existen, por supuesto, otras formas de aprender CTEs que también puedes consultar.

Sintaxis CTE en SQL Server

Generalmente, la sintaxis CTE en SQL Server es como la del siguiente ejemplo:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Las CTEs siempre deben comenzar con la palabra clave WITH. A continuación viene el nombre de la CTE, la palabra clave AS y los paréntesis. En esos paréntesis se define la CTE. Definirla, como verás en nuestros ejemplos, significa escribir la sentencia SELECT. Puedes encontrar más detalles en este artículo que explica qué es una CTE.

6 Ejemplos de CTEs en SQL Server

1: Encontrar el Promedio de los Números Más Altos y Más Bajos de los Flujos Diarios

En los primeros cinco ejemplos, utilizaremos el mismo conjunto de datos. Muestra algunos datos inventados de una plataforma imaginaria de streaming de música; llamémosla Terpsícore.

El conjunto de datos consta de tres tablas. La primera es artisty aquí está la consulta para crear la tabla. Esta tabla contiene las siguientes columnas:

• id - El ID del artista y la clave principal de la tabla.
• artist_name - El nombre del artista.

Esta tabla muestra tres artistas.

La siguiente tabla es albums. Aquí está la consulta para crearla. Y aquí están las columnas que contiene:

• id - El ID del álbum y la clave primaria de la tabla.
• artist_id - El artista (y la clave externa de la tabla).
• album_title - El título del álbum.
• year_released - El año de publicación del álbum.

Hay diez álbumes en la tabla.

La última tabla es streams. Muestra los datos de streaming de las canciones individuales. Puedes crear la tabla utilizando esta consulta. Y las columnas:

• id - El ID del stream y la clave primaria de la tabla.
• artist_id - El ID del artista y una clave externa.
• album_id - El ID del álbum y una clave externa.
• song_title - El nombre de la canción.
• date - La fecha del stream.
• number_of_streams - El número de veces que se reprodujo la canción en una fecha determinada.
• pay_per_stream - Valor (en dólares) que Terpsichore paga a los artistas por cada emisión.

Hay 45 filas en esta tabla. Te mostraremos sólo las cinco primeras, para que te hagas una idea de la lógica de la tabla.

Ahora, ¡el ejemplo! Empecemos escribiendo sólo un CTE en SQL Server. Lo haremos para calcular el número medio más alto y más bajo de flujos diarios.

Aquí está el código; lo explicaremos a continuación:

WITH daily_streaming AS (
  SELECT date,
	   MIN(number_of_streams) AS minimum_streaming,
	   MAX(number_of_streams) AS maximum_streaming
  FROM streams
  GROUP BY date
)

SELECT AVG(minimum_streaming) AS average_minimum_daily_streaming,
	 AVG(maximum_streaming) AS average_maximum__daily_streaming
FROM daily_streaming;

Una CTE es brillante para descomponer la lógica de cualquier cálculo en SQL Server.

Recuerde que en un día se transmiten varias canciones. El propósito de nuestra CTE es obtener el número más bajo y más alto de streams cada día.

Como se mencionó anteriormente, un CTE en SQL Server siempre comienza con la palabra clave WITH, que es seguida por el nombre del CTE. Nuestro CTE se llama daily_streaming.

Después de la palabra clave AS viene el paréntesis con una sentencia SELECT - es decir, la definición del CTE. La utilizamos, junto con las funciones agregadas MIN() y MAX(), para calcular el mayor y menor número de flujos por fecha.

La siguiente sentencia SELECT utiliza los datos de la CTE, haciendo referencia a ella en la cláusula FROM. Como hemos dicho, una CTE se puede utilizar como cualquier otra tabla. En esta SELECT, usamos la función de agregado AVG() para obtener el promedio de los puntos máximos y mínimos de los flujos diarios.

La salida muestra que el promedio del punto más bajo es de 90 flujos. La media de los flujos diarios más altos es de 8.367.

2: Calcular la tarifa media total pagada por canción

Ahora vamos a practicar lo que hemos aprendido. Nuevamente escribiremos un solo CTE.

El problema que hay que resolver es hallar la tarifa total media que Terpsícore pagó por cada canción.

He aquí la solución:

WITH paid_per_song AS (
  SELECT song_title,
	   SUM(number_of_streams * pay_per_stream) AS total_pay
  FROM streams
  GROUP BY id, song_title, pay_per_stream
)

SELECT song_title,
       AVG(total_pay) AS average_total_pay
FROM paid_per_song
GROUP BY song_title
ORDER BY average_total_pay DESC;

Usamos la CTE de SQL Server para calcular la tarifa total pagada por canción multiplicando el número de streams por el pago por stream, y luego sumándolo usando la función agregada SUM().

No hay cambios en cuanto a la sintaxis de la CTE: primero viene WITH, luego el nombre de la CTE, y AS viene después.

A continuación utilizamos un SELECT que invoca al CTE para calcular el pago medio por canción. Es sencillo: utilice AVG(), haga referencia a la CTE en FROM y agrupe por el título de la canción.

La consulta devuelve el siguiente resultado:

Vemos que la canción 'Uptown' ganó 47,4803330 $ en total. La segunda y tercera canción por ingresos son "I Wanna Be Your Lover" y "Little Red Corvette". Si eres fan de este artista, no necesitas SQL para averiguar quién escribió estas tres canciones.

3: Encuentra el álbum más reproducido de cada artista

En este ejercicio, tienes que encontrar el álbum más reproducido de cada artista. Introduce el nombre del artista, el título del álbum y el número de streams por álbum.

Una vez más, se trata de una consulta con un solo CTE. Sin embargo, es un poco más compleja que las dos anteriores - hay algunos JOINs y una función ventana.

WITH album_streaming AS (
  SELECT artist_id,
	   album_id,
	   SUM(number_of_streams) AS streams_by_album,
	   RANK() OVER (PARTITION BY artist_id ORDER BY SUM(number_of_streams) DESC) AS streaming_rank
  FROM streams
  GROUP BY artist_id, album_id
)

SELECT artist_name,
       album_title,
	 streams_by_album
FROM album_streaming alst
JOIN albums al
ON alst.album_id = al.id
JOIN artist ar 
ON al.artist_id = ar.id
WHERE streaming_rank = 1;

De nuevo, la sintaxis CTE en SQL Server es familiar. Así que centrémonos en lo que hace esta CTE. La utilizamos para clasificar los álbumes por artista. En primer lugar, seleccionamos los ID de artista y álbum. A continuación, utilizamos SUM() para calcular el número de streams por álbum.

Ahora viene la parte crucial: clasificar la salida utilizando la función de ventana RANK(). Una vez invocada la función, aparece la cláusula OVER(), obligatoria para las funciones de ventana de SQL. Particionamos el conjunto de datos por el ID del artista y ordenamos los datos dentro de cada partición por el número de streams, en orden descendente.

¿Qué significa esto en la práctica? Significa que la función de ventana clasificará los álbumes de un artista, luego la clasificación se reinicia cuando la función llega al siguiente artista, y así sucesivamente. El álbum del artista con más streams se clasificará en primer lugar en su partición.

Si ejecuta sólo esta sentencia SELECT dentro del CTE, obtendrá este resultado:

Como puedes ver, los álbumes del primer artista se ordenan del primero al tercero, según el número de streams. Cuando llegamos al segundo artista, la clasificación se reinicia. Lo mismo ocurre con el tercer artista.

Veamos ahora qué hace la segunda sentencia SELECT. En realidad, no es nada complicado. Devuelve el nombre del artista y del álbum y el número de streams. Lo que complica esta consulta es que tenemos que unir tres tablas.

La primera unión es el CTE album_streaming. Luego la unimos con albums y luego con la tabla artist tabla. Al final, filtramos los datos utilizando la cláusula WHERE porque sólo nos interesa el álbum más reproducido.

Obtendrás este resultado:

El álbum de Prince con más streams es "Dirty Mind", con 13.820 streams. Para Jimi Hendrix, el álbum con más streams es 'Are You Experienced', y para Santana, es 'Abraxas'.

Esta solución utiliza las funciones de ventana, así que aquí tienes un recordatorio de cómo funcionan a la hora de clasificar datos.

4: Calcular la media de streams por canción y compararla con la media de streams por fecha

Ahora las cosas se complican. Pero no demasiado, no te preocupes. Nos estamos basando en lo que hemos aprendido hasta ahora sobre CTEs en SQL Server.

Aquí, necesitamos encontrar el número medio de streams por canción. A continuación, tenemos que calcular el número medio de streams por fecha.

La salida debe mostrar ambas métricas. Además, debe mostrar la diferencia entre la media de transmisiones por canción y la media diaria (como diferencia porcentual), el título de la canción y las fechas.

Hasta ahora, hemos escrito consultas con un CTE. Esta vez, la solución consiste en dos CTE. Veamos cómo funciona:

WITH streams_per_song AS (
  SELECT song_title,
	   AVG(number_of_streams) AS average_streams_per_song
  FROM streams
  GROUP BY song_title
),

streams_per_date AS (
  SELECT date,
	   AVG(number_of_streams) AS average_streams_per_date
  FROM streams
  GROUP BY date
)

SELECT song_title,
	 average_streams_per_song,
	 date,
	 average_streams_per_date,
	 (average_streams_per_song - average_streams_per_date)/CAST(average_streams_per_date AS DECIMAL(10,2))*100 AS diff_from_daily_average
FROM streams_per_song, streams_per_date;

El primer CTE se escribe como de costumbre. Lo utilizamos para calcular el número medio de streams por canción con AVG().

Después de cerrar los paréntesis, hay que separar el primer CTE del segundo CTE con una coma.

A continuación escribimos el segundo CTE. ¡He aquí! ¡No hay WITH! Así es. Cuando se escriben varios CTEs en una consulta en SQL Server, se escribe WITH sólo delante del primer CTE. El segundo (y cualquier CTE subsiguiente) comienza con el nombre del CTE; todo lo demás es igual.

Esta segunda consulta sirve para calcular el número medio de flujos por fecha. De nuevo, utilizamos la función AVG().

El tercer SELECT utiliza los datos de ambos CTEs. Devuelve todas las columnas necesarias. La última columna es diff_from_daily_average. La calculamos restando la media de flujos por fecha de la media de flujos por canción. La diferencia se divide por el promedio de streams por fecha y se multiplica por 100 para obtener el porcentaje. Además, convertimos el resultado en un tipo de datos decimal utilizando la función CAST().

Debido al tamaño de la salida, sólo mostraremos las primeras filas:

Los datos muestran que el flujo diario medio para el 1 de enero de 2023 es de 1.031. "All Along the Watchtower" está un 21,14% por debajo de esa media. Las dos canciones siguientes están un 28,13% y un 43,45% por debajo de la media diaria, y así sucesivamente.

5: Calcular la media de pago por álbum más alta y más baja por artista

Vamos a explicar lo que queremos decir con esto. Primero queremos encontrar la media de pago por álbum y fecha. A continuación, tenemos que encontrar el valor más bajo y más alto de pago por álbum. Después, queremos agregar los datos por artista. Junto con su nombre, necesitamos mostrar el valor de la paga más baja que el artista obtuvo por un álbum. Necesitamos hacer lo mismo con el pago más alto por un álbum.

La solución en SQL Server contiene dos CTEs. Sin embargo, esta vez se trata de un CTE anidado. Es decir, el segundo CTE hace referencia al primer CTE. Veamos cómo funciona:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),
	
min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

¡Ten cuidado cuando leas la explicación del código! Es fácil perderse en todas estas agregaciones. Para facilitar la lectura, he copiado cada parte de la consulta y la he seguido con una explicación. Empezaremos con el primer CTE:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),

El primer CTE calcula el pago medio por álbum y la fecha. Esto se hace multiplicando el número de streams por el pago por stream y usando AVG().

min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

Al escribir el segundo CTE en SQL Server, la sintaxis es la misma que en el ejemplo anterior - sin WITH adicional, comienza con el nombre del CTE y separa los CTEs con una coma. La única diferencia es que esta vez el segundo CTE hace referencia al primer CTE, no al conjunto de datos original.

Este CTE anidado utiliza las funciones MIN() y MAX() para encontrar el salario medio más bajo y más alto de cada álbum para todas las fechas. El primer CTE está referenciado en FROM.

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Por último, SELECT une el segundo CTE con las funciones albums y artist tablas. Volvemos a aplicar las funciones MIN() y MAX() sobre el resultado del segundo CTE. Esto es para devolver sólo los valores del menor y mayor pago de todos los álbumes de cada artista.

Esto es lo que obtenemos después de ejecutar la consulta:

6: Encontrar el camino más largo entre Rotterdam y Amsterdam

Se trata de una variación del problema del camino más corto de la teoría de grafos, con la diferencia de que buscaremos el camino más largo.

Un grafo es un tipo de estructura de datos formada por nodos o puntos conectados por aristas. Al estar conectados, es posible encontrar un camino de un nodo a otro aunque no estén conectados directamente.

Piénsalo como un mapa de carreteras. Este es exactamente el ejemplo que utilizaremos aquí. A continuación se muestra la tabla cities_distanceque muestra las ciudades y la distancia entre ellas. Utilice esta consulta para crear la tabla. Contiene estas columnas:

• ciudad_de - La ciudad de origen.
• city_to - La ciudad de llegada.
• distance - La distancia entre las dos ciudades, en kilómetros.

Estos son los datos:

Necesitamos encontrar el camino más largo de Rotterdam a Amsterdam. El camino debe incluir el nombre de todas las ciudades a lo largo del camino, separadas por '/'. Además, debemos mostrar la longitud del camino más largo.

Cuando decimos el camino más largo, queremos excluir los caminos circulares (en los que se pueden hacer rondas interminables y aumentar la distancia). Queremos que el camino más largo sólo pase una vez por cada ciudad.

Para resolver este problema, utilizaremos una CTE recursiva. Es una consulta que se referencia a sí misma hasta llegar al final de los datos. Esta característica es ideal para consultar datos de grafos, donde múltiples caminos pueden llevar al mismo objetivo.

Veamos cómo funciona esta ETC recursiva:

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
        cd.distance AS distance
   FROM cities_distance cd
   WHERE cd.city_from = 'Rotterdam'

UNION ALL

  SELECT cd.city_to,
         CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
  FROM longest_path lp 
  INNER JOIN cities_distance cd
  ON cd.city_from = lp.city_to
  WHERE lp.city_to <> 'Amsterdam'
  AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

La sintaxis es la misma que antes - la consulta recursiva también empieza por WITH en SQL Server.

Como siempre, hay una sentencia SELECT entre paréntesis. Hay dos, para ser más precisos. Veamos qué hace la primera.

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         cd.distance AS distance
  FROM cities_distance cd
  WHERE cd.city_from = 'Rotterdam'

El primer SELECT en recursividad se llama miembro ancla. Sirve para seleccionar el punto de partida de la recursión. El punto de partida será Rotterdam, que obtenemos filtrando esta ciudad en WHERE. La columna city_to se utiliza para mostrar todos los destinos finales a los que se puede llegar directamente desde Rotterdam. La columna path mostrará todas las ciudades de origen y destino. La longitud de esa ruta se muestra en la columna distance.

A continuación viene UNION ALL, que unirá los resultados del ancla y el miembro recursivo, es decir, el segundo SELECT. La unión de estas dos consultas es necesaria para que la recursión funcione.

Nota: En algunos otros dialectos de SQL, también es posible utilizar UNION. Sin embargo, SQL Server sólo permite UNION ALL.

Ahora llegamos al miembro recursivo. Hace referencia al propio CTE en FROM y lo une con la tabla cities_distance. Para que las consultas estén unidas, ambas deben tener el mismo número de columnas del mismo tipo de datos. Las dos primeras columnas son las mismas que en el miembro ancla. La columna longest_path suma todas las distancias para llegar a todas las ciudades desde Rotterdam.

SELECT cd.city_to,
       CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
       CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
FROM longest_path lp 
INNER JOIN cities_distance cd
ON cd.city_from = lp.city_to
WHERE lp.city_to <> 'Amsterdam'
AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

También hemos añadido dos condiciones en WHERE. La primera excluye todas las relaciones en las que Ámsterdam es el destino final; buscamos el camino más largo, no el más corto, hasta Ámsterdam. La segunda condición garantiza que cualquier ciudad nueva que se añada al camino no esté ya incluida en él. De lo contrario, la consulta entrará en una recursión interminable. Esto responde a lo que hemos dicho antes: el camino más largo no debe visitar la misma ciudad más de una vez.

Para entender mejor de qué estamos hablando, aquí está la salida de la CTE recursiva:

Se puede ver que hay tres caminos de Rotterdam a Amsterdam. Uno es directo, con una distancia de 78,20 km. El segundo y el tercero pasan por otras ciudades y tardan 165,90 y 120,50 km, respectivamente.

Eso sí, ¡este no es el resultado final! Nuestra solución también tiene una sentencia SELECT que hace referencia al CTE:

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

Este SELECT devuelve el trayecto y la distancia. Utilizamos el comando TOP 1 combinado con ORDER BY para devolver el camino más largo de Rotterdam a Amsterdam. Ordenamos los datos de mayor a menor distancia, por lo que la primera fila será también el camino más largo.

He aquí el resultado final:

Este resultado muestra que el camino más largo de Rotterdam a Amsterdam es de 165,9 km y pasa por Leiden, Gouda y Utrecht.

Si no se cansa de hablar de este tema, aquí tiene más ejemplos de CTE.

¿Cuándo utilizar las CTE?

Como ha visto en nuestros ejemplos, una CTE tiene muchos propósitos en SQL Server.

Uno de ellos es que generalmente mejora la legibilidad del código. Todas las soluciones anteriores (excepto la recursión) podrían haberse escrito con subconsultas. Pero eso haría el código mucho más largo y menos claro.

Si lo piensas bien, las subconsultas suelen escribirse en contra de la lógica del problema que intentas resolver. Primero tienes la consulta principal, que utiliza la salida de la subconsulta. Así que la subconsulta suele ser el primer paso del cálculo, aunque no esté colocada así en el código.

En cambio, los CTE pueden escribirse siguiendo la lógica del problema. Puede escribir varios CTEs separados y combinar sus salidas en las sentencias SELECT. También puede hacer referencia a la salida de un CTE con la segunda consulta (o tercera, cuarta...), siendo la sentencia final SELECT otro nivel de cálculo.

Uno de los ejemplos también mostraba que se podían clasificar datos en SQL Server utilizando una función de ventana y una CTE.

Y si quieres escribir consultas recursivas en SQL Server, no puedes hacerlo sin CTEs. Un CTE puede ser no recursivo, pero no existen consultas recursivas sin CTE. Junto con los gráficos, la recursividad es extremadamente útil en la consulta de estructuras jerárquicas, como la organización de datos y los árboles genealógicos.

Puedes encontrar más información sobre cuándo utilizar una CTE aquí.

¡Los CTEs son la puerta de entrada al uso de SQL avanzado!

Los CTEs son uno de los conceptos más avanzados de SQL Server. Si quieres abrir la puerta a habilidades de nivel avanzado en SQL Server, los CTEs son imprescindibles.

A medida que tus consultas en SQL Server se vuelvan más complejas, pronto te darás cuenta de que aprender CTEs fue una de las mejores decisiones que tomaste. También son un trampolín para las consultas recursivas, que permiten consultar tipos inusuales de estructuras de datos en SQL Server, como jerarquías y gráficos.

Este artículo es sólo un avance de los conocimientos que puedes encontrar en el curso Consultas recursivas y expresiones de tabla comunes en MS SQL Server. Así que no se detenga aquí. Hay mucho más que aprender.