CTE en SQLite: la cláusula WITH paso a paso

Una CTE es una subconsulta con nombre

Una CTE (Common Table Expression) es básicamente una subconsulta a la que le pones un nombre. En vez de anidar un SELECT dentro de otro SELECT, la defines arriba con WITH, le asignas un nombre, y luego la usas en la consulta principal como si fuera una tabla más.

La estructura siempre es la misma:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Léelo de arriba abajo: primero se construye un resultado con nombre llamado customer_totals y luego se consulta ese resultado. La CTE se comporta como una vista temporal que solo existe mientras dura esta sentencia.

La misma consulta sin CTE

Aquí tienes la misma lógica escrita como subconsulta, para que veas qué es lo que reemplaza la CTE:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

La misma respuesta. Pero fíjate en el orden de lectura: tu vista tiene que meterse dentro de los paréntesis, entender qué se está calculando y luego volver a salir. La versión con CTE se lee en el mismo orden en que ocurre el trabajo: defines el resultado intermedio y después lo usas. En una consulta pequeña da igual. Pero en una consulta con tres o cuatro pasos, marca la diferencia entre un código que puedes leer de un vistazo y otro que tienes que descifrar.

Varias CTEs en una misma consulta

Puedes encadenar varias CTEs separándolas con comas. Cada una puede hacer referencia a las anteriores, así que vas armando una especie de pipeline de pasos con nombre:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Un solo WITH, seguido de las definiciones de CTE separadas por comas. La segunda CTE (big_spenders) lee de la primera (customer_totals) como si fuera una tabla cualquiera. El SELECT principal va después de la última definición de CTE.

Un error típico: poner WITH otra vez delante de la segunda CTE. No lo hagas, es un error de sintaxis. Un único WITH cubre todas.

Referenciar una CTE varias veces

Aquí es donde las CTEs le sacan ventaja de verdad a las subconsultas. Si necesitas el mismo resultado intermedio en dos sitios, una CTE te permite calcularlo una vez y reutilizarlo las veces que haga falta:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

La CTE se referencia dos veces: una para calcular el promedio y otra como fuente principal. Sin la CTE, tendrías que duplicar la consulta con GROUP BY, y cualquier cambio habría que hacerlo en dos sitios.

CTEs con INSERT, UPDATE y DELETE

Las CTEs no son exclusivas del SELECT. Puedes anteponer una cláusula WITH a un INSERT, UPDATE o DELETE para reutilizar una subconsulta con nombre dentro de una operación de escritura:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

La CTE describe qué filas marcar. El INSERT ... SELECT la usa como origen. El mismo truco funciona con DELETE FROM ... WHERE id IN (SELECT id FROM cte) para borrados por etapas cuando la lógica de selección es enrevesada.

Cuándo usar una CTE en SQLite

Algunas reglas prácticas:

• La consulta tiene más de un paso lógico. Agregar, luego filtrar sobre el agregado y después hacer un join con el resultado: eso es un pipeline, y una CTE por paso lo vuelve legible.
• Si no, repetirías la misma subconsulta. Defínela una vez y referénciala dos veces.
• La subconsulta merece un nombre. Si ibas a poner un comentario encima de la subconsulta para explicar qué representa, el nombre de la CTE es ese comentario, y la sintaxis te obliga a escribirlo.
• Vas a escribir una consulta recursiva. Eso solo es posible con WITH RECURSIVE, tema que veremos en la siguiente página.

Cuándo no vale la pena:

• Una subconsulta simple usada en un único lugar. WHERE id IN (SELECT id FROM ...) está bien tal cual.
• Consultas críticas en rendimiento donde ya comprobaste que insertar la lógica directamente (inline) ayuda. SQLite suele tratar una CTE como barrera de optimización con menos rigidez que otras bases de datos, pero en rutas calientes conviene verificarlo con EXPLAIN QUERY PLAN.

Un ejemplo completo

Juntando todo: un pequeño reporte que encuentra el pedido más grande de cada cliente y lo compara con su promedio:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Dos CTEs, cada una con una única responsabilidad. El SELECT principal se encarga de dar formato al resultado. Puedes leer la consulta de arriba abajo y entender cada paso por separado: justo de eso van las CTEs.

Siguiente paso: CTEs recursivas

Hasta aquí hemos visto CTEs normales: subconsultas con nombre que se evalúan una sola vez. SQLite también admite WITH RECURSIVE, donde una CTE se referencia a sí misma para recorrer jerarquías, generar secuencias o atravesar grafos. Eso lo veremos en la siguiente página.