CTE: consultas de múltiples pasos con WITH

Las consultas reales tienden a crecer: tres JOIN, un GROUP BY, una subconsulta, otra subconsulta… y se vuelven ilegibles. CTE ("Tabla Común Expressions", la cláusula WITH) le permiten dar un nombre a un paso intermedio y utilizarlo como si fuera una tabla en la consulta principal.

Sintaxis básica

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

Un CTE es válido sólo para la consulta que le sigue: no sobrevive la ejecución.

Ejemplo

Encuentre los 3 clientes que han gastado más (suma sobre pedidos entregados):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Leer en inglés: "primero calcule el gasto por cliente; luego únase a clientes y ocupar el top 3". Sin un CTE necesitarías un en línea subconsulta en FROM (legal, pero más ruidosa).

Múltiples CTE en cascada

Puede definir múltiples CTE separados por comas, y cada uno puede hacer referencia a los anteriores:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average porque average siempre tiene una sola fila: la El producto cartesiano simplemente agrega total_average a cada fila del resultado.

CTE vs subconsulta: cuándo usar cuál

Los CTE brillan cuando:

• la misma subconsulta se utiliza dos o más veces en la consulta principal;
• desea que la estructura sea explícita en pasos con nombre (autodocumentados);
• son el requisito previo para CTE recursivos (WITH RECURSIVE, la siguiente lección de este módulo).

Una subconsulta en línea es más concisa para un paso que se usa solo una vez.

Pruébalo

Ejercicio de revisión