Explicación de las CTEs de SQL con ejemplos

Aprenda cómo puede aprovechar el poder de las Expresiones Comunes de Tabla (CTEs) para mejorar la organización y legibilidad de sus consultas SQL.

La abreviatura CTE, comúnmente utilizada, significa Expresión de Tabla Común.

Para aprender sobre las Expresiones de Tabla Común SQL a través de la práctica, recomiendo el curso interactivo Consultas recursivas y expresiones de tabla comunes en LearnSQL.es. Contiene más de 100 ejercicios prácticos sobre CTEs recursivas simples y complejas.

¿Qué hace una CTE? ¿Por qué querría usar una en su código SQL? Respondamos a estas preguntas.

¿Qué es una CTE?

Una Expresión de Tabla Común es un conjunto de resultados temporales con nombre. Se crea una CTE mediante una consulta WITH y luego se hace referencia a ella en una sentencia SELECT, INSERT, UPDATE o DELETE.

Digamos que tienes una tabla llamada schools con las columnas school_id, school_name, district_id, y el número de estudiantes. Necesita escribir una consulta para mostrar una lista de escuelas junto con su ID de distrito y el número promedio de estudiantes por escuela en ese distrito.

Su lógica podría ser la siguiente

1. Cree una tabla con la lista de distritos y el correspondiente número medio de alumnos por escuela.
2. Unir esta tabla con la lista de escuelas y mostrar la información requerida.
3. Elimine la tabla con el número medio de alumnos por escuela para cada distrito.

Si utiliza un CTE, no necesita crear y soltar una tabla. Puede simplemente hacer referencia al conjunto de resultados temporal creado por la consulta WITH, como se ve a continuación:

WITH avg_students AS (
SELECT district_id, AVG(students) as average_students
FROM schools
GROUP BY district_id)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg
ON s.district_id = avg.district_id;

Así, aquí se empieza por definir el conjunto de resultados temporal avg_students en la consulta WITH. Entre los paréntesis, hay una sentencia SELECT que define este conjunto de resultados; contiene una lista de distritos y el correspondiente número medio de alumnos por escuela. Después del paréntesis de cierre, se inicia la sentencia SELECT principal. Observe que hace referencia al conjunto de resultados temporal como una tabla ordinaria, utilizando su nombre asignado (avg_students). El resultado incluirá el nombre de la escuela, el ID de su distrito y el número medio de estudiantes en este distrito.

En el sentido de que no se puede utilizar en ninguna otra consulta SQL, el conjunto de resultados temporal se "abandona". Por supuesto, puede volver a definirlo si lo necesita.

Ahora que tiene una comprensión básica de las Expresiones de Tabla Comunes y su sintaxis, es el momento de ver cómo utilizar las CTEs en casos de negocio del mundo real.

CTEs en acción

Comencemos por explorar los datos. Supongamos que usted es un analista de datos de un banco minorista y quiere analizar las bonificaciones otorgadas a los empleados el mes pasado. La siguiente tabla es el punto de partida:

Bonus_jan

Ahora digamos que quiere ver la bonificación pagada a cada empleado junto con la bonificación media para su puesto. Para ello, primero hay que calcular la bonificación media de cada puesto. Esto se puede hacer en un conjunto de resultados temporal (un CTE). La consulta completa tendrá el siguiente aspecto:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position)
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.bonus, ap.average_bonus_for_position
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position;

Como puede ver, empezamos definiendo el conjunto de resultados temporal avg_position. Luego está la sentencia SELECT principal, donde se une la tabla bonus_jan con el conjunto de resultados temporal avg_position para mostrar información sobre cada empleado, su bonificación y la bonificación media para ese puesto:

Múltiples CTEs en una consulta

Puede tener múltiples Expresiones de Tabla Común en una consulta - simplemente use una palabra clave WITH y separe los CTEs con comas.

Digamos que quiere comparar la bonificación de cada empleado con la bonificación media de ese puesto y la bonificación media de esa región. Para ello, cree dos conjuntos de resultados temporales: uno con la bonificación media de cada puesto y otro con la bonificación media de cada región. Aquí está la consulta completa:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position),
    avg_region AS (
    SELECT region, AVG (bonus) AS average_bonus_for_region
    FROM bonus_jan
    GROUP BY region)    
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.region, b.bonus, ap.average_bonus_for_position, ar.average_bonus_for_region
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position
JOIN avg_region ar
ON b.region = ar.region;

Después de definir los conjuntos de resultados temporales avg_position y avg_region, escriba la sentencia SELECT principal para mostrar las bonificaciones medias de los puestos y las regiones junto con la información de cada empleado:

CTEs anidadas

Las Expresiones de Tabla Común también pueden ser anidadas. Esto significa tener múltiples CTEs en la misma consulta donde al menos una CTE hace referencia a otra CTE. Esto será más claro después de ver un ejemplo.

Supongamos que quiere evaluar el rendimiento de los diferentes puntos de venta de la red comercial del banco. En concreto, quiere comparar la bonificación media de los empleados de cada punto de venta con la bonificación media mínima y máxima de los puntos de venta.

La lógica podría ser la siguiente:

1. Calcular la bonificación media de los empleados de cada punto de venta (CTE: avg_per_outlet).
2. Encontrar la bonificación media mínima entre puntos de venta (CTE: min_bonus_outlet).
3. Buscar la bonificación media máxima de los puntos de venta (CTE: max_bonus_outlet).
4. Imprimir el ID de cada punto de venta junto con la bonificación media de este punto de venta y las bonificaciones medias mínima y máxima de todos los puntos de venta.

Para crear los CTEs min_bonus_outlet y max_bonus_outlet, tendrá que hacer referencia al primer CTE, avg_per_outlet. Aquí está la consulta completa:

WITH avg_per_outlet AS (
    SELECT outlet, AVG(bonus) AS average_bonus_for_outlet
    FROM bonus_jan
    GROUP BY outlet),
    min_bonus_outlet AS (
    SELECT MIN (average_bonus_for_outlet) AS min_avg_bonus_for_outlet
    FROM avg_per_outlet),
    max_bonus_outlet AS (
    SELECT MAX (average_bonus_for_outlet) AS max_avg_bonus_for_outlet
    FROM avg_per_outlet)    
SELECT ao.outlet, ao.average_bonus_for_outlet, min.min_avg_bonus_for_outlet,
max.max_avg_bonus_for_outlet
FROM avg_per_outlet ao
CROSS JOIN min_bonus_outlet min
CROSS JOIN max_bonus_outlet max;

Observe que hay tres Expresiones de Tabla Común diferentes; dos de ellas (min_bonus_outlet y max_bonus_outlet) hacen referencia a otra CTE (avg_per_outlet). Esto las convierte en CTEs anidadas.

En la sentencia SELECT principal, mostramos el ID del punto de venta, la bonificación media de todos los empleados de este punto de venta y las bonificaciones medias mínima y máxima de todos los puntos de venta. Para ello, realizamos una unión cruzada de los tres conjuntos de resultados temporales. Este es el resultado de esta consulta:

Para ver más ejemplos de CTE, consulte las guías introductorias de LearnSQL.essobre qué es un CTE y cuándo debe utilizarlo.

¿Por qué utilizar una CTE?

Ahora, puede haber notado que en la mayoría de los casos, podría usar una o más subconsultas en lugar de CTEs. Entonces, ¿por qué usar una CTE?

• Las Expresiones Comunes de Tabla organizan mejor las consultas largas. Las subconsultas múltiples suelen tener un aspecto desordenado.
• Las CTEs también hacen que una consulta sea más legible, ya que tiene un nombre para cada una de las Expresiones Comunes de Tabla utilizadas en una consulta.
• Las CTEs organizan la consulta para que refleje mejor la lógica humana. Con las CTE, se empieza por definir el conjunto o conjuntos de resultados temporales y luego se hace referencia a ellos en la consulta principal. Con las subconsultas, se empieza por la consulta principal y luego se colocan las subconsultas en medio de la consulta.
• Por último, también existe una categoría específica de CTEs llamada CTEs recursivas que pueden referenciarse a sí mismas. Estos CTEs pueden resolver problemas que no pueden ser abordados con otras consultas. Las consultas recursivas son especialmente útiles para trabajar con datos jerárquicos.

Aprenda más sobre las CTEs recursivas en nuestras guías en profundidad sobre las capacidades de las consultas recursivas SQL y las consultas jerárquicas en PostgreSQL y Oracle.

¡Es hora de practicar las Expresiones de Tabla Comunes!

¿Está entusiasmado por aprovechar el poder de las CTEs en sus consultas SQL? Si quiere escribir CTEs como un profesional, necesita mucha práctica.

LearnSQL.es ofrece un curso completo sobre Consultas recursivas y expresiones de tabla comunes. Incluye 114 ejercicios interactivos que cubren CTEs simples, CTEs anidados y CTEs recursivos. Este curso es una oportunidad perfecta para aprender a gestionar tus consultas SQL con Expresiones de Tabla Comunes, cómo y cuándo anidar CTEs, y cómo utilizar CTEs recursivas.

¿Quiere aprender más sobre las CTEs de SQL? Consulte nuestras guías para principiantes:

• ¿Qué es una CTE?
• ¿Cuándo debo utilizar una Expresión de Tabla Común (CTE)?
• Cómo Organizar las Consultas SQL con CTEs

¡Feliz aprendizaje!