CTE: la cláusula WITH

1. Qué es

Un CTE (Common Table Expression) es una tabla derivada con nombre, declarada antes de la consulta que la usa.

WITH nombre AS (
    SELECT ...
)
SELECT ... FROM nombre;

Hace exactamente lo mismo que una tabla derivada. La diferencia entera es el orden de lectura, y no es un detalle menor:

-- Tabla derivada: se lee de dentro hacia fuera
SELECT t2.pais FROM (SELECT t1.pais FROM (SELECT ...) t1 WHERE ...) t2;

-- CTE: se lee de arriba abajo, como se piensa
WITH paso1 AS (SELECT ...),
     paso2 AS (SELECT ... FROM paso1 WHERE ...)
SELECT pais FROM paso2;

Un CTE convierte una consulta anidada en una secuencia de pasos con nombre. Ese nombre es documentación: ventas_por_cliente dice lo que es; t2 no dice nada.

2. Sintaxis

WITH nombre1 AS (
    consulta1
),
nombre2 AS (
    consulta2          -- puede usar nombre1
)
SELECT ... FROM nombre1 JOIN nombre2 ...;

Reglas:

• WITH va antes del SELECT principal, y solo una vez aunque haya varios CTE.
• Los CTE se separan por coma, y AS va antes del paréntesis.
• Un CTE puede usar los anteriores, no los posteriores (salvo con RECURSIVE, cte-recursivas).
• Viven solo durante esa consulta. No es una vista ni una tabla temporal: al terminar, desaparece.
• Se pueden usar tantas veces como quieras dentro de la consulta.

3. Ejemplos sobre Northwind

El caso simple:

WITH por_pais AS (
    SELECT ship_country AS pais,
           count(*)              AS pedidos,
           round(sum(freight),2) AS flete
    FROM orders
    GROUP BY 1
)
SELECT * FROM por_pais WHERE pedidos > 50 ORDER BY pedidos DESC;

  pais   | pedidos |  flete
---------+---------+----------
 USA     |     122 | 13771.29
 Germany |     122 | 11283.28
 Brazil  |      83 |  4880.19
 France  |      77 |  4237.84
 UK      |      56 |  2954.27

Con una sola transformación la ganancia es pequeña. Aparece cuando hay dos o más.

3.1 Encadenar pasos

WITH ventas AS (
    SELECT o.customer_id,
           sum(od.unit_price * od.quantity * (1 - od.discount)) AS total
    FROM orders o
    JOIN order_details od ON od.order_id = o.order_id
    GROUP BY 1
),
media AS (
    SELECT avg(total) AS media_cliente FROM ventas      -- usa el CTE anterior
)
SELECT count(*) AS por_encima
FROM ventas, media
WHERE ventas.total > media.media_cliente;

 por_encima
------------
         30

30 clientes de 89 facturan por encima de la media. Léelo de arriba abajo: calcula las ventas por cliente → calcula su media → cuenta cuántos la superan. Es la secuencia mental, escrita tal cual.

El FROM ventas, media sin ON es un CROSS JOIN deliberado: media tiene una sola fila, así que multiplicar por ella no multiplica nada. Es el uso legítimo del producto cartesiano de cross-join-y-self-join.

3.2 El ejemplo que justifica el tema: segmentación

WITH ventas_cliente AS (
    SELECT o.customer_id,
           count(DISTINCT o.order_id)                           AS pedidos,
           sum(od.unit_price * od.quantity * (1 - od.discount))  AS total
    FROM orders o
    JOIN order_details od ON od.order_id = o.order_id
    GROUP BY 1
),
clasificado AS (
    SELECT customer_id, pedidos, total,
           CASE WHEN total >= 50000 THEN 'A'
                WHEN total >= 20000 THEN 'B'
                ELSE                     'C'
           END AS segmento
    FROM ventas_cliente
)
SELECT segmento,
       count(*)                  AS clientes,
       round(sum(total), 2)      AS facturacion,
       round(100.0 * sum(total) / (SELECT sum(total) FROM ventas_cliente), 1) AS pct
FROM clasificado
GROUP BY segmento
ORDER BY segmento;

 segmento | clientes | facturacion | pct
----------+----------+-------------+------
 A        |        4 |   370612.03 | 29.3
 B        |       14 |   389585.91 | 30.8
 C        |       71 |   505595.10 | 39.9

4 + 14 + 71 = 89 clientes; 29,3 + 30,8 + 39,9 = 100%. ✓

Tres cosas que enseña:

1. ventas_cliente se usa dos veces — desde clasificado y desde la subconsulta del pct. Con tablas derivadas habría que escribirla dos veces enteras.
2. Cada CTE es un paso con nombre: agregar → clasificar → resumir. Escrito con anidamiento serían tres niveles de paréntesis.
3. El resultado dice algo: 4 clientes aportan el 29,3% del negocio. Los 71 del grupo C, ocho de cada diez clientes, aportan el 39,9%.

4. Rendimiento: inline contra materializado

Esto es lo que más ha cambiado y lo que más gente sigue contando mal.

Hasta PostgreSQL 11, un CTE era una barrera de optimización: se calculaba entero y aparte, siempre. Desde la 12 se incorpora (inline) a la consulta principal si se usa una sola vez y no modifica datos.

EXPLAIN (COSTS OFF)
WITH t AS (SELECT ship_country, count(*) AS n FROM orders GROUP BY 1)
SELECT * FROM t WHERE n > 50;

 HashAggregate
   Group Key: orders.ship_country
   Filter: (count(*) > 50)
   ->  Seq Scan on orders

No hay ningún CTE Scan. PostgreSQL fusionó el CTE con la consulta y empujó el filtro dentro del agregado. Es exactamente el mismo plan que sin CTE.

Forzándolo a materializar:

EXPLAIN (COSTS OFF)
WITH t AS MATERIALIZED (SELECT ship_country, count(*) AS n FROM orders GROUP BY 1)
SELECT * FROM t WHERE n > 50;

 CTE Scan on t
   Filter: (n > 50)
   CTE t
     ->  HashAggregate
           Group Key: orders.ship_country
           ->  Seq Scan on orders

Ahora sí: se calcula el CTE entero y después se filtra encima. Se ve todavía más claro sin agregación:

-- Sin materializar: el filtro llega hasta la tabla
 Aggregate
   ->  Seq Scan on orders
         Filter: (ship_country = 'Brazil')

-- Materializado: se copian las 830 filas y luego se filtran
 Aggregate
   CTE t
     ->  Seq Scan on orders
   ->  CTE Scan on t
         Filter: (ship_country = 'Brazil')

Cuándo se materializa solo: cuando el CTE se referencia más de una vez.

EXPLAIN (COSTS OFF)
WITH t AS (SELECT ship_country, count(*) AS n FROM orders GROUP BY 1)
SELECT a.ship_country, a.n, b.n FROM t a JOIN t b ON b.n = a.n AND b.ship_country <> a.ship_country;

 Hash Join
   CTE t
     ->  HashAggregate
   ->  CTE Scan on t a
   ->  CTE Scan on t b

Aparece el CTE t una sola vez y dos CTE Scan: se calculó una vez y se leyó dos. Eso es una ventaja real frente a repetir la tabla derivada.

Las dos palabras clave:

Por defecto, no toques ninguna de las dos. El comportamiento automático acierta casi siempre. Se usan cuando EXPLAIN demuestra que se está equivocando.

Y el mito que hay que desmontar: "los CTE son lentos" era cierto hasta PostgreSQL 11 y dejó de serlo en la 12. En SQL Server nunca fue cierto: allí siempre se han incorporado.

5. Lo que un CTE no es

Y algo que sí es, y sorprende: un CTE puede modificar datos.

WITH borrados AS (
    DELETE FROM tabla WHERE condicion RETURNING *
)
INSERT INTO historico SELECT * FROM borrados;

Mover filas de una tabla a otra en una sola sentencia atómica. Esto es exclusivo de PostgreSQL y se ve en upsert-y-returning.

6. Errores comunes

1. Repetir WITH en cada CTE. Va una sola vez; los demás se separan por coma.
2. Referenciar un CTE definido después. Solo hacia atrás, salvo RECURSIVE.
3. Esperar que sobreviva a la consulta. Si lo necesitas en varias, es una vista o una tabla temporal.
4. Poner MATERIALIZED "por si acaso". Puedes bloquear que el filtro se empuje dentro y multiplicar el trabajo.
5. Encadenar ocho CTE de una línea cada uno. El nombre debe aportar significado; paso1, paso2, paso3 no aportan nada.
6. Usar un CTE donde bastaba un WHERE. Nombrar un paso trivial añade ruido.
7. Creer que un CTE materializado se puede indexar. No: si el volumen intermedio es grande y hay que filtrarlo mucho, lo que quieres es una tabla temporal.

7. Diferencias con SQL Server

Dos avisos al portar:

• En SQL Server, WITH puede confundirse con las table hints si la sentencia anterior no cerró con ;. Por eso se ve tanto ;WITH al principio. En PostgreSQL no hace falta.
• SQL Server siempre incorpora el CTE, no lo materializa nunca. Un CTE caro usado tres veces se ejecuta tres veces. Es lo contrario del comportamiento por defecto de PostgreSQL cuando hay varias referencias — y es una diferencia de rendimiento real, no de sintaxis.

8. Ejercicios

1. Reescribe con CTE la consulta de tablas derivadas que calcula media, máximo y mínimo de productos por categoría.
2. Con dos CTE: productos por categoría y facturación por categoría, unidos en un solo informe.
3. Clasifica los pedidos en pequeño (<500), mediano (<2000) y grande, y cuenta cuántos hay de cada tipo con su facturación.
4. Los clientes que están por encima de la media en pedidos y también en facturación.
5. Explica qué hace MATERIALIZED en esta consulta y si merece la pena: sql WITH t AS MATERIALIZED (SELECT * FROM orders) SELECT count(*) FROM t WHERE ship_country = 'Brazil';

9. Pregunta de negocio

Dirección quiere el informe trimestral de 1997 y 1998: por trimestre, número de pedidos, clientes activos, facturación, ticket medio y la variación porcentual respecto al trimestre anterior.

La última columna es la que decide la estructura.