Vistas (VIEW) — Definición y casos de uso

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

Una vista (VIEW) es una sentencia SELECT con nombre y almacenada a la que puedes referirte como a una tabla.

A diferencia de una tabla, una vista no tiene datos propios — cada vez que la referencias, el SELECT almacenado se ejecuta de nuevo.

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

① Ejecuta PRAGMA table_info(employee); / PRAGMA table_info(department); / PRAGMA table_info(sales); para revisar las definiciones de columnas de las tres tablas.

② Ejecuta SELECT * FROM employee LIMIT 5; / SELECT * FROM department LIMIT 5; / SELECT * FROM sales LIMIT 5; para previsualizar las primeras cinco filas de cada tabla.

CREATE VIEW — Da un nombre a una consulta compleja

Define una vista con CREATE VIEW view_name AS SELECT ...;.

Una vez definida, escribir SELECT * FROM view_name ejecuta el SELECT almacenado y devuelve su resultado.

Como una vista es una consulta almacenada sin datos propios, cualquier actualización de las tablas subyacentes aparece la próxima vez que referencias la vista.

Cuando ya no la necesites, elimínala con DROP VIEW view_name;.

Si quieres recrear una vista con el mismo nombre, ejecuta DROP VIEW IF EXISTS view_name; antes de CREATE VIEW para que el script sea reejecutable.

-- Nombra como vista una consulta de conteo por departamento
DROP VIEW IF EXISTS dept_headcount;
CREATE VIEW dept_headcount AS
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- Una vez definida, referénciala como a una tabla
SELECT dept_name, headcount FROM dept_headcount
ORDER BY headcount DESC;

-- Elimínala cuando termines
DROP VIEW dept_headcount;

Imagina el requisito: "Quiero ver una lista de empleados con el nombre y la ubicación del departamento junto a cada fila, sin escribir el JOIN cada vez". Dentro de una sola ejecución, lleva la vista de la definición a la referencia y a la eliminación como un script autocontenido. (Cuando se ejecute correctamente, aparecerá la explicación.)

① Define una vista llamada emp_dept que haga LEFT JOIN de employee y department por dept_id y devuelva emp_id, name, salary, dept_name y location. Pon una sentencia que elimine cualquier vista existente con el mismo nombre antes de la definición, para que el script no falle al reejecutarlo.

② Extrae name, dept_name y salary de la vista y muestra las 5 primeras filas ordenadas por salary descendente.

③ Por último, elimina la vista para que su definición no quede colgando en la página.

Tres casos de uso — reutilización, autorización, abstracción

Hay tres razones principales para usar una vista.

Reutilización: agrupa un JOIN o una agregación usada con frecuencia bajo un solo nombre para no tener que reescribir la misma consulta una y otra vez.

Autorización: crea una vista que expone solo un subconjunto de columnas o filas, y concede acceso a la vista en lugar de a la tabla subyacente (oculta columnas de salario alto, muestra solo filas del departamento del usuario, etcétera).

Abstracción: quien llama no necesita conocer la estructura interna de la vista (qué tablas une, cómo) — basta con saber el nombre de la vista y las columnas que devuelve.

-- (1) Abstracción: una vista que etiqueta empleados por cohorte de año de contratación (quien llama no ve la expresión)
DROP VIEW IF EXISTS emp_cohort;
CREATE VIEW emp_cohort AS
SELECT name, hired_on,
  CASE
    WHEN hired_on < '2018-01-01' THEN 'Veteran'
    WHEN hired_on < '2021-01-01' THEN 'Mid'
    ELSE 'Recent'
  END AS cohort
FROM employee;

SELECT cohort, COUNT(*) AS cnt
FROM emp_cohort
GROUP BY cohort
ORDER BY cohort;

DROP VIEW emp_cohort;

-- (2) Autorización: una vista que excluye el salario y expone solo columnas publicables
DROP VIEW IF EXISTS emp_public;
CREATE VIEW emp_public AS
SELECT emp_id, name, dept_id FROM employee;
DROP VIEW emp_public;

Imagina este requisito: "Quiero agrupar en un solo sitio la lógica que clasifica empleados en franjas salariales (High / Mid / Low) y confirmar que actualizar la tabla employee subyacente hace que el resultado agregado de la vista se actualice automáticamente". Como una vista es un SELECT almacenado sin datos propios, reevalúa la tabla subyacente cada vez que la referencias.

① Elimina cualquier vista existente con el mismo nombre y luego define una vista emp_tier con una columna tier que devuelva High cuando salary sea 6 500 000 o más, Mid cuando esté entre 5 000 000 y 6 500 000, y Low cuando sea menor.

② Agrega el conteo por tier con el alias cnt y comprueba los conteos antes de la actualización.

③ INSERTA un nuevo empleado en employee (emp_id 999 / name 'Zoe' / dept_id 1 / manager_id NULL / city 'Tokyo' / salary 8000000 / hired_on '2024-01-01'), que cae en la franja High.

④ Ejecuta la misma consulta de agregación que en ② y confirma que el conteo de High ha subido en 1 (prueba de que una actualización de la tabla subyacente se ve inmediatamente a través de la vista).

⑤ Limpia haciendo DELETE de la fila con emp_id = 999 y DROP de la vista.

Las vistas de agregación acortan la consulta de quien llama

Las vistas pueden almacenar agregaciones GROUP BY, no solo JOINs.

Si agrupas una agregación usada con frecuencia — como las ventas totales por empleado — en una sola vista, quienes la llamen pueden saltarse JOIN y GROUP BY y simplemente SELECT filas que ya están agregadas.

El ejemplo de abajo agrega sales por empleado, lo une con employee para adjuntar el nombre del empleado y define eso como una vista.

Quienes la llaman simplemente referencian la vista para ver "quién vendió cuánto" — no tienen que pensar en el join y la agregación internos.

-- Una vista que etiqueta los totales de ventas por departamento con el nombre del departamento
DROP VIEW IF EXISTS dept_sales;
CREATE VIEW dept_sales AS
SELECT d.dept_name, SUM(s.amount) AS total
FROM sales s
JOIN employee e   ON s.emp_id = e.emp_id
JOIN department d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- Quienes la llaman solo hacen SELECT sobre la vista agregada
SELECT dept_name, total FROM dept_sales
ORDER BY total DESC;

DROP VIEW dept_sales;

Imagina el requisito: "Para el panel de rendimiento de ventas quiero mostrar los empleados con mayor venta total sin escribir el JOIN y la agregación cada vez". Lleva la vista de la definición a la referencia y a la eliminación como un script autocontenido en una sola ejecución.

① Une employee y sales por emp_id, agrega los totales de ventas por empleado bajo el alias total y define el resultado como una vista llamada emp_sales. Las columnas de la vista deben ser emp_id, name y total — tres columnas en total. Pon una sentencia de eliminar-si-existe para el mismo nombre de vista antes de la definición.

② Extrae name y total de la vista y muestra las 5 primeras filas ordenadas por total descendente y luego por name ascendente como criterio de desempate.

③ Por último, elimina la vista.