¿Cuándo debo utilizar una expresión de tabla común (CTE)?

¿Le interesa saber más sobre las expresiones comunes de tabla o CTE? ¿Le gustaría saber cuándo son útiles las CTE? Siga leyendo: lo discutiremos en este artículo.

Si ha oído hablar de las expresiones comunes de tabla, probablemente se haya preguntado para qué sirven. Incluso si no lo has hecho, ¡es bueno que estés aquí! Las CTEs pueden ser muy útiles, especialmente si ya dominas los fundamentos de SQL, como seleccionar, ordenar, filtrar datos y unir tablas. Vayamos al grano de inmediato y veamos qué son las CTEs, así como cuándo y cómo utilizarlas.

¿Qué es una expresión común de tabla?

Una expresión común de tabla (CTE) es una característica de SQL relativamente nueva. Se introdujo en SQL:1999, la cuarta revisión de SQL, con normas ISO publicadas de 1999 a 2002 para esta versión de SQL.

Las CTE se introdujeron por primera vez en SQL Server en 2005, y luego PostgreSQL las hizo disponibles a partir de la versión 8.4 en 2009. MySQL esperó un poco más y los puso a disposición en 2018, a partir de la versión 8.0. En pocas palabras, es un conjunto de datos temporal devuelto por una consulta, que luego es utilizado por otra consulta. Es temporal porque el resultado no se almacena en ningún sitio; sólo existe cuando se ejecuta la consulta.

Hay dos tipos de CTEs

• no recursivo
• recursivo

En este artículo sólo hablaré de la CTE no recursiva, y sólo mencionaré la CTE recursiva brevemente al final.

La sintaxis básica del CTE (no recursivo) es la siguiente:

WITH expression_name AS (CTE definition)

Como puede ver, se realiza mediante una sentencia WITH. Por esta razón, los CTEs también se llaman consultas WITH. Después de WITH, se define un CTE entre paréntesis. Definir un CTE significa simplemente escribir una consulta SELECT que le dará un resultado que desea utilizar dentro de otra consulta.

Como puede ver, se hace utilizando una sentencia WITH. Por esta razón, los CTEs también se llaman consultas WITH. Después del WITH, se define un CTE entre paréntesis. Definir CTE significa simplemente escribir una consulta SELECT que le dará un resultado que desea utilizar dentro de otra consulta.

SELECT ... 
FROM expression_name

Usted define su consulta SELECT y luego hace referencia a su CTE, utilizándola como lo haría con cualquier otra tabla después de la cláusula FROM.

Si quieres leer más sobre las CTEs antes de pasar a los ejemplos, aquí hay un artículo que las explica muy bien.

La sintaxis CTE

Ahora, veamos cómo funciona la sintaxis CTE en la práctica. Supongamos que hay una base de datos que contiene varios datos de la universidad con las siguientes tres tablas:

• students
• subjects
• exams

La tabla students tiene las siguientes columnas:

• idel ID del estudiante, una clave primaria
• first_nameel nombre del estudiante
• last_nameel apellido del estudiante

La siguiente tabla es la subjects tabla que contiene los datos:

• idel ID de la asignatura, una clave primaria
• subject_nameel nombre de la asignatura

La tercera tabla es la exams tabla que almacena los siguientes datos:

• idel ID del examen realizado
• exam_datela fecha en que se realizó el examen
• subject_idEl ID de la asignatura, una clave externa de la tabla subjects
• student_idel ID del estudiante que hizo el examen, una clave externa de la tabla students

Su tarea consiste en calcular la nota media de los alumnos. A continuación, para cada estudiante con una nota media superior a 8,5, necesitas mostrar su nombre, apellido y su nota media, y etiquetarlos como estudiantes "excepcionales". ¿Cómo se hace esto con un CTE?

El código que le dará el resultado deseado puede escribirse así:

WITH grade_average AS (
SELECT	s.id,
		s.first_name,
		s.last_name,
		AVG (e.grade) AS average_grade 
FROM students s JOIN exams e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name
)

SELECT	first_name,
		last_name,
		average_grade,
		'exceptional' AS tag
FROM grade_average
WHERE average_grade>8.5;

En primer lugar, debe definir su CTE. Como ya has aprendido, esto se hace utilizando una sentencia WITH. A continuación, el nombre de la CTE, que en este caso es grade_average en este caso. La consulta CTE se define entre los paréntesis. Cuando se mira por sí sola, no es complicada; es una consulta SELECT de aspecto bastante regular. Selecciona id, first_name, y el last_name de la tabla students. También calcula la nota media, utilizando la columna grade de la tabla exams. El resultado se muestra en la nueva columna average_grade. Las tablas students y exams se unen en la columna ID del alumno correspondiente de cada tabla. El resultado se agrupa por las columnas id, first_name, y last_name de la tabla students. Los registros están agrupados, ya que se necesita obtener el resultado por alumno.

Después de definir el CTE, hay otra consulta SELECT que utiliza el CTE. Esta consulta selecciona las columnas first_name, last_name, y average_grade del CTE, grade_average. También asigna el valor "excepcional". Hay una cláusula WHERE al final para mostrar sólo los estudiantes con una nota media superior a 8,5.

Al ejecutar la consulta se obtendrán los nombres de tres alumnos excepcionales.

Uso de más de un CTE en una consulta

Es posible definir y utilizar más de un CTE en una consulta. Para ello, separe cada CTE con una coma y utilice una sentencia WITH sólo cuando defina el primer CTE.

Permítame mostrarle un ejemplo. Con las mismas tablas del ejemplo anterior, tienes la siguiente tarea: mostrar el nombre de las asignaturas y sus respectivas notas medias y mínimas, pero sólo para aquellas asignaturas en las que todos hayan aprobado el examen, es decir, que su nota sea 5 o superior.

Para obtener el resultado deseado, tu consulta debería ser así:

WITH subject_average AS (
SELECT	su.id,
su.subject_name,
		AVG (e.grade) AS subject_average_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
),

min_grade AS (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS subject_min_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
HAVING MIN (e.grade) > 5
)

SELECT	sa.id,
		sa.subject_name,
		sa.subject_average_grade
FROM subject_average sa JOIN min_grade m ON sa.id =m.id;

En primer lugar, se define un CTE llamado subject_average se define. Selecciona las columnas id y subject_name de la tabla subjects. A continuación, calcula las calificaciones medias utilizando los datos de la tabla exams y asigna los resultados en la nueva columna subject_average_grade. Luego, agrupa los datos para obtener el resultado por materia.

Ahora, define el segundo CTE. Recuerde lo que dije antes: separe los CTEs con comas y escriba el segundo CTE omitiendo la sentencia WITH. El segundo CTE aquí se llama min_grade. También selecciona id y subject_name de la tabla subjects luego calcula las calificaciones mínimas, mostrando el resultado en la nueva columna subject_min_grade. Agrupa los datos como se hizo en el primer CTE. Como necesita el resultado sólo para las asignaturas en las que todos han aprobado, utiliza una cláusula HAVING para seleccionar sólo las asignaturas en las que la nota mínima es 5 o superior.

Por último, escribe la consulta SELECT que mostrará el ID de la asignatura, el nombre de la misma y la nota media de cada asignatura que cumpla los criterios. Sólo hay dos asignaturas de este tipo:

Una vez que aprendas lo básico de los CTEs, está el curso Consultas recursivas y expresiones de tabla comunes con muchos más ejemplos donde puedes practicar la escritura de la sintaxis.

Cuándo utilizar las CTEs

Los CTEs te permiten realizar agregaciones de varios niveles. ¿Qué son?

Volvamos a las tablas que utilizamos en los ejemplos anteriores. La tarea ahora es calcular la nota media mínima y la nota media máxima por asignatura.

¿Por dónde empezarías? Si piensas de forma lógica, primero deberías encontrar las notas mínimas y máximas por asignatura y luego encontrar la media de los resultados por asignatura. Es sencillo: el código tiene este aspecto:

SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade,
		AVG (MIN (e.grade)) AS avg_min_grade,
		AVG (MAX (e.grade)) AS avg_max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name;

Lógicamente, esto trata de calcular primero la nota mínima y la nota máxima por asignatura, y luego la media de esos valores. Y ya está. Ahora ejecutas el código y obtienes un mensaje parecido a este:

Msg 130, Level 15, State 1, Line 16
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

¿No es el resultado que esperabas? Eso es porque SQL no permite construcciones como AVG (MIN (e.grade)). Tus pensamientos eran correctos, pero tienes que usar un CTE para traducirlos a un código SQL. Así es como se hace:

WITH min_max_grade AS (
SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
)

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM min_max_grade;

El CTE se llama min_max_grade. En él, hay una sentencia SELECT que calcula las calificaciones mínimas y máximas por asignatura, como pretendía hacer en la consulta que devolvía el mensaje de error. El resultado se muestra en las nuevas columnas min_grade y max_grade. Un CTE ayuda ahora a traducir su lógica en código.

Después de definir el CTE, escribe una sentencia SELECT que calcule la media de min_grade y max_grade desde el CTE. El resultado se mostrará en las nuevas columnas avg_min_grade y avg_max_grade. Ahora que lo ves, es fácil, ¿verdad?

Las CTEs también son muy útiles cuando necesitas organizar consultas largas y complejas. El uso de las CTEs mejorará la legibilidad de su código, ya que lo descompone muy bien en pasos separados. Es más fácil cambiar el código o corregir errores. Si insistiera en no utilizar CTEs, su código podría tener este aspecto:

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
	FROM subjects su JOIN exams e ON su.id = e.subject_id
	GROUP BY su.id, su.subject_name
) AS min_max;

Comparado con la solución que utiliza un CTE, esto parece un poco desordenado y más difícil de leer. La lectura de las subconsultas puede ser difícil, porque primero hay que pensar en lo que hace cada subconsulta, luego volver a la consulta principal, y de alguna manera conectarlas todas en la cabeza. Además, el uso de subconsultas de este modo va en contra de la forma en que tu mente funciona lógicamente y de cómo piensas en los pasos que te llevarían a la solución. Recuerda que has dividido el problema en dos pasos: primero, calcular las notas mínimas y máximas de cada asignatura, y luego calcular la media de las mínimas y las máximas. El código CTE refleja exactamente este orden.

La lógica en el código con una subconsulta es la opuesta a la forma en que pensaste en la solución. Aquí, primero escribimos que queremos un promedio de las calificaciones, luego especificamos en la subconsulta que queremos que los promedios sean de las calificaciones mínimas y máximas. Cuando se utiliza una subconsulta, la forma de escribir el código generalmente va en contra de cómo se piensa en la lógica.

Y si el código con una subconsulta es menos legible y más difícil de entender que el código con un CTE en este sencillo ejemplo, ¡imagina lo que sería si tuvieras que escribir consultas más complejas! Te estarías rascando la cabeza, esforzándote mucho por entender lo que hace cada parte del código. Tener dificultades para entender un código puede ser muy frustrante. Aquí es donde las CTEs pueden ayudarte.

Probablemente hayas notado que las CTEs son muy parecidas a las subconsultas. Tal vez te preguntes por qué estoy usando CTEs cuando todo lo que hice podría haberse hecho con subconsultas. Es cierto, pero además de ser más legibles, las CTEs tienen una gran ventaja sobre las subconsultas: los resultados de una CTE pueden ser utilizados más de una vez en una consulta. Si te interesa saber más sobre este tema, te recomiendo que leas más diferencias entre CTEs y subconsultas.

Anteriormente mencioné que las CTEs pueden ser no recursivas o recursivas. Hasta ahora, sólo hemos visto las CTEs no recursivas. Las CTEs recursivas son CTEs que se referencian a sí mismas; al hacerlo, devuelven el subresultado y repiten el proceso hasta devolver el resultado final. El uso de CTEs recursivos realmente libera el poder de los CTEs; son útiles cuando se procesan estructuras jerárquicas, como árboles y gráficos.

¿Consiguiendo el hilo de cómo usar las CTEs?

Hemos cubierto algunos de los fundamentos de las CTEs en este artículo. Has aprendido qué es una CTE, has entendido su sintaxis y has revisado algunos ejemplos sencillos para darte una idea de lo que las CTEs pueden hacer. También he señalado algunos usos comunes de las CTE para ayudarte a encontrar una forma de utilizarlas en tu estudio o trabajo. Espero haberte dado algunas buenas indicaciones; ahora te toca a ti poner en práctica lo que has aprendido.

Si tienes alguna duda o comentario, házmelo saber en la sección de comentarios.