Subconsultas ② — Tablas derivadas, subconsultas en SELECT, CREATE / INSERT SELECT

Datos usados en este artículo — employee / department / sales

La última vez usaste una subconsulta en WHERE.

En este artículo recorrerás cuatro aplicaciones en orden: poner una subconsulta en la cláusula `FROM` (una tabla derivada), escribir una en una columna de `SELECT`, combinarla con `CASE` y construir una tabla nueva a partir del resultado de una subconsulta.

El material son tres tablas de datos de empleados.

Usando employee (30 empleados), department (6 departamentos) y sales (50 líneas de venta), construirás una tabla de agregados por departamento y alinearás las ventas totales de cada empleado como una columna.

Antes de empezar los ejercicios, echa un vistazo a las definiciones de columnas y los datos de muestra de las tres tablas que usa este artículo — employee / department / sales.

① Ejecuta PRAGMA table_info(...) para comprobar las definiciones de columnas de las tres tablas.

② Ejecuta SELECT * FROM table_name LIMIT 5; para previsualizar las primeras 5 filas de cada tabla.

Tablas derivadas en FROM — tratar el resultado de una subconsulta como una sola tabla

Una tabla derivada es una forma de escribir una subconsulta en la cláusula FROM de modo que su resultado se trate como una sola tabla virtual.

Puedes construir primero "el salario promedio por departamento" como una tabla de agregados, y luego aplicar más JOIN o WHERE a esa tabla.

Es útil cuando quieres reducir un resultado agregado un paso más.

La regla clave de las tablas derivadas es que debes darle un alias.

Escribe AS alias como FROM (SELECT ...) AS ds (SQLite también te deja omitir AS) — sin él obtienes un error.

A través de ese alias referencias las columnas de la tabla derivada, como ds.avg_salary.

-- Tabla derivada en FROM: agregar plantilla y salario promedio por departamento, luego unir con department
SELECT d.dept_name, ds.headcount, ds.avg_salary
FROM (
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS ds
JOIN department d ON d.dept_id = ds.dept_id
ORDER BY ds.avg_salary DESC;

Imagina este requisito: "agrega el salario promedio por departamento, y lista solo los departamentos cuyo promedio es mayor que 5,900,000, con sus nombres de departamento". (Ejecútalo correctamente y aparecerá la explicación.)

① En una subconsulta, agrega employee por dept_id y construye una tabla derivada que tenga dept_id y el salario promedio (alias avg_salary). Excluye de la agregación a los empleados cuyo dept_id es NULL.

② Da un alias a la tabla derivada, únela con la tabla department por dept_id y extrae el nombre del departamento.

③ En el WHERE exterior, reduce a los departamentos cuyo avg_salary es mayor que 5900000, y ordena por avg_salary descendente.

Subconsultas en la cláusula SELECT — incrustar un único valor como columna

Cuando escribes una subconsulta escalar en la lista de columnas de SELECT, puedes añadir una columna cuyo valor se calcula uno por fila.

Pon (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id) en una columna, y podrás alinear "las ventas totales de ese empleado" como una columna por empleado.

Esta es una subconsulta correlacionada que referencia el emp.emp_id exterior.

-- Subconsulta en la cláusula SELECT: añadir las ventas totales de cada empleado como una columna
SELECT emp.name,
  COALESCE(
    (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id),
    0
  ) AS total_amount
FROM employee emp
ORDER BY emp.emp_id
LIMIT 6;

Un empleado sin ventas hace que la subconsulta devuelva NULL, así que reemplazarlo por 0 con COALESCE(..., 0) hace que la tabla sea más fácil de leer.

Una subconsulta en la cláusula SELECT también tiene que caber en una fila y una columna.

Imagina este requisito: "junto a una lista de todos los empleados, muestra las ventas totales de ese empleado como una columna extra, mostrando 0 para los empleados sin ventas".

① Da a la tabla employee el alias emp y extrae name.

② En las columnas de SELECT, añade una subconsulta que calcule el total de sales de ese empleado, con alias total_amount. Correlaciónala con sale.emp_id = emp.emp_id, y reemplaza el total por 0 para los empleados cuyo total salga NULL.

③ Ordena por total_amount descendente, deshaciendo empates por emp_id ascendente, y limita a las primeras 8 filas.

Combinar con CASE — adjuntar una etiqueta de ramificación según el valor de una subconsulta

Una subconsulta en la cláusula SELECT funciona igual de bien como condición en CASE.

Puedes construir una columna de rango como "si las ventas totales son 1,500,000 o más entonces High, si son mayores que 0 entonces Mid, si no None" juzgando el valor de la subconsulta con CASE WHEN.

Ten en cuenta que tienes que repetir la misma subconsulta en cada WHEN del CASE (si lo primero es la legibilidad, también hay una forma de escribirla solo una vez con WITH (expresiones de tabla común), que aprenderás en un capítulo posterior).

En este artículo cubrirás la forma básica de combinar una subconsulta y CASE directamente.

-- Clasificar empleados por su número de ventas
SELECT emp.name,
  (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) AS sale_count,
  CASE
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 4 THEN 'Frequent'
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 1 THEN 'Occasional'
    ELSE 'None'
  END AS activity
FROM employee emp
ORDER BY sale_count DESC, emp.emp_id
LIMIT 8;

Imagina este requisito: "para cada empleado, muestra las ventas totales y un rango (High / Mid / None) lado a lado".

① Da a employee el alias emp y extrae name y las ventas totales de ese empleado (NULL reemplazado por 0, alias total_amount).

② Con CASE, añade una columna de rango con alias grade donde las ventas totales 1500000 o más es 'High', mayor que 0 y menor que 1500000 es 'Mid', y si no (0) es 'None'. Usa la misma subconsulta de ventas totales también para el juicio.

③ Ordena por total_amount descendente, deshaciendo empates por emp_id ascendente, y limita a las primeras 8 filas.

CREATE / INSERT SELECT — convertir el resultado de una subconsulta en una tabla

Escribir CREATE TABLE new_table AS SELECT ... te permite guardar el resultado del SELECT directamente como una tabla nueva (CTAS: Create Table As Select).

Se usa a menudo cuando quieres conservar un resultado agregado como una instantánea.

Cuando quieres añadir filas a una tabla que ya existe, usa INSERT INTO existing_table SELECT ... para añadir en bloque el resultado del SELECT.

Estas son operaciones de escritura que crean una tabla o añaden filas.

En este artículo usarás una tabla de trabajo desechable top_seller para la agregación.

Para que obtengas el mismo resultado sin importar cuántas veces lo ejecutes, limpiarás la tabla de trabajo con DROP TABLE IF EXISTS antes de recrearla.

El INSERT en bloque es más rápido

INSERT INTO table SELECT ... inserta las filas objetivo todas a la vez en una sola sentencia.

Comparado con repetir INSERT INTO table VALUES (...) para cada fila, el análisis del SQL, las actualizaciones de índices y el manejo de la transacción ocurren una sola vez, así que cuantas más filas insertes, más rápido se vuelve.

Al copiar o mover otra tabla o un resultado agregado, prefiere el INSERT ... SELECT en bloque antes que repetir el INSERT fila a fila.

-- Guardar un agregado por departamento en una tabla de instantánea
DROP TABLE IF EXISTS dept_summary;
CREATE TABLE dept_summary AS
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id;

SELECT * FROM dept_summary ORDER BY avg_salary DESC;

Imagina este requisito: "reúne a los empleados cuyas ventas totales son 1,500,000 o más en una tabla de agregados con 3 columnas — ID de empleado, nombre y ventas totales". Como esto es una operación de escritura, estructúralo de modo que la tabla de trabajo se recree y no se rompa al reejecutar.

① Primero, limpia la tabla de trabajo si existe con DROP TABLE IF EXISTS top_seller;.

② Une employee y sales, agrega las ventas totales por empleado, escribe un SELECT que conserve solo a los empleados cuyo total es 1500000 o más, y a partir de ese resultado construye la tabla top_seller con CREATE TABLE top_seller AS SELECT .... Haz que las columnas sean las 3: emp_id, name y total_amount (ventas totales).

③ Finalmente, comprueba el contenido con SELECT * FROM top_seller ORDER BY total_amount DESC;.

Imagina este requisito: "a la tabla de agregados de la Práctica 4, añade también a los empleados de nivel medio cuyas ventas totales son 900,000 o más pero menos de 1,500,000". Usa INSERT INTO ... SELECT para añadir las filas en bloque. Para que no se rompa al reejecutar, estructúralo de modo que recree la tabla antes de añadir.

① Limpia la tabla de trabajo con DROP TABLE IF EXISTS top_seller;, luego recréala con el mismo CREATE TABLE top_seller AS SELECT ... (1.5M o más) que en la Práctica 4.

② Con INSERT INTO top_seller SELECT ..., añade a los empleados cuyas ventas totales son 900000 o más y menos de 1500000 (3 columnas: emp_id, name, ventas totales). Haz que el orden de las columnas coincida con top_seller.

③ Comprueba todas las filas con SELECT * FROM top_seller ORDER BY total_amount DESC;.