Subconsultas, vitas y CTEs

Hasta ahora ya dominas:

• CRUD
• JOINs
• Relaciones y claves foráneas

Con esto puedes resolver un enorme número de problemas.

Pero tarde o temprano llegarás a consultas que:

• Necesitan varios pasos intermedios
• Requieren filtrar basándose en resultados calculados
• Se vuelven demasiado difíciles de leer en una sola SELECT, o necesitan reutilizar una consulta compleja en varios lugares

Ahí es donde entran en juego: las subconsultas, las VISTAS, las CTEs

SUBCONSULTAS — Consultas dentro de consultas

Una subconsulta es simplemente una SELECT que aparece dentro de otra SELECT.

Sirve para: obtener datos intermedios, filtrar basándose en resultados calculados.

La pregunta que responde una subconsulta es:

“¿Cómo puedo usar el resultado de esta consulta como parte de otra consulta mayor?”

1.1 Creemos un escenario simple

Crea una tabla de productos:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

Inserta datos:

INSERT INTO products (name, price)
VALUES
 ('Teclado', 50),
 ('Ratón', 25),
 ('Monitor', 200),
 ('Altavoces', 40),
 ('Silla Gaming', 150);

1.2 Subconsulta básica en WHERE

Ejemplo:

“Quiero todos los productos cuyo precio sea mayor que la media de los precios”.

Imagina que tienes una tabla llamada products dentro de una base de datos que estás abriendo en DB Browser for SQLite. Esta tabla contiene una lista de productos con sus precios.

El planteamiento es muy común:

Quiero obtener todos los productos cuyo precio sea mayor que la media de todos los precios.

Sin una subconsulta tendrías que ejecutar dos consultas por separado, es decir en dos pasos manuales. Sin embargo, con una subconsulta, SQLite puede calcular ese valor por ti dentro de la propia consulta, sin necesidad de que tú hagas pasos adicionales.

Ejemplo en DB Browser for SQLite

Si quieres seguirlo, crea una base de datos de prueba y una tabla de productos:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

Inserta algunos datos de ejemplo:

INSERT INTO products (name, price) VALUES
('Teclado mecánico', 120),
('Ratón inalámbrico', 45),
('Monitor 24 pulgadas', 160),
('Alfombrilla grande', 20),
('Auriculares gamer', 120),
('Webcam HD', 70);

Ahora tienes una tabla real con precios variados.

Paso previo: calcular la media manualmente

En DB Browser ejecutas:

SELECT AVG(price) FROM products;

Supongamos que devuelve un valor como 89.1666…

El funcionamiento sería este:

1. La base de datos calcula la media de todos los precios.
2. Ese número lo usarías después para filtrar qué productos superan ese precio.

Manualmente, tendrías que volver a escribir:

SELECT *
FROM products
WHERE price > 89.1666;

Uso de una subconsulta en WHERE

Ahora verás la ventaja principal: puedes pedirle a SQLite que haga el cálculo dentro de la misma consulta. Así no tienes que escribir la media ni calcular nada por separado.

Consulta final:

SELECT *
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

SQLite ejecuta primero la subconsulta de dentro de los paréntesis:

SELECT AVG(price) FROM products;

Ese valor, que será único, se incrusta como si fuera un número dentro de la comparación price > valor.

Después, SQLite evalúa la condición para cada fila de la tabla productos.

Qué devuelve esta consulta

En nuestro caso devolverá todos los productos con precio superior a la media. Con tus datos, seguramente obtengas algo parecido a:

• Teclado mecánico → 120
• Monitor 24 pulgadas → 160
• Auriculares gamer → 120

(Dependiendo del conjunto de datos exacto).

Es decir, la consulta te está dando todos los productos “caros”, usando como referencia la media real calculada en ese momento.

Por qué esto es importante

La gran ventaja de usar subconsultas es que los valores se calculan dinámicamente según el estado actual de la base de datos. Si mañana actualizas precios o añades productos, ya no tendrás que recalcular manualmente la media; la consulta seguirá funcionando perfectamente.

Otra ventaja es conceptual: encapsulas una operación lógica completa dentro de una única consulta, y esto simplifica el mantenimiento, reduce errores y evita cálculos manuales.

1.3 Subconsulta en SELECT

1.3 Subconsulta en SELECT

Explicación ampliada, sencilla y con un ejemplo real ejecutado en DB Browser for SQLite

En este caso, seguimos trabajando con la misma tabla products. La diferencia es que ahora la subconsulta no aparece en el WHERE, sino como una columna más del resultado.

La idea es esta:

Quiero listar todos los productos, pero además mostrar en cada fila cuál es el precio medio general de todos los productos.

Sin subconsultas, esto no sería cómodo porque tendrías que calcular la media en otra consulta, copiarla y luego escribirla manualmente. Pero SQLite te permite calcular esa media dentro del propio SELECT.

Ejemplo directo en DB Browser

Usa la tabla de antes:

SELECT
  name,
  price,
  (SELECT AVG(price) FROM products) AS global_avg
FROM products;

Cuando ejecutas esto en DB Browser, verás algo así:

name	price	global_avg
Teclado mecánico	120	89.1666
Ratón inalámbrico	45	89.1666
Monitor 24 pulgadas	160	89.1666
Alfombrilla grande	20	89.1666
Auriculares gamer	120	89.1666
Webcam HD	70	89.1666

La columna global_avg será idéntica en todas las filas porque es una subconsulta que devuelve un único valor.

Qué está ocurriendo realmente

1. SQLite ejecuta la subconsulta (SELECT AVG(price) FROM products), que produce un único número.
2. Ese número se coloca en cada fila como si fuera un valor constante.
3. Así puedes comparar rápidamente precios individuales respecto al precio medio, sin hacer pasos adicionales.

Ventajas

• Es útil cuando necesitas añadir estadísticas globales a cada fila.
• También es perfecto para informes: puedes añadir máximos, mínimos, totales, medias, conteos, etc.
• Si un día los precios cambian, esta consulta seguirá funcionando sin tener que modificar nada.

1.4 Subconsulta correlacionada

Una subconsulta correlacionada es distinta porque depende de la fila actual. Esto quiere decir que se ejecuta tantas veces como filas tiene la tabla externa.

Ejemplo conceptual:

• Para cada producto quiero calcular la media de todos los productos más baratos que él.
• Después quiero quedarme solo con los productos cuyo precio sea mayor que esa media.

La consulta es:

SELECT *
FROM products p
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE price < p.price
);

Qué significa esto paso a paso

Supón que estamos analizando la fila del Ratón inalámbrico (precio 45):

1. La subconsulta dice:

   “Dame la media de los productos cuyo precio sea menor que 45”.

2. Buscamos productos más baratos que 45, que podría ser solo la Alfombrilla (20).

   Media: 20.

3. Evaluamos si 45 > 20.

   Sí, por tanto el ratón pasa el filtro.

Ahora supongamos que estamos en el Monitor 24 pulgadas (160):

1. Productos más baratos: todos excepto él mismo.
2. Media de precios menores que 160: por ejemplo serie de valores como 120, 45, 20, 120, 70.
3. Si 160 es mayor que esa media, pasa el filtro.

Qué devuelve normalmente esta consulta

Devuelve productos cuyo precio es “alto”, pero no respecto a la media general, sino respecto a la media específica de los productos más baratos que ellos.

En DB Browser verás una lista reducida, normalmente los productos más caros.

Por qué esto es útil

Las subconsultas correlacionadas permiten comparaciones dinámicas basadas en cada fila.

Te dan una potencia enorme cuando necesitas criterios individualizados en lugar de valores globales.

Ejemplos típicos en bases de datos reales:

• Obtener los empleados que cobran más que la media de los que tienen menos experiencia.
• Encontrar los artículos cuyo stock es superior a la media de los artículos de menor demanda.
• Detectar clientes con gasto superior a la media de los clientes con menor facturación.

1.5 Cuándo NO usar subconsultas

Las subconsultas son útiles, pero: muchas veces un JOIN es más rápido, otras veces un CTE es más legible, si la consulta se repite a menudo, una VIEW es mejor

Regla general:

Si la subconsulta empieza a dificultar la lectura, cámbiala por CTE o VIEW.

VISTAS (VIEWS) — Consultas guardadas como tablas virtuales

Una vista es una herramienta muy útil cuando empiezas a trabajar con consultas más largas o repetitivas. Su función principal es permitirte guardar una consulta con un nombre, exactamente igual que si fuera una tabla, pero sin almacenar datos en disco. La vista no contiene información física. Lo único que guarda es una consulta SQL internamente.

Cada vez que haces un SELECT sobre la vista, SQLite ejecuta automáticamente la consulta original y te muestra el resultado actualizado según los datos reales de las tablas.

Esto te permite:

• Evitar repetir consultas largas
• Mantener organizado tu proyecto
• Simplificar informes
• Proteger cierta lógica de negocio
• Crear “tablas virtuales” que siempre están sincronizadas con los datos reales

Qué problema resuelven las vistas

Imagina que tienes una consulta compleja para filtrar productos caros, quizá con varias condiciones, subconsultas, JOINs, etc. Si necesitas usarla varias veces, sería incómodo repetir todo el SQL. Además, repetir consultas aumenta la probabilidad de errores si un día decides cambiar algo.

Una vista permite almacenar esa lógica y reutilizarla siempre, usando un nombre sencillo.

2.1 Crear una vista básica

Ejemplo real en DB Browser for SQLite

Supón que tienes una tabla products con este tipo de datos:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

Inserta algunos productos, por ejemplo:

INSERT INTO products (name, price) VALUES
('Monitor 24 pulgadas', 200),
('Silla Gaming', 150),
('Ratón inalámbrico', 45),
('Alfombrilla grande', 20),
('Teclado mecánico', 120);

Ahora quieres una vista que muestre solo los productos cuyo precio supera 100.

Podrías escribir cada vez:

SELECT *
FROM products
WHERE price > 100;

Pero si lo vas a usar a menudo, es mejor guardar esa consulta como una vista:

CREATE VIEW expensive_products AS
SELECT *
FROM products
WHERE price > 100;

Con esto, SQLite crea una tabla virtual llamada expensive_products. La clave es que no guarda datos reales, sino una referencia a esa consulta.

Cómo consultar la vista

En DB Browser simplemente haces:

SELECT * FROM expensive_products;

Resultado típico:

name	price
Monitor 24 pulgadas	200
Silla Gaming	150
Teclado mecánico	120

Observa que:

• La vista funciona como si fuera una tabla
• La tabla products sigue siendo la fuente real
• Si cambias el precio de un producto en products, la vista se actualizará automáticamente al hacer un nuevo SELECT

Vista actualizada en tiempo real

Por ejemplo, en DB Browser cambia el precio del teclado:

UPDATE products
SET price = 90
WHERE name = 'Teclado mecánico';

Ahora vuelve a consultar la vista:

SELECT * FROM expensive_products;

El teclado desaparecerá porque ya no cumple la condición price > 100.

Esto demuestra que la vista no tiene datos propios. Siempre refleja el estado actual de las tablas.

Cuándo usar vistas en proyectos reales

Las vistas son muy útiles en situaciones como:

• Informes que se generarán muchas veces
• Consultas complejas con múltiples JOINs
• Filtros que se usan repetidamente (productos caros, usuarios activos, ventas recientes)
• Simplificación de la lógica para otros usuarios o módulos de la aplicación
• Preparar datos para herramientas externas (BI, informes, dashboards)

Un ejemplo práctico real:

Puedes crear una vista que muestre ventas del último mes. Otro módulo de tu programa, quizá escrito en Node.js, puede simplemente hacer SELECT sobre esa vista sin preocuparse por fechas, condiciones ni cálculos.

Cómo visualizar vistas en DB Browser for SQLite

En la interfaz gráfica:

1. Abre la base de datos.
2. En la parte izquierda, en el panel “Database Structure”, verás secciones para tablas y vistas.
3. Haz clic en “Views” y verás expensive_products.
4. Puedes hacer clic derecho y seleccionar “Modify View” para ver o editar la consulta original.

2.2 Modificar y eliminar vistas

Una vez que entiendes cómo crear vistas, es normal que quieras cambiarlas o eliminarlas. SQLite permite ambas cosas, pero es importante saber cómo funciona cada caso.

Cómo eliminar una vista

Si quieres borrar una vista porque ya no la necesitas o vas a crear otra con el mismo nombre, puedes usar:

DROP VIEW expensive_products;

Esto elimina la vista del esquema, pero no afecta a ninguna tabla ni borra datos reales.

En DB Browser:

1. Ve a la pestaña “Execute SQL”.
2. Ejecuta el comando anterior.
3. Revisa en “Database Structure” que la vista desapareció.

Cómo modificar una vista

SQLite no permite modificar una vista directamente con un comando ALTER VIEW.

La forma correcta es:

1. Eliminar la vista
2. Crear una nueva vista con el mismo nombre y la consulta actualizada

Ejemplo:

Primero la borras:

DROP VIEW IF EXISTS expensive_products;

Después la creas de nuevo, por ejemplo con un nuevo criterio de precio:

CREATE VIEW expensive_products AS
SELECT name, price
FROM products
WHERE price >= 120;

Esto te permite mantener el mismo nombre pero cambiando la lógica interna.

2.3 Crear vistas basadas en JOINs

Ejemplo más realista

Las vistas se vuelven verdaderamente útiles cuando encapsulan consultas complejas, especialmente aquellas que combinan varias tablas.

Supongamos que tienes esta estructura:

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  category_id INTEGER,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

Ejemplo de categorías:

INSERT INTO categories (name) VALUES
('Periféricos'),
('Monitores'),
('Mobiliario');

Ejemplo de productos:

INSERT INTO products (name, price, category_id) VALUES
('Monitor 24 pulgadas', 200, 2),
('Ratón inalámbrico', 45, 1),
('Silla Gaming', 150, 3),
('Teclado mecánico', 120, 1);

Ahora quieres una vista que muestre productos con su categoría ya resuelta con un JOIN, lista para usar en informes o aplicaciones:

CREATE VIEW products_with_category AS
SELECT
  p.id,
  p.name,
  p.price,
  c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id;

Al consultar:

SELECT * FROM products_with_category;

Obtendrás algo así:

id	name	price	category
1	Monitor 24 pulgadas	200	Monitores
2	Ratón inalámbrico	45	Periféricos
3	Silla Gaming	150	Mobiliario
4	Teclado mecánico	120	Periféricos

Ventajas de una vista basada en JOIN

• No tienes que recordar cómo se unen las tablas
• Puedes reutilizar la vista en informes
• Se simplifica mucho el código en Node.js u otros lenguajes
• Si una categoría cambia de nombre, los resultados se actualizan solos

2.4 Vistas con cálculos y agregaciones

Ejemplo práctico común

Imagina que quieres un informe de precios por categoría. Podrías crear una vista:

CREATE VIEW avg_price_by_category AS
SELECT
  c.name AS category,
  AVG(p.price) AS avg_price
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.id;

Luego consultas:

SELECT * FROM avg_price_by_category;

Obtendrás algo como:

category	avg_price
Periféricos	82.5
Monitores	200
Mobiliario	150

Esta vista resume la información sin que tengas que escribir GROUP BY cada vez.

2.5 Encadenar vistas (vistas que usan otras vistas)

Supongamos que ya tienes la vista:

products_with_category

Puedes crear otra basada en ella:

CREATE VIEW expensive_products_with_category AS
SELECT *
FROM products_with_category
WHERE price > 100;

Consulta:

SELECT * FROM expensive_products_with_category;

Resultado:

name	price	category
Monitor 24 pulgadas	200	Monitores
Silla Gaming	150	Mobiliario
Teclado mecánico	120	Periféricos

Conclusión del bloque

Las vistas funcionan como “consultas guardadas” que puedes tratar como tablas virtuales. No almacenan datos, solo la lógica de la consulta. Son especialmente útiles para:

• Ordenar y simplificar tu SQL
• Reducir repetición
• Crear informes reutilizables
• Encapsular JOINs y cálculos avanzados
• Preparar datos para interfaces externas

2.6 Vistas para simplificar informes avanzados

En proyectos reales es muy común que necesites generar informes que combinan datos de varias tablas, cálculos, filtros por fecha, agregaciones y ordenaciones. Esos informes suelen tener consultas largas que no quieres repetir continuamente.

Una vista permite encapsular esa lógica compleja para que tu aplicación, o incluso tú mismo, pueda leer los datos de forma más clara.

Supongamos que amplías tu base de datos con una tabla de ventas:

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  total REAL NOT NULL,
  sale_date TEXT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Insertamos algunos datos de ejemplo:

INSERT INTO sales (product_id, quantity, total, sale_date) VALUES
(1, 2, 400, '2024-02-01'),
(3, 1, 150, '2024-02-03'),
(4, 1, 120, '2024-02-04'),
(2, 4, 180, '2024-02-05');

Ahora quieres un informe que muestre:

Producto, categoría, cantidad, total, precio unitario y fecha.

Consulta sin vista (larga):

SELECT
  p.name AS product,
  c.name AS category,
  s.quantity,
  s.total,
  s.sale_date,
  p.price
FROM sales s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id;

Si conviertes esto en una vista:

CREATE VIEW sales_report AS
SELECT
  p.name AS product,
  c.name AS category,
  s.quantity,
  s.total,
  s.sale_date,
  p.price
FROM sales s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id;

Luego solo tienes que consultar:

SELECT * FROM sales_report;

Esto simplifica enormemente el trabajo diario y evita repetir código complejo.

2.7 Vistas parametrizadas: por qué no existen, pero cómo simularlas

SQLite no permite vistas con parámetros.

Es decir, no puedes crear una vista como:

CREATE VIEW ventas_por_mes(mes) AS ...

Eso no existe en SQLite.

Sin embargo, sí puedes simularlo combinando vistas y filtros:

Ejemplo: creamos una vista con todas las ventas detalladas:

CREATE VIEW sales_detailed AS
SELECT
  s.*,
  p.name AS product,
  c.name AS category
FROM sales s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id;

Luego consultas por mes:

SELECT *
FROM sales_detailed
WHERE sale_date LIKE '2024-02%';

Funciona igual que una vista parametrizada, pero el parámetro lo pones en el WHERE al consultar.

2.8 Vistas como capa de seguridad

En bases de datos grandes, las vistas se usan también como mecanismo de protección. En SQLite esto aplica menos porque no tiene un sistema avanzado de permisos, pero es un concepto importante en SQL profesional.

Ejemplo conceptual:

Supón que tienes una tabla de usuarios con datos sensibles:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT,
  password_hash TEXT
);

Puedes crear una vista que expone solo los campos seguros:

CREATE VIEW public_users AS
SELECT id, name, email
FROM users;

De esta forma, cuando una aplicación externa consulte esta vista, nunca tendrá acceso a password_hash.

Aunque SQLite no gestiona permisos, la vista sigue siendo muy útil para mantener la separación lógica entre:

• datos públicos
• datos internos

2.9 Usar vistas dentro de otra consulta

Una vista también puede participar en consultas más complejas del mismo modo que lo haría una tabla normal.

Ejemplo:

Tienes la vista products_with_category.

Quieres obtener solo los productos caros con categoría incluida.

En lugar de escribir todo de nuevo:

SELECT *
FROM (
  SELECT p.id, p.name, p.price, c.name AS category
  FROM products p
  JOIN categories c ON p.category_id = c.id
)
WHERE price > 100;

Simplemente haces:

SELECT *
FROM products_with_category
WHERE price > 100;

Esto permite construir consultas modulares.

Cada vista actúa como un ladrillo reutilizable en tu diseño de consultas.

2.10 Cómo visualizar y depurar vistas en DB Browser for SQLite

DB Browser ofrece herramientas visuales muy útiles:

1. En el panel izquierdo, abre la sección “Views”.
2. Haz clic en el nombre de la vista para ver la consulta que la define.
3. Si quieres editarla, debes:
   • eliminarla (DROP VIEW nombre)
   • volver a crearla con los cambios

Puedes probar tus vistas directamente en la pestaña “Execute SQL”.

Un consejo útil:

Cuando una vista no funciona como esperas, copia su consulta original, pégala como consulta normal y ejecútala para ver qué ocurre. Esto te ayuda a depurar errores.

2.11 Buenas prácticas al usar vistas

• Asigna nombres descriptivos

  Ejemplo: clientes_activos, ventas_por_categoria, productos_con_stock.

• Úsalas para evitar repetir consultas complejas

  Así reduces errores y haces tu proyecto más mantenible.

• No abuses si la consulta es trivial

  Por ejemplo, una vista que simplemente haga SELECT * FROM products no aporta nada.

• Recuerda que una vista se ejecuta cada vez

  Si la consulta es extremadamente pesada, considera optimizarla o usar índices en las tablas base.

• Úsalas como capa intermedia

  Muy útil en proyectos Node.js para simplificar la lógica del backend.

2.12 Vistas temporales (concepto práctico aunque SQLite no tiene “TEMP VIEW” explícito)

SQLite no dispone de un comando específico como CREATE TEMP VIEW, pero sí puedes crear vistas dentro de una base de datos temporal cargada en memoria. Esto te permite trabajar con vistas que desaparecen al cerrar la sesión.

Cuando abres DB Browser for SQLite y creas una base de datos “en memoria”, todo lo que crees (incluyendo vistas) dejará de existir cuando cierres el programa.

Es útil cuando quieres probar consultas complejas sin afectar a tu base de datos real.

Ejemplo:

1. En DB Browser, selecciona “Create a New Database”, elige “In-Memory Database”.
2. Crea tus tablas y vistas.
3. Trabaja con total libertad.
4. Al cerrar DB Browser, todo desaparece sin dejar rastro.

Esto se usa mucho para experimentar sin miedo a romper nada.

2.13 Vistas materializadas (concepto teórico, no soportado en SQLite pero importante)

En motores como PostgreSQL y Oracle existen las llamadas vistas materializadas.

La diferencia principal:

• Una vista normal se recalcula cada vez que la consultas.
• Una vista materializada guarda los resultados físicamente en disco.

¿Por qué es importante mencionarlas?

Porque cuando tus consultas son extremadamente pesadas, recalcularlas cada vez puede ser costoso. En esos motores, una vista materializada acelera mucho la lectura.

SQLite no soporta vistas materializadas, pero es un concepto que conviene conocer porque en el futuro puede ser relevante si trabajas con bases de datos más grandes.

Para simular una vista materializada en SQLite podrías:

• Crear una tabla física
• Insertar en ella resultados de una consulta
• Refrescarla manualmente cuando quieras actualizarla

Ejemplo:

CREATE TABLE expensive_products_mat AS
SELECT *
FROM products
WHERE price > 100;

Y cuando quieras actualizarla:

DELETE FROM expensive_products_mat;

INSERT INTO expensive_products_mat
SELECT *
FROM products
WHERE price > 100;

Esto solo es necesario en proyectos avanzados, pero es bueno tenerlo presente.

2.14 Vistas como interfaz entre los datos y el backend (Node.js)

En proyectos reales con SQLite, especialmente cuando trabajas con Node.js sin frameworks, las vistas actúan como una capa intermedia muy potente.

Ejemplo práctico:

Supón que en tu backend tienes que mostrar un informe que combina:

• productos
• categorías
• ventas
• cálculos
• filtros por mes

Si lo haces en JavaScript, tendrás que escribir consultas complejas, manejar JOINs, lógica de fechas, etc.

Pero si encapsulas toda esa lógica en una vista llamada por ejemplo monthly_sales_report, tu backend solo necesita:

const rows = await db.all("SELECT * FROM monthly_sales_report;");

Sin preocuparte de las relaciones internas.

Si un día decides cambiar la lógica del informe, solo modificas la vista en SQLite y tu backend sigue funcionando igual.

Esta es una de las razones por las que las vistas son tan usadas en aplicaciones empresariales.

2.15 Caso práctico completo: informe mensual con vista

Vamos a crear un informe mensual real usando vistas.

Supongamos que quieres un informe que calcule:

• nombre del producto
• su categoría
• total vendido en ese mes
• cantidad vendida
• media de precio por venta

Primero, creas la vista:

CREATE VIEW monthly_sales AS
SELECT
  p.name AS product,
  c.name AS category,
  SUM(s.quantity) AS total_quantity,
  SUM(s.total) AS total_revenue,
  AVG(s.total / s.quantity) AS avg_unit_price,
  substr(s.sale_date, 1, 7) AS year_month
FROM sales s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY p.id, year_month;

Ahora, para obtener datos de febrero 2024:

SELECT *
FROM monthly_sales
WHERE year_month = '2024-02';

Resultado típico:

product	category	total_quantity	total_revenue	avg_unit_price	year_month
Monitor 24 pulgadas	Monitores	2	400	200	2024-02
Silla Gaming	Mobiliario	1	150	150	2024-02
Ratón inalámbrico	Periféricos	4	180	45	2024-02

Este informe está disponible con un SELECT muy sencillo, y toda la lógica está encapsulada en la vista.

2.16 Relación entre vistas e índices

Las vistas no almacenan datos, por lo que no puedes crear índices sobre ellas.

Pero sí puedes mejorar el rendimiento indirectamente:

• Añadiendo índices sobre las tablas base
• Ordenando adecuadamente los JOIN
• Simplificando cálculos internos

Por ejemplo, si tu vista hace muchos JOINs basados en product_id, crea un índice en la tabla correspondiente:

CREATE INDEX idx_sales_product_id ON sales(product_id);

Esto acelera notablemente las consultas que usan la vista.

2.17 Ejercicio práctico guiado con vistas

Te propongo un ejercicio que puedes hacer en DB Browser:

Crear una vista que te muestre:

• cada producto
• la cantidad total vendida
• los ingresos generados
• y el porcentaje de ventas respecto al total global de ingresos

Pasos:

1. Calcular el total global:

SELECT SUM(total) FROM sales;

1. Crear la vista:

CREATE VIEW product_sales_summary AS
SELECT
  p.name AS product,
  SUM(s.quantity) AS total_units,
  SUM(s.total) AS revenue,
  SUM(s.total) * 1.0 / (SELECT SUM(total) FROM sales) AS revenue_percentage
FROM sales s
JOIN products p ON s.product_id = p.id
GROUP BY p.id;

1. Consultar la vista:

SELECT * FROM product_sales_summary;

Obtendrás un informe profesional completamente automatizado.

CTEs (Common Table Expressions) — Consultas intermedias limpias y legibles

CTEs (Common Table Expressions)

Una CTE es una forma de crear una tabla temporal que existe únicamente durante la ejecución de una consulta. No se guarda en la base de datos, no ocupa espacio y desaparece al terminar el SELECT. Su objetivo principal es mejorar la claridad de consultas largas o con cálculos intermedios.

Puedes imaginar una CTE como un paso previo al SELECT principal.

Primero se calculan los bloques definidos con WITH, y luego el SELECT final los utiliza como si fueran tablas.

Son perfectas cuando:

• una subconsulta hace el código difícil de leer
• no quieres crear una vista porque sería demasiado permanente
• tienes cálculos intermedios
• quieres dividir la consulta en pasos lógicos

La idea es organizar tu SQL igual que organizarías un programa en funciones.

3.1 Ejemplo básico de CTE

Explicación paso a paso en DB Browser

Quiero obtener productos cuyo precio sea mayor que la media.

Antes lo hicimos con una subconsulta. Ahora lo hacemos con una CTE.

WITH avg_price AS (
  SELECT AVG(price) AS media
  FROM products
)
SELECT *
FROM products
WHERE price > (SELECT media FROM avg_price);

Qué ocurre internamente:

1. SQLite evalúa el CTE avg_pricemedia89.16

   Esto produce una tabla temporal con un único valor: la media.

   Algo así:

2. Ahora el SELECT principal puede usar esa tabla temporal:

   price > (SELECT media FROM avg_price)

3. Se devuelven los productos caros.

La lógica queda separada y visible de un vistazo.

3.2 CTE para separar cálculos intermedios

Explicación clara con ejemplo práctico

Supongamos que quieres:

1. Calcular el precio medio global
2. Calcular para cada producto cuánto se desvía su precio respecto a la media
3. Filtrar los productos que están por encima de esa media

Esto implica dos cálculos distintos. En lugar de mezclarlo todo en una sola consulta confusa, lo ordenamos con CTEs:

WITH
  media AS (
    SELECT AVG(price) AS avg_price
    FROM products
  ),
  enriched AS (
    SELECT
      p.*,
      p.price - (SELECT avg_price FROM media) AS diff
    FROM products p
  )
SELECT *
FROM enriched
WHERE diff > 0;

Significado:

• media calcula una estadística global
• enriched usa esa estadística para enriquecer cada fila con un campo nuevo
• el SELECT final solo se preocupa de filtrar

Este estilo hace que tu SQL sea mucho más legible, sobre todo cuando trabajas con informes o con muchas columnas derivadas.

3.3 CTE con JOINs (caso real)

Ideal para simplificar consultas largas

Supongamos que tienes productos y categorías.

Quieres preparar una tabla limpia que combine ambos datos.

WITH product_data AS (
  SELECT
    p.id,
    p.name,
    p.price,
    c.name AS category
  FROM products p
  JOIN categories c ON p.category_id = c.id
)
SELECT *
FROM product_data
WHERE price > 100;

Aquí el CTE:

• encapsula un JOIN complejo
• prepara una tabla limpia
• se reutiliza en el SELECT final

Esto es muy útil cuando después quieres aplicar filtros o cálculos sobre datos ya “preparados”.

3.4 Cuándo usar CTEs

Explicación extendida

Úsalas cuando:

• La consulta se está volviendo demasiado larga

  Separar en bloques mejora la comprensión.

• Usas varias veces el mismo cálculo

  Ejemplo: la media, el total de ventas, un rango de fechas.

• Quieres definir pasos lógicos

  Primera fase: estadísticas.

  Segunda fase: enriquecer datos.

  Tercera fase: filtrar.

• Estás creando informes avanzados

  Los informes suelen requerir cálculos intermedios y varias transformaciones.

3.5 Cuándo NO usarlas

Evítalas cuando:

• La consulta es muy sencilla
• Una vista sería más práctica, por ejemplo para uso repetido
• No hay necesidad de dividir la lógica

4. Comparativa final: Subconsulta vs Vista vs CTE

Explicación clara para recordar cuándo usar cada cosa

Necesidad	Mejor opción	Por qué
Ejecutar una consulta compleja solo una vez	Subconsulta	No hace falta más estructura
Reutilizar una consulta muchas veces	Vista	Se guarda y permanece disponible
Organizar una consulta larga	CTE	Permite dividirla en partes
Hacer cálculos previos antes de filtrar	CTE	Legibilidad y orden
Usar lógica compleja como si fuera una tabla	CTE	Se comporta como tabla temporal
Exponer datos preparados a cualquier consulta	Vista	Encapsulación limpia

Regla general:

• Si la consulta se repetirá → Vista.
• Si la consulta necesita claridad → CTE.
• Si es algo puntual y pequeño → Subconsulta.

Resumen con un ejemplo práctico final

Imagina este objetivo real:

“Quiero un informe mensual de productos caros, con categoría y desviación respecto al precio medio.”

Sin CTE: sería una consulta larga y difícil de leer.

Con CTEs:

WITH
  media AS (
    SELECT AVG(price) AS avg_price
    FROM products
  ),
  combined AS (
    SELECT
      p.name,
      p.price,
      c.name AS category,
      p.price - (SELECT avg_price FROM media) AS diff
    FROM products p
    JOIN categories c ON p.category_id = c.id
  )
SELECT *
FROM combined
WHERE price > 100;

Mucho más claro, organizado y profesional.

5. CTEs avanzadas

Explicación ampliada y paso a paso para que todo quede claro

Hasta ahora hemos visto CTEs básicas que sirven para dividir una consulta en pasos. Pero las CTEs pueden hacer mucho más. Pueden:

• Encapsular varias transformaciones encadenadas
• Limpiar datos
• Preparar informes profesionales
• Reemplazar subconsultas complejas
• Servir como base para cálculos derivados
• Resolver situaciones donde una vista sería demasiado rígida

Vamos a ver varios usos avanzados, siempre con ejemplos que puedes ejecutar en DB Browser for SQLite.

5.1 Encadenar múltiples CTEs para preparar datos

Imagina que quieres crear un informe sobre productos con estos pasos:

1. Calcular el precio medio
2. Añadir la desviación de cada producto respecto a la media
3. Clasificar los productos como “Caros”, “Normales” o “Baratos”
4. Mostrar solo los caros

Esto sería difícil sin organizar la lógica. Con CTE queda muy claro:

WITH
  media AS (
    SELECT AVG(price) AS avg_price
    FROM products
  ),
  enriched AS (
    SELECT
      p.*,
      p.price - (SELECT avg_price FROM media) AS diff
    FROM products p
  ),
  classified AS (
    SELECT
      name,
      price,
      diff,
      CASE
        WHEN diff > 30 THEN 'Caro'
        WHEN diff > -10 THEN 'Normal'
        ELSE 'Barato'
      END AS categoria_precio
    FROM enriched
  )
SELECT *
FROM classified
WHERE categoria_precio = 'Caro';

La lógica está dividida por fases como un programa bien diseñado.

Una CTE ayuda a que el SQL sea fácil de leer, modificar y depurar.

5.2 CTE con JOINs complejos para informes profesionales

Aquí un ejemplo más realista.

Supón que tienes productos, categorías y ventas.

Quieres un informe que muestre:

• producto
• categoría
• cantidad total vendida
• ingresos totales
• precio medio por unidad

En lugar de hacer un SELECT enorme, se divide en partes:

WITH
  product_info AS (
    SELECT
      p.id,
      p.name,
      p.price,
      c.name AS category
    FROM products p
    JOIN categories c ON p.category_id = c.id
  ),
  sales_info AS (
    SELECT
      s.product_id,
      SUM(s.quantity) AS total_qty,
      SUM(s.total) AS total_revenue
    FROM sales s
    GROUP BY s.product_id
  )
SELECT
  pi.name,
  pi.category,
  si.total_qty,
  si.total_revenue,
  si.total_revenue * 1.0 / si.total_qty AS avg_unit_price
FROM product_info pi
JOIN sales_info si ON pi.id = si.product_id
WHERE si.total_qty > 0;

Cada CTE representa una pieza clara del cálculo.

Esto es exactamente lo que ocurre en informes empresariales reales, donde las consultas deben ser entendibles por equipos distintos y mantenidas durante años.

5.3 CTEs como sustituto elegante de vistas temporales

A veces quieres organizar datos, pero no deseas crear una vista permanente porque:

• la consulta solo se va a usar una vez
• no quieres saturar el esquema de la base de datos
• estás probando transformaciones

Las CTEs nacen para este caso.

Ejemplo:

WITH products_clean AS (
  SELECT
    id,
    name,
    price,
    LOWER(TRIM(name)) AS name_normalized
  FROM products
)
SELECT *
FROM products_clean
WHERE price > 100;

Aquí limpias los datos dentro de una CTE pero no alteras la tabla original.

5.4 CTEs con agregaciones encadenadas

Cuando necesitas hacer múltiples cálculos estadísticos sobre la misma tabla, dividirlos con CTEs da claridad.

Ejemplo:

Obtener media, máximo, mínimo y rango de precios.

WITH stats AS (
  SELECT
    AVG(price) AS avg_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
  FROM products
)
SELECT
  avg_price,
  max_price,
  min_price,
  max_price - min_price AS price_range
FROM stats;

Esto sería mucho más difícil de leer si estuviera todo dentro de un único SELECT.

5.5 CTEs recursivas

Un concepto avanzado pero muy útil

SQLite soporta CTE recursivas. Esto permite:

• generar secuencias numéricas
• recorrer jerarquías
• analizar estructuras de árbol
• expandir grafos

Ejemplo sencillo: generar números del 1 al 10.

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

5.6 CTEs dentro de Node.js (aplicación real)

En un backend Node.js con SQLite, un CTE se trata igual que cualquier consulta.

Ejemplo usando es modules:

const rows = await db.all(`
  WITH expensive AS (
    SELECT * FROM products WHERE price > 100
  )
  SELECT name, price FROM expensive;
`);

Las CTE te permiten mover lógica pesada al SQL, en vez de implementarla en JavaScript.

Esto ahorra tiempo, líneas de código y errores.

5.7 Ejercicio práctico con CTE para DB Browser

Te propongo un ejercicio realista que puedes copiar y ejecutar:

Objetivo: crear un informe que muestre para cada categoría:

• número de productos
• precio medio
• producto más caro
• producto más barato

WITH category_stats AS (
  SELECT
    c.name AS category,
    COUNT(p.id) AS total_products,
    AVG(p.price) AS avg_price,
    MAX(p.price) AS max_price,
    MIN(p.price) AS min_price
  FROM products p
  JOIN categories c ON p.category_id = c.id
  GROUP BY c.id
)
SELECT *
FROM category_stats;

Muy difícil de leer con subconsultas, pero transparente con CTE.

6. Errores comunes

• Error 1 — Crear vistas que dependen de tablas que todavía no existen. Solución: crear vistas al final.
• Error 2 — Usar vistas para cálculos muy pesados. Las vistas no guardan datos; se recalculan cada vez.
• Error 3 — Abusar de subconsultas cuando un JOIN es suficiente. Consulta más lenta y más difícil de leer.
• Error 4 — No usar alias claros en vistas y CTEs. Dificulta la comprensión.

Ejercicio final explicado paso a paso

Mini sistema de empleados, departamentos, subconsultas, vistas y CTEs

El objetivo es construir un pequeño módulo de análisis salarial.

Irás creando datos, consultas avanzadas y herramientas que te ayudarán a “limpiar” y organizar la información.

Todo se ejecutará en DB Browser en la pestaña Execute SQL.

Paso 1

Creación de tablas y datos realistas

Primero creamos las tablas base.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  salary REAL NOT NULL
);

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

ALTER TABLE employees ADD COLUMN department_id INTEGER;

Después activamos las claves foráneas.

PRAGMA foreign_keys = ON;

Insertamos algunos departamentos:

INSERT INTO departments (name)
VALUES
  ('Desarrollo'),
  ('Diseño'),
  ('Marketing');

Insertamos empleados (observa cómo cada uno pertenece a un departamento):

INSERT INTO employees (name, salary, department_id)
VALUES
  ('Ana',   30000, 1),
  ('Luis',  35000, 1),
  ('Marta', 28000, 2),
  ('Pablo', 26000, 2),
  ('Sofía', 40000, 3),
  ('Raúl',  22000, 3);

Con esto ya tienes suficiente información realista para practicar.

Paso 2

Subconsulta correlacionada: salarios superiores a la media de su departamento

Planteamiento:

Quiero ver solo aquellos empleados cuyo salario esté por encima de la media de su propio departamento.

Esa condición depende de cada fila, por eso se usa una subconsulta correlacionada.

SELECT
  e.id,
  e.name,
  e.salary,
  e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

Explicación sencilla:

1. Para cada empleado, SQLite busca todos los empleados del mismo departamento.
2. Calcula la media salarial de ese departamento.
3. Compara la media con el salario del empleado actual.
4. Si su salario es mayor, aparece en el resultado.

Este tipo de consulta se usa mucho en análisis reales:

“empleados por encima de la media”, “productos más caros que la media de su categoría”, etc.

Paso 3

VISTA para tener información combinada: employee_info

Creamos una vista para juntar:

• nombre del empleado
• salario
• nombre del departamento

CREATE VIEW employee_info AS
SELECT
  e.id,
  e.name AS employee_name,
  e.salary,
  d.name AS department_name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id;

Consultar la vista:

SELECT * FROM employee_info;

Ventajas prácticas:

• Ya no necesitas escribir el JOIN cada vez.
• Tienes una tabla virtual limpia con toda la información.
• Te ayuda a crear informes o depurar datos fácilmente.

Paso 4

CTE para calcular media global y diferencia salarial

Ahora quieres algo distinto:

Quiero saber la media salarial global y, además, mostrar para cada empleado cuánto se desvía su salario de esa media.

Este es el uso típico de un CTE:

hacer primero un cálculo global y después combinarlo con cada fila.

WITH global_avg AS (
  SELECT AVG(salary) AS avg_salary
  FROM employees
)
SELECT
  e.id,
  e.name,
  e.salary,
  g.avg_salary,
  ROUND(e.salary - g.avg_salary, 2) AS diff_from_global_avg
FROM employees e
CROSS JOIN global_avg g;

Explicación clara:

• global_avg es una “tabla temporal” con un solo valor: la media de todos los salarios.
• En el SELECT final, cada empleado se combina con esa media.
• Luego calculas su diferencia respecto a la media.

Esto se usa muchísimo en informes: diferencias, rankings, percentiles, etc.

Paso 5

CTE con varios JOINs y filtro final de salarios altos

Ahora trabajamos como en un informe profesional:

1. Preparamos los datos en un CTE (empleado + departamento).
2. En el SELECT final aplicamos filtros.

WITH employee_data AS (
  SELECT
    e.id,
    e.name AS employee_name,
    e.salary,
    d.name AS department_name
  FROM employees e
  LEFT JOIN departments d
    ON e.department_id = d.id
)
SELECT
  employee_name,
  department_name,
  salary
FROM employee_data
WHERE salary > 30000
ORDER BY salary DESC;

Explicación:

• employee_data organiza y limpia toda la información relevante en una sola “tabla temporal”.
• Después aplicas el filtro salary > 30000 con total claridad.
• Es exactamente lo que se busca cuando la consulta crece y necesita orden.

Bonus: mezclar CTE + media global + filtros avanzados

Para cerrar el ejercicio guiado, un ejemplo profesional que junta todo:

• una CTE con la media global
• una CTE con los datos combinados
• un filtro avanzado

WITH
  global_avg AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
  ),
  employee_data AS (
    SELECT
      e.id,
      e.name AS employee_name,
      e.salary,
      d.name AS department_name
    FROM employees e
    LEFT JOIN departments d
      ON e.department_id = d.id
  )
SELECT
  ed.employee_name,
  ed.department_name,
  ed.salary,
  ROUND(ed.salary - ga.avg_salary, 2) AS diff_from_global_avg
FROM employee_data ed
CROSS JOIN global_avg ga
WHERE ed.salary > ga.avg_salary
ORDER BY ed.salary DESC;

Este ejemplo demuestra claramente:

• uso de subconsulta para construir datos derivados
• vista previa en CTE
• cálculo estadístico
• filtro final basado en la media
• lectura mucho más clara que con subconsultas anidadas

Este tipo de consultas son el estándar para informes de RRHH, ventas, contabilidad, análisis de productos, etc.

Versión avanzada del ejercicio con datos más complejos

Vamos a partir de la misma idea de empleados y departamentos, pero añadiendo un poco más de realismo:

• Cada departamento tiene un presupuesto anual.
• Cada empleado tiene:
  • fecha de contratación
  • una pequeña prima o bonus anual
• Creamos consultas que mezclen subconsultas, vistas y CTEs.

Puedes hacerlo todo en DB Browser for SQLite, en la pestaña Execute SQL.

Paso A

Ampliar el esquema con más columnas

Si ya tienes las tablas employees y departments creadas, amplíalas así:

ALTER TABLE departments ADD COLUMN annual_budget REAL;

ALTER TABLE employees ADD COLUMN hire_date TEXT;
ALTER TABLE employees ADD COLUMN annual_bonus REAL;

Rellena los presupuestos de los departamentos:

UPDATE departments
SET annual_budget = CASE name
  WHEN 'Desarrollo' THEN 200000
  WHEN 'Diseño'     THEN 120000
  WHEN 'Marketing'  THEN 180000
END;

Actualiza los empleados con datos más ricos:

UPDATE employees
SET hire_date = CASE name
  WHEN 'Ana'   THEN '2020-01-10'
  WHEN 'Luis'  THEN '2018-03-15'
  WHEN 'Marta' THEN '2021-06-01'
  WHEN 'Pablo' THEN '2019-11-20'
  WHEN 'Sofía' THEN '2017-09-05'
  WHEN 'Raúl'  THEN '2022-02-10'
END,
annual_bonus = CASE name
  WHEN 'Ana'   THEN 2000
  WHEN 'Luis'  THEN 3000
  WHEN 'Marta' THEN 1500
  WHEN 'Pablo' THEN 1000
  WHEN 'Sofía' THEN 4000
  WHEN 'Raúl'  THEN 800
END;

Con esto ya tienes un escenario más realista: salarios, bonus, presupuestos y fechas.

Paso B

Consulta avanzada con subconsulta y JOIN

Objetivo:

“Quiero ver los empleados cuyo salario total (salario + bonus) sea superior a la media del departamento, pero solo si el presupuesto del departamento es mayor de 150000.”

Primero, una subconsulta correlacionada que use salario + bonus:

SELECT
  e.name,
  e.salary,
  e.annual_bonus,
  (e.salary + e.annual_bonus) AS total_comp,
  d.name AS department_name,
  d.annual_budget
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE (e.salary + e.annual_bonus) > (
  SELECT AVG(e2.salary + e2.annual_bonus)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
AND d.annual_budget > 150000;

Qué combina aquí:

• JOIN entre empleados y departamentos.
• Subconsulta correlacionada para comparar con la media de su departamento.
• Filtro adicional por presupuesto.

Es una consulta bastante típica de análisis salarial y de recursos humanos.

Paso C

Vista avanzada con datos ya preparados

Creamos una vista que nos deje todo listo para informes:

CREATE VIEW employee_rich_info AS
SELECT
  e.id,
  e.name AS employee_name,
  e.salary,
  e.annual_bonus,
  (e.salary + e.annual_bonus) AS total_comp,
  e.hire_date,
  d.name AS department_name,
  d.annual_budget
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id;

Ahora un informe sencillo se vuelve muy corto:

SELECT
  employee_name,
  department_name,
  total_comp,
  hire_date
FROM employee_rich_info
WHERE total_comp > 32000
ORDER BY total_comp DESC;

La vista encapsula toda la lógica, y las consultas posteriores se vuelven mucho más limpias.

Paso D

CTE + vista para un informe global

Objetivo:

“Quiero saber para cada empleado cuánto se desvía su compensación total de la media global de compensación.”

Usamos la vista employee_rich_info como base y una CTE para la media:

WITH global_comp AS (
  SELECT AVG(total_comp) AS avg_comp
  FROM employee_rich_info
)
SELECT
  e.employee_name,
  e.department_name,
  e.total_comp,
  g.avg_comp,
  ROUND(e.total_comp - g.avg_comp, 2) AS diff_from_global
FROM employee_rich_info e
CROSS JOIN global_comp g
ORDER BY diff_from_global DESC;

Aquí se mezclan:

• Vista como fuente de datos preparada.
• CTE para la media global.
• Cálculo derivado de diferencia.

Es un ejemplo muy realista de informe profesional con SQLite.

Variante con CTEs recursivas

Ahora vamos a introducir un concepto más avanzado: una estructura jerárquica de empleados con jefes y subordinados.

La idea:

• Cada empleado puede tener un manager_id que apunta a otro empleado.
• Usamos una CTE recursiva para recorrer la jerarquía desde la dirección hacia abajo.

Paso E

Añadir jerarquía de jefes

Primero añadimos la columna manager_id:

ALTER TABLE employees ADD COLUMN manager_id INTEGER;

Asignamos algunos jefes de manera sencilla:

UPDATE employees
SET manager_id = CASE name
  WHEN 'Ana'   THEN NULL    -- Ana es jefa de Desarrollo
  WHEN 'Luis'  THEN 1       -- Luis reporta a Ana
  WHEN 'Marta' THEN NULL    -- Marta es jefa de Diseño
  WHEN 'Pablo' THEN 3       -- Pablo reporta a Marta
  WHEN 'Sofía' THEN NULL    -- Sofía es jefa de Marketing
  WHEN 'Raúl'  THEN 5       -- Raúl reporta a Sofía
END;

Ya tienes una estructura tipo árbol:

• Ana → Luis
• Marta → Pablo
• Sofía → Raúl

Paso F

CTE recursiva para ver el árbol de la empresa

Usamos WITH RECURSIVE para construir una tabla temporal que muestre la jerarquía:

WITH RECURSIVE org_tree AS (
  -- Nivel raíz: empleados sin manager (directores o jefes de área)
  SELECT
    e.id,
    e.name,
    e.department_id,
    e.manager_id,
    0 AS level,
    e.name AS path
  FROM employees e
  WHERE e.manager_id IS NULL

  UNION ALL

  -- Niveles siguientes: empleados que tienen como manager a alguien ya incluido
  SELECT
    e.id,
    e.name,
    e.department_id,
    e.manager_id,
    ot.level + 1 AS level,
    ot.path || ' > ' || e.name AS path
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
  id,
  name,
  department_id,
  manager_id,
  level,
  path
FROM org_tree
ORDER BY path;

Qué está pasando:

• La parte inicial de la CTE coge a los empleados sin manager_id (los jefes principales).
• La parte recursiva va buscando empleados que tienen como manager_id a alguien que ya está en el árbol.
• level indica la profundidad (0 = jefe, 1 = subordinario directo, 2 = subordinado del subordinado, etc.).
• path muestra la cadena de mando: por ejemplo Ana > Luis.

Este tipo de consulta se usa para:

• Organigramas
• Menús anidados
• Categorías con subcategorías
• Comentarios con respuestas en cascada

Paso G

CTE recursiva con agregación (salario por equipo)

Podemos ir un poco más allá y calcular el total de compensación por cada “subárbol” de la jerarquía.

Primero, una CTE recursiva que construye el árbol y asocia total_comp:

WITH RECURSIVE org_tree AS (
  SELECT
    e.id,
    e.name,
    e.manager_id,
    (e.salary + e.annual_bonus) AS total_comp
  FROM employees e
  WHERE e.manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id,
    (e.salary + e.annual_bonus) AS total_comp
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
),
team_salary AS (
  SELECT
    manager.id AS manager_id,
    manager.name AS manager_name,
    SUM(member.total_comp) AS team_total_comp
  FROM employees manager
  JOIN org_tree member ON
    member.id = manager.id
    OR member.manager_id = manager.id
  GROUP BY manager.id
)
SELECT *
FROM team_salary;

Este ejemplo es más complejo conceptualmente, pero ilustra bien la potencia:

• CTE recursiva para construir el conjunto de empleados que cuelgan de cada jefe.
• Agregaciones para calcular totales de equipo.

Aunque no lo uses a diario, conocer que esto es posible con SQLite te da un plus importante a nivel de diseño de soluciones.