Tablas derivadas

1. Qué es

Una tabla derivada es una subconsulta puesta en el FROM. El resultado de un SELECT se comporta como si fuera una tabla más.

SELECT ...
FROM (SELECT ...) AS t     ← esto es una tabla derivada
WHERE t.columna > ...

Es la tercera forma de subconsulta del bloque:

Y resuelve un problema muy concreto: hay cosas que SQL no deja hacer en un solo paso.

• Filtrar por un alias del SELECT → el WHERE no lo ve (orden-logico-de-ejecucion).
• Agregar sobre un agregado → avg(count(*)) da error.
• Filtrar por una función de ventana → el WHERE se ejecuta antes que las ventanas.

Todas se resuelven igual: haz el primer paso en una tabla derivada y trabaja sobre su resultado.

2. Sintaxis

SELECT t.a, t.b
FROM (SELECT algo AS a, otra_cosa AS b FROM tabla WHERE ...) AS t
WHERE t.a > 10;

• El alias (AS t) es obligatorio en el estándar SQL. PostgreSQL 16+ lo permite omitir, pero escríbelo siempre: sin él la consulta no es portable y las columnas no se pueden prefijar.
• Las columnas de dentro tienen que tener nombre para poder usarlas fuera. Un count(*) sin alias se llama count, y eso funciona pero es feo.
• Se pueden anidar y unir con JOIN como cualquier tabla.

3. Ejemplos sobre Northwind

Filtrar por un alias que el WHERE no ve:

SELECT *
FROM (SELECT category_id,
             count(*)                 AS productos,
             round(avg(unit_price),2) AS precio_medio
      FROM products
      GROUP BY category_id) AS t
WHERE t.productos >= 12
ORDER BY t.precio_medio DESC;

 category_id | productos | precio_medio
-------------+-----------+--------------
           1 |        12 |        37.98
           3 |        13 |        25.16
           2 |        12 |        22.85
           8 |        12 |        20.68

Aquí el HAVING count(*) >= 12 habría hecho lo mismo. La tabla derivada gana cuando el filtro es sobre algo que ni WHERE ni HAVING pueden ver — un alias calculado, una ventana, una expresión compuesta.

3.1 La agregación de dos niveles

Esto no se puede escribir directamente:

SELECT avg(count(*)) FROM orders GROUP BY customer_id;   -- no es lo que parece

Y esto sí:

SELECT round(avg(pedidos),2) AS media, max(pedidos) AS maximo
FROM (SELECT customer_id, count(*) AS pedidos
      FROM orders
      GROUP BY customer_id) AS t;

 media | maximo
-------+--------
  9.33 |     31

"La media de pedidos por cliente" es una media de conteos: primero se cuenta por cliente, después se promedia. Son dos agregaciones a dos granularidades distintas, y la tabla derivada es lo que las separa.

El ticket medio, bien calculado:

SELECT round(avg(facturacion),2) AS ticket_medio,
       round(max(facturacion),2) AS mayor,
       round(min(facturacion),2) AS menor
FROM (SELECT order_id, sum(unit_price * quantity * (1 - discount)) AS facturacion
      FROM order_details
      GROUP BY order_id) AS t;

 ticket_medio |  mayor   | menor
--------------+----------+-------
      1525.05 | 16387.50 | 12.50

1.525,05 — el mismo número del tema funciones-de-agregacion, pero ahora con su dispersión al lado: pedidos de 12,50 y de 16.387,50. Un rango de 1.300 a 1. Esa media no describe a ningún pedido real, y solo se ve porque la tabla derivada dejó los totales por pedido a mano.

Dos niveles con desglose:

SELECT anio, round(avg(pedidos_mes),1) AS pedidos_mes_medio
FROM (SELECT extract(year FROM order_date)::int AS anio,
             date_trunc('month', order_date)    AS mes,
             count(*)                           AS pedidos_mes
      FROM orders
      GROUP BY 1, 2) AS t
GROUP BY anio
ORDER BY anio;

 anio | pedidos_mes_medio
------+-------------------
 1996 |              25.3
 1997 |              34.0
 1998 |              54.0

El negocio se duplicó en dos años, y esta es la forma honesta de verlo: 1996 y 1998 están incompletos, así que comparar totales anuales engaña (152 / 408 / 270) y comparar medias mensuales no. Es el mismo aviso del gráfico que se desploma en el último periodo.

3.2 Filtrar por una función de ventana

Las ventanas se calculan después del WHERE, así que no se pueden filtrar donde se calculan. La tabla derivada es la salida:

SELECT t.pais, t.pedidos
FROM (SELECT ship_country AS pais,
             count(*)     AS pedidos,
             rank() OVER (ORDER BY count(*) DESC) AS puesto
      FROM orders
      GROUP BY 1) AS t
WHERE t.puesto <= 3;

  pais   | pedidos
---------+---------
 Germany |     122
 USA     |     122
 Brazil  |      83

Tres filas para "el top 3", y Alemania y EE. UU. empatan a 122. rank() da el mismo puesto a los empatados, así que aquí el corte es honesto. Con LIMIT 3 habría salido uno de los dos arbitrariamente. Las ventanas están en el bloque 7 (ranking-row-number-rank-ntile); este patrón —ventana dentro, filtro fuera— es el que más se repite de todo SQL analítico.

3.3 Unir dos agregados

SELECT count(*)
FROM (SELECT customer_id FROM orders GROUP BY customer_id HAVING count(*) > 10)   AS a
JOIN (SELECT customer_id FROM orders GROUP BY customer_id HAVING sum(freight) > 1000) AS b
  USING (customer_id);

 count
-------
    15

15 clientes cumplen las dos condiciones. Es el patrón de joins-multiples-y-granularidad: agregar por separado y unir los agregados. Aquí las dos condiciones cabrían en un solo HAVING, pero en cuanto vengan de tablas distintas esta es la única forma.

4. El problema: se leen del revés

SELECT t2.pais, t2.pedidos
FROM (SELECT t1.pais, t1.pedidos
      FROM (SELECT ship_country AS pais, count(*) AS pedidos
            FROM orders GROUP BY 1) AS t1
      WHERE t1.pedidos > 20) AS t2
ORDER BY t2.pedidos DESC;

Funciona. Y hay que leerla de dentro hacia fuera, mientras que el ojo va de arriba abajo. Con dos niveles ya cuesta; con cuatro es ilegible.

Esa es la única razón por la que existe el tema siguiente: un CTE es una tabla derivada con nombre, escrita en el orden en que se piensa (cte-with).

-- Lo mismo, legible
WITH por_pais AS (
    SELECT ship_country AS pais, count(*) AS pedidos FROM orders GROUP BY 1
)
SELECT pais, pedidos FROM por_pais WHERE pedidos > 20 ORDER BY pedidos DESC;

Cuándo sigue mereciendo la pena una tabla derivada: cuando es corta, se usa una sola vez y está dentro de un FROM que ya se lee bien. Para todo lo demás, CTE.

5. Errores comunes

1. Olvidar el alias. PostgreSQL 16+ lo perdona; el estándar y SQL Server no. Escríbelo.
2. Columnas sin nombre dentro. No se pueden referenciar bien desde fuera.
3. Anidar tres o más niveles. Legal e ilegible. Pasa a CTE.
4. Repetir la misma tabla derivada dos veces en la misma consulta. Se ejecuta dos veces y hay que mantenerla en dos sitios. Es la señal exacta de que quieres un CTE.
5. Usar una tabla derivada donde bastaba HAVING. Si el filtro es sobre una agregación del mismo nivel, HAVING es más corto.
6. Filtrar dentro cuando podías filtrar fuera, o al revés. Filtra lo antes posible: reducir filas dentro de la derivada es lo que abarata todo lo de fuera.
7. Creer que una tabla derivada "materializa" el resultado. No es una tabla temporal: PostgreSQL suele fusionarla con la consulta externa. No hay coste extra por usarla, ni ahorro por reutilizarla.

6. Diferencias con SQL Server

La única diferencia práctica es el alias, y como SQL Server lo exige, escribirlo siempre hace el código portable sin coste.

El punto importante: una tabla derivada normal no puede ver las columnas de la consulta externa. Esto falla en los dos motores:

SELECT p.product_name, t.media
FROM products p,
     (SELECT avg(unit_price) AS media FROM products WHERE category_id = p.category_id) t;
                                                                        ↑ p no existe aquí

Para eso está LATERAL (o APPLY), y es el tema lateral.

7. Ejercicios

1. La media, el máximo y el mínimo de productos por categoría.
2. Los clientes cuyo ticket medio supera los 2.000, con su número de pedidos.
3. El número medio de líneas por pedido.
4. Los tres empleados con más facturación, usando rank() dentro de una tabla derivada.
5. Explica por qué esto da error y arréglalo: sql SELECT category_id, count(*) AS productos FROM products WHERE productos > 10 GROUP BY category_id;

8. Pregunta de negocio

Dirección quiere saber si el negocio depende demasiado de unos pocos clientes: qué porcentaje de la facturación total aportan los 10 mayores.