5 Ejemplos Prácticos de SQL CTE

Las expresiones comunes de tabla (CTEs) se introdujeron en SQL para mejorar la legibilidad y la estructura de las consultas SQL, especialmente aquellas que requieren múltiples pasos para obtener la salida necesaria. En este artículo, repasaremos varios ejemplos para mostrar cómo las CTE de SQL pueden ayudarle con cálculos complejos y estructuras de datos jerárquicas.

Expresiones Comunes de Tabla en SQL

Las expresiones comunes de tabla (CTEs), también llamadas cláusulas WITH, permiten crear subconsultas con nombre que son referenciadas en la consulta principal. Las CTE se introdujeron en SQL para mejorar la legibilidad y la estructura de una sentencia SQL.

La sintaxis básica de las CTE es la siguiente:

WITH subquery_name AS
(SELECT … subquery ...)
SELECT … main query ...

Empezamos con la palabra clave WITH seguida del nombre que asignamos a la CTE (subconsulta). A continuación, ponemos la palabra clave AS e incluimos la subconsulta entre paréntesis. Una vez definida la CTE, pasamos a la consulta principal, donde podemos hacer referencia a esta CTE por su nombre.

Si no conoce las CTEs, puede que necesite consultar este artículo que explica con más detalle cómo funcionan las CTEs.

Es posible tener múltiples CTEs en una consulta, referenciar un CTE dentro de otro (es decir, CTEs anidados), o incluso referenciar un CTE dentro de sí mismo (CTEs recursivos). Esto nos da un montón de herramientas y oportunidades.

Ejemplos de CTE SQL

Para mostrar cómo las CTEs pueden ayudarle con varias tareas analíticas, voy a repasar cinco ejemplos prácticos.

Empezaremos con la tabla orderscon información básica como la fecha del pedido, el ID del cliente, el nombre de la tienda, el ID del empleado que registró el pedido y el importe total del pedido.

Ahora vamos a escribir un par de consultas SQL. También puedes practicar CTEs SQL en este curso interactivo Consultas recursivas y expresiones de tabla comunes interactivo que cubre todos los tipos de CTEs.

Ejemplo 1

En nuestro primer ejemplo, queremos comparar el importe total de cada pedido con el importe medio de los pedidos en la tienda correspondiente.

Podemos empezar calculando el importe medio de los pedidos de cada tienda utilizando una CTE y añadiendo esta columna a la salida de la consulta principal:

WITH avg_per_store AS
  (SELECT store, AVG(amount) AS average_order
   FROM orders
   GROUP BY store)
SELECT o.id, o.store, o.amount, avg.average_order AS avg_for_store
FROM orders o
JOIN avg_per_store avg
ON o.store = avg.store;

Como puede ver, nuestra consulta comienza con un CTE llamado avg_per_store. Usando este CTE, creamos una tabla que lista todas las tiendas y el importe medio de los pedidos por tienda. Luego, en la consulta principal, seleccionamos para mostrar el ID del pedido, el nombre de la tienda, el importe del pedido de la tabla original orders y el importe medio de los pedidos de cada tienda (avg_for_store) de la CTE definida anteriormente.

Este es el resultado:

Con esta tabla, podemos ver cómo se compara cada pedido con el importe medio de los pedidos en la tienda correspondiente.

Pasemos ahora a un ejemplo más complejo.

Ejemplo 2

Aquí compararemos diferentes tiendas. En concreto, queremos ver cómo se compara el importe medio de los pedidos de cada tienda con el mínimo y el máximo del importe medio de los pedidos de todas las tiendas.

Como en el primer ejemplo, empezaremos calculando el importe medio de los pedidos de cada tienda mediante una CTE. A continuación, definiremos otros dos CTEs:

• Para calcular el mínimo del importe medio de los pedidos entre todas las tiendas.
• Para calcular el máximo del importe medio de los pedidos de todas las tiendas.

Observe que estos dos CTEs utilizarán el resultado del primer CTE.

Finalmente, en la consulta principal, uniremos los tres CTEs para obtener la información que necesitamos:

WITH avg_per_store AS (
    SELECT store, AVG(amount) AS average_order
    FROM orders
    GROUP BY store),
    min_order_store AS (
    SELECT MIN (average_order) AS min_avg_order_store
    FROM avg_per_store),
    max_order_store AS (
    SELECT MAX (average_order) AS max_avg_order_store
    FROM avg_per_store)
SELECT avg.store, avg.average_order, min.min_avg_order_store,
max.max_avg_order_store
FROM avg_per_store avg
CROSS JOIN min_order_store min
CROSS JOIN max_order_store max;

Como puede ver, incluso con múltiples CTEs anidados, la consulta SQL permanece limpia y fácil de seguir. Si utilizáramos subconsultas, tendríamos que anidar una subconsulta dentro de las otras dos y repetirla varias veces dentro de la misma consulta. Aquí, con las CTEs, simplemente definimos las tres CTEs al principio y luego las referenciamos cuando sea necesario.

Este es el resultado de la consulta:

Puede ver fácilmente cómo se compara cada tienda con las demás en cuanto al importe medio de los pedidos. Por supuesto, cuando sólo tiene tres tiendas, podríamos compararlas sin añadir las columnas min_avg_order_store y max_avg_order_store. Sin embargo, cuando se necesita analizar el rendimiento de muchas tiendas por diferentes métricas, este enfoque puede ser muy útil.

Lea esta guía para aprender las mejores prácticas de SQL CTE.

Ejemplo 3

En nuestro siguiente ejemplo, continuaremos comparando el rendimiento de nuestras tiendas pero con algunas métricas diferentes. Digamos que nuestra empresa considera que los pedidos inferiores a 200 $ son pequeños y que los pedidos iguales o superiores a 200 $ son grandes. Ahora, queremos calcular cuántos pedidos grandes y pequeños tuvo cada tienda.

Para abordar esta tarea utilizando las cláusulas WITH, necesitamos dos expresiones de tabla comunes:

• Obtener el número de pedidos grandes de cada tienda.
• Obtener el número de pedidos pequeños de cada tienda.

Algunas tiendas pueden no tener ningún pedido grande o ningún pedido pequeño, lo que lleva a los valores de NULL. Tenemos que asegurarnos de no perder ninguna tienda durante los JOINs. Por esta razón, prefiero tener otro CTE que simplemente genere una lista de todas las tiendas. Luego, en la consulta principal, uniremos este CTE con los dos CTE que contienen las métricas de pedidos grandes y pequeños:

WITH stores AS
   (SELECT store
    FROM orders
    GROUP BY store),
  big AS
  (SELECT store, COUNT(*) AS big_orders
   FROM orders
   WHERE amount >= 200.00
   GROUP BY store),
  small AS
  (SELECT store, COUNT(*) AS small_orders
   FROM orders
   WHERE amount < 200.00
   GROUP BY store)
SELECT s.store, b.big_orders, sm.small_orders
FROM stores s
FULL JOIN big b
ON s.store = b.store
FULL JOIN small sm
ON s.store = sm.store;

Así, en esta consulta

• Definimos el CTE stores para obtener una lista completa de tiendas.
• Definimos el CTE big para calcular, para cada tienda, el número de pedidos con un importe total igual o superior a 200 $.
• Definir el CTE small para calcular, para cada tienda, el número de pedidos inferiores a 200 $.
• Una los tres CTEs.

Este es el resultado:

Ahora podemos ver que la tienda del Oeste funciona realmente bien; todos sus pedidos son superiores a 200 $. La tienda del Centro también va bien, con dos pedidos por encima de 200 $ y un pedido por debajo de 200 $. Sólo la mitad de los pedidos de la tienda Este son grandes, con dos pedidos por encima de 200 $ y dos pedidos por debajo de 200 $.

Ejemplo 4

Para los dos siguientes ejemplos, utilizaremos la siguiente tabla con información básica sobre los empleados de nuestra empresa. Específicamente, tenemos el ID del empleado, el nombre, el apellido, el ID del superior del empleado, el departamento y el monto del último bono.

Ahora, calculemos la bonificación media por departamento y, a continuación, contemos cuántos empleados tuvieron bonificaciones superiores a la media de sus respectivos departamentos y cuántos tuvieron inferiores.

Las expresiones comunes de tabla pueden ser muy útiles con cálculos tan complejos. Tendremos tres CTEs en esta consulta SQL:

• Para calcular el importe medio de las bonificaciones de cada departamento.
• Para calcular, por departamento, el número de empleados cuyas bonificaciones estaban por encima de la media de su respectivo departamento.
• Para calcular, por departamento, el número de empleados cuyas bonificaciones fueron inferiores a la media de sus respectivos departamentos.

En la consulta principal, uniremos los tres CTE.

WITH avg_bonus_department AS
    (SELECT department, AVG(bonus) AS average_bonus
    FROM employees
    GROUP BY department),
    above_average AS
    (SELECT e.department, count(*) AS employees_above_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus > average_bonus
     GROUP BY e.department),
     below_average AS
     (SELECT e.department, count(*) AS employees_below_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus < average_bonus
     GROUP BY e.department)
SELECT avg.department, avg.average_bonus, aa.employees_above_average, ba.employees_below_average
FROM avg_bonus_department avg
LEFT JOIN above_average aa
ON avg.department = aa.department
LEFT JOIN below_average ba
ON avg.department = ba.department;

He aquí el resultado de la consulta:

Como sólo hay una persona en Finanzas, la bonificación media del departamento es exactamente igual a la bonificación de esta persona. Como resultado, no tenemos a nadie en el departamento de Finanzas cuya bonificación esté por encima o por debajo de la media (reflejados como valores NULL en el resultado). Lo mismo ocurre con el director general.

Para el departamento de Ventas, podemos ver que la bonificación media fue de 828,57 $, y sólo dos de siete personas tuvieron bonificaciones por encima de la media del departamento.

Le dejamos que interprete los resultados de los departamentos de Marketing y Operaciones del mismo modo, y pasaremos a un ejemplo aún más complejo con una consulta recursiva.

Ejemplo 5

Las expresiones comunes de tabla pueden referenciarse a sí mismas, lo que las convierte en una herramienta perfecta para analizar estructuras jerárquicas. Veámoslo con un ejemplo.

Utilizando la información de la tabla employees y de la tabla orders podemos dibujar la siguiente estructura organizativa de nuestra empresa. El personal de tienda se considera parte del equipo de Ventas. Además, en la orders tabla, podemos ver qué empleados tienen pedidos en qué tiendas, por lo que podemos deducir la tienda a la que pertenece cada vendedor.

Ahora, digamos que necesitamos averiguar el nivel de cada empleado en la estructura organizativa (es decir, el nivel 1 es el director general, el nivel 2 es para sus subordinados directos, etc.). Podemos añadir una columna que muestre esto con una consulta recursiva:

WITH RECURSIVE levels AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    1 AS level
  FROM employees
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employees.id,
    employees.first_name,
    employees.last_name,
    employees.superior_id,
    levels.level + 1
  FROM employees, levels
  WHERE employees.superior_id = levels.id
)

SELECT *
FROM levels;

Como ve, el CTE levels de esta consulta se referencia a sí misma. Comienza seleccionando el registro correspondiente al gran jefe, el que no tiene superior (es decir, superior_id IS NULL). Asignamos 1 al nivel de esta persona, luego usamos UNION ALL para añadir otros registros, añadiéndole uno por cada nivel de dirección en la estructura organizativa.

Este es el resultado:

El tema de las consultas recursivas es bastante difícil, así que no voy a entrar en más detalles aquí. Pero por favor asegúrate de revisar este artículo que explica las CTEs recursivas con ejemplos, especialmente si trabajas con datos jerárquicos.

Y consulta este artículo para ver más ejemplos de CTEs SQL.

¡Practiquemos las CTEs SQL!

Espero que estos ejemplos te hayan mostrado lo útiles que pueden ser las CTEs para diferentes tareas analíticas. Ayudan a mejorar la legibilidad y la estructura de tus consultas SQL, ayudan con cálculos anidados y complejos, y son útiles para el procesamiento eficiente de datos jerárquicos. Obtenga más información sobre cuándo utilizar las CTE en este artículo.

Si quieres dominar las expresiones comunes de tabla, te recomiendo que empieces con este único Consultas recursivas y expresiones de tabla comunes curso. Incluye 114 ejercicios interactivos que cubren todo tipo de CTEs, incluyendo CTEs simples, CTEs anidadas y CTEs recursivas. Al final del curso, sabrá cómo gestionar consultas SQL con CTEs, cómo y cuándo anidar CTEs, y cómo utilizar CTEs recursivas para moverse por modelos de datos jerárquicos.

Si quieres dominar otras herramientas avanzadas para el análisis de datos con SQL, deberías plantearte seguir el SQL avanzado curso Va más allá de las CTE y también cubre las funciones de ventana y las extensiones GROUP BY en SQL. Y LearnSQL.es ofrece muchas maneras diferentes de practicar estos conceptos avanzados de SQL en línea.

Gracias por leer y ¡feliz aprendizaje!