Un informe por pasos: WITH

En resumen: WITH (common table expression, CTE) le da un nombre a cada paso de una consulta para que se lea de arriba abajo y un paso pueda usarse más de una vez.

Problema

Un informe de dentro hacia fuera

El dueño quiere una lista de los clientes que han gastado más de 100 € (sin los pedidos cancelados), con su cantidad de pedidos. Con subconsultas en FROM ya puedes hacerlo:

SQLbase de datos: tienda

SELECT c.name, t.orders, t.spent
FROM (
  SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM (
    SELECT o.id, 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 <> 'cancelled'
    GROUP BY o.id, o.customer_id
  ) AS ot
  GROUP BY customer_id
) AS t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > 100
ORDER BY t.spent DESC;

SELECT c.name, t.orders, t.spent FROM ( SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent FROM ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) AS ot GROUP BY customer_id ) AS t JOIN customers c ON c.id = t.customer_id WHERE t.spent > 100 ORDER BY t.spent DESC;

▶ EjecutarRestaurar la consultaBase nueva

El resultado es correcto. Pero intenta decir qué hace la consulta: tienes que empezar por los paréntesis más internos y avanzar hacia fuera. Dentro de un mes ni tú mismo la reconocerás.

La idea

WITH

WITH le da un nombre a un paso, antes de la consulta principal:

SQLbase de datos: tienda

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT * FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT * FROM order_totals;

▶ EjecutarRestaurar la consultaBase nueva

order_totals es el valor de cada pedido que no está cancelado: 53 filas. Después de WITH, el nombre se usa como una tabla. Un paso con nombre así se llama CTE (common table expression).

Puede haber varios pasos, separados por comas. Cada uno ve los pasos anteriores:

SQLbase de datos: tienda

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
),
customer_totals AS (
  SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.name, ct.orders, ct.spent
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
WHERE ct.spent > 100
ORDER BY ct.spent DESC;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ), customer_totals AS ( SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent FROM order_totals GROUP BY customer_id ) SELECT c.name, ct.orders, ct.spent FROM customer_totals ct JOIN customers c ON c.id = ct.customer_id WHERE ct.spent > 100 ORDER BY ct.spent DESC;

▶ EjecutarRestaurar la consultaBase nueva

El mismo informe del principio, pero se lee de arriba abajo: el valor de cada pedido, luego un total por cada cliente, luego solo los que pasan de 100 €. Salen siete clientes, encabezados por Maria (375,70 € en 12 pedidos).

Pruébalo

Pedidos por encima del promedio

Muestra el número y el valor de los pedidos (sin los cancelados) cuyo valor está por encima del valor promedio de un pedido. El campo ya tiene el paso order_totals.

SQLbase de datos: tienda

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT id, ROUND(total, 2) AS total
FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT id, ROUND(total, 2) AS total FROM order_totals;

Comprobar▶ Ejecutar

Míralo

Cuánto vive el nombre

Un nombre de WITH vale solo para la consulta de la que forma parte. Aquí hay dos consultas: la primera cuenta los pedidos, la segunda vuelve a pedir order_totals:

SQLbase de datos: tienda

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT COUNT(*) FROM order_totals;

SELECT * FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT COUNT(*) FROM order_totals; SELECT * FROM order_totals;

▶ EjecutarRestaurar la consultaBase nueva

1. ¿Por qué la segunda consulta no encuentra order_totals?

Porque un nombre de WITH vive solo en la consulta de la que forma partePorque la primera consulta borró order_totalsPorque falta una coma

Comprobar

WITH no crea una tabla en la base de datos. Si necesitas un paso en muchos informes, para eso están las vistas (VIEW), que conoceremos en el módulo sobre el esquema.

La idea

Cuándo WITH, cuándo una subconsulta

El resultado es el mismo. La diferencia es para quien lee:

• una condición corta como price > (SELECT AVG(price) FROM products) es más clara en su sitio, como subconsulta;
• un informe de varios pasos es más claro con WITH: cada paso tiene un nombre que dice qué es;
• un paso que necesitas dos veces se escribe una vez.

También ayuda al buscar un error: cambia el último SELECT por SELECT * FROM order_totals y verás qué da cada paso.

Más precisamente, para curiosos

Si la base de datos ejecuta cada paso por separado o los fusiona en una sola consulta lo decide la base de datos. En PostgreSQL antes de la versión 12 cada WITH se ejecutaba por separado, y desde la versión 12 la base puede fusionarlo con la consulta principal. Si alguna vez una consulta con WITH es más lenta que la misma con subconsultas, la razón suele estar ahí.

Ejercicio

Tres tareas

1. El equipo de ventas quiere los ingresos por ciudad, para decidir dónde anunciarse.

Muestra la ciudad y los ingresos de los pedidos de sus clientes (sin los cancelados), redondeados a dos decimales. Empieza por el paso order_totals.

SQLbase de datos: tienda

WITH order_totals AS (
  SELECT o.id, 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 <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT * FROM order_totals;

WITH order_totals AS ( SELECT o.id, 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 <> 'cancelled' GROUP BY o.id, o.customer_id ) SELECT * FROM order_totals;

Comprobar▶ Ejecutar

2. ¿Qué categorías han aportado más de 200 € (sin los pedidos cancelados)?

Muestra el nombre de la categoría y sus ingresos (la suma de cantidad por precio unitario), solo para las categorías con ingresos de más de 200 €. Haz un paso con los ingresos por category_id y luego únelo con categories.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

3. En el módulo sobre unir tablas hiciste con CROSS JOIN la cuadrícula de cada ciudad y cada subcategoría. Ahora rellénala: los ingresos de cada par, con 0 donde no hay ventas.

Muestra la ciudad, el nombre de la subcategoría (columna category) y los ingresos (columna revenue) de los clientes de esa ciudad en esa subcategoría (sin los pedidos cancelados), redondeados a dos decimales. Para los pares sin ventas los ingresos son 0. El campo ya tiene la cuadrícula.

SQLbase de datos: tienda

WITH grid AS (
  SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category
  FROM customers cu
  CROSS JOIN categories cat
  WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL
)
SELECT * FROM grid;

WITH grid AS ( SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category FROM customers cu CROSS JOIN categories cat WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL ) SELECT * FROM grid;

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. ¿Cómo se escriben dos pasos en un solo WITH?

WITH a AS (…) WITH b AS (…) SELECT …WITH a AS (…); b AS (…); SELECT …WITH a AS (…), b AS (…) SELECT …

2. ¿Qué le hace WITH order_totals AS (…) a la base de datos?

Crea una tabla order_totals que se quedaNada en la base de datos: le pone nombre a un resultado solo para esta consultaGuarda el resultado hasta el final del día

3. ¿Qué te da WITH que no te da una subconsulta en FROM?

Los pasos se leen de arriba abajo, y un paso se puede usar varias vecesResultados más correctosFunciona sin GROUP BY

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Muchas tablas, y una tabla unida consigo misma · UNION, INTERSECT, EXCEPT y CROSS JOIN · Subconsultas · Subconsultas correlacionadas
• Continúa en: Árboles y secuencias: WITH RECURSIVE · Funciones de ventana · Buscar por significado · La panadería, etapa 3: las preguntas
• Ejercicios sobre el tema: Subconsultas y ventanas