Tutorial SQL: Vistas con CREATE VIEW

Concepto de vista

Una vista en SQL es una tabla virtual basada en el resultado de una consulta SQL. A diferencia de las tablas físicas que almacenan datos reales en el disco, las vistas no almacenan datos por sí mismas, sino que muestran los datos almacenados en otras tablas de manera personalizada. Podemos pensar en una vista como una "ventana" a través de la cual observamos los datos de nuestra base de datos desde una perspectiva específica.

Las vistas funcionan como consultas predefinidas que se comportan como tablas normales cuando las utilizamos en nuestras operaciones SQL. Cuando accedemos a una vista, el sistema de gestión de base de datos ejecuta la consulta asociada a ella y nos presenta los resultados como si fueran datos de una tabla.

Características principales de las vistas

• No almacenan datos físicamente: Las vistas no ocupan espacio de almacenamiento para los datos que muestran, solo guardan la definición de la consulta.

• Siempre muestran datos actualizados: Cuando consultamos una vista, siempre obtenemos los datos más recientes de las tablas subyacentes.

• Simplifican consultas complejas: Permiten encapsular consultas complicadas en una estructura simple y reutilizable.

• Proporcionan una capa de abstracción: Ocultan la complejidad de las tablas base y permiten presentar solo la información relevante.

• Mejoran la seguridad: Pueden utilizarse para restringir el acceso a determinadas columnas o filas de las tablas originales.

Tipos de vistas

En MySQL y PostgreSQL, las vistas se clasifican principalmente en dos tipos:

• Vistas simples: Basadas en una única tabla y sin funciones agregadas.

CREATE VIEW empleados_marketing AS
SELECT id, nombre, apellido, email
FROM empleados
WHERE departamento = 'Marketing';

• Vistas complejas: Basadas en múltiples tablas (mediante JOINs) o que incluyen funciones agregadas, agrupaciones, etc.

CREATE VIEW resumen_ventas AS
SELECT v.fecha, p.nombre AS producto, SUM(v.cantidad) AS total_vendido
FROM ventas v
JOIN productos p ON v.producto_id = p.id
GROUP BY v.fecha, p.nombre;

Ventajas de utilizar vistas

• Reutilización de código: Las consultas complejas se definen una vez y pueden reutilizarse múltiples veces.

• Mantenimiento simplificado: Si cambia la estructura de las tablas base, solo necesitamos modificar la definición de la vista, no todas las consultas que la utilizan.

• Consistencia de datos: Garantizan que diferentes aplicaciones o usuarios accedan a los mismos datos de la misma manera.

• Control de acceso: Permiten proporcionar acceso a datos específicos sin exponer toda la tabla o la base de datos.

• Simplificación para el usuario final: Los usuarios pueden trabajar con estructuras de datos más sencillas y adaptadas a sus necesidades.

Limitaciones de las vistas

• Rendimiento: En algunos casos, las vistas complejas pueden tener un impacto en el rendimiento, especialmente si se basan en consultas muy complejas o si se anidan varias vistas.

• Restricciones de actualización: No todas las vistas permiten operaciones de inserción, actualización o eliminación de datos. Las vistas que involucran múltiples tablas, funciones agregadas o cláusulas DISTINCT suelen tener limitaciones para modificar datos.

• Dependencia de las tablas base: Si se elimina o modifica una tabla base, las vistas que dependen de ella pueden dejar de funcionar correctamente.

Diferencia entre vistas y tablas temporales

A diferencia de las tablas temporales que existen solo durante una sesión y almacenan físicamente datos, las vistas son permanentes (a menos que se eliminen explícitamente) y no almacenan datos. Las vistas son simplemente consultas almacenadas que se ejecutan cada vez que se accede a ellas, mientras que las tablas temporales contienen datos reales durante su tiempo de vida.

-- Ejemplo de tabla temporal
CREATE TEMPORARY TABLE temp_empleados AS
SELECT * FROM empleados WHERE salario > 50000;

-- Ejemplo de vista
CREATE VIEW empleados_bien_pagados AS
SELECT * FROM empleados WHERE salario > 50000;

Las vistas son componentes fundamentales en el diseño de bases de datos relacionales, ya que proporcionan una capa de abstracción que simplifica la interacción con los datos y mejora tanto la seguridad como la usabilidad de nuestros sistemas de información.

CREATE VIEW

La sentencia CREATE VIEW es la instrucción SQL que nos permite definir y crear vistas en nuestra base de datos. Esta sentencia establece una consulta predefinida que se almacena en el catálogo de la base de datos para su uso posterior, sin almacenar físicamente los datos que muestra.

La sintaxis básica para crear una vista en SQL es relativamente sencilla:

CREATE [OR REPLACE] VIEW nombre_vista [(columna1, columna2, ...)]
AS consulta_select
[WITH [CASCADED | LOCAL] CHECK OPTION];

Vamos a analizar cada componente de esta sintaxis:

• CREATE VIEW: Palabras clave que indican que estamos creando una vista.
• OR REPLACE: Cláusula opcional que permite sobrescribir una vista existente con el mismo nombre.
• nombre_vista: El nombre que asignamos a nuestra vista.
• columna1, columna2, ...: Lista opcional de nombres de columnas para la vista. Si se omite, las columnas tendrán los mismos nombres que en la consulta SELECT.
• AS consulta_select: La consulta SELECT que define qué datos mostrará la vista.
• WITH CHECK OPTION: Cláusula opcional que asegura que las operaciones de inserción o actualización a través de la vista cumplan con las condiciones de la vista.

Ejemplos básicos de CREATE VIEW

Veamos algunos ejemplos prácticos para entender mejor cómo crear vistas:

Ejemplo 1: Vista simple basada en una tabla

CREATE VIEW clientes_premium AS
SELECT id, nombre, apellido, email, fecha_registro
FROM clientes
WHERE tipo = 'premium';

Esta vista muestra solo los clientes clasificados como "premium" y selecciona únicamente las columnas especificadas.

Ejemplo 2: Vista con columnas renombradas

CREATE VIEW productos_disponibles (codigo, nombre, precio_venta, stock)
AS
SELECT id_producto, nombre_producto, precio * 1.21, cantidad_disponible
FROM productos
WHERE estado = 'activo';

En este ejemplo, hemos renombrado las columnas de la vista para que sean más descriptivas o amigables para los usuarios.

Opciones adicionales al crear vistas

Cláusula OR REPLACE

La cláusula OR REPLACE nos permite actualizar la definición de una vista existente sin tener que eliminarla primero:

CREATE OR REPLACE VIEW ventas_mensuales AS
SELECT 
    EXTRACT(YEAR FROM fecha_venta) AS año,
    EXTRACT(MONTH FROM fecha_venta) AS mes,
    SUM(monto) AS total_ventas
FROM ventas
GROUP BY año, mes
ORDER BY año, mes;

Si la vista ventas_mensuales ya existe, esta sentencia la reemplazará con la nueva definición. Si no existe, simplemente la creará.

WITH CHECK OPTION

La cláusula WITH CHECK OPTION es útil cuando queremos asegurar que las operaciones de modificación (INSERT, UPDATE) realizadas a través de la vista cumplan con las condiciones definidas en la vista:

CREATE VIEW empleados_activos AS
SELECT id, nombre, apellido, departamento, estado
FROM empleados
WHERE estado = 'activo'
WITH CHECK OPTION;

Con esta opción, si intentamos actualizar un empleado a través de esta vista y cambiamos su estado a "inactivo", la base de datos rechazará la operación porque violaría la condición WHERE estado = 'activo'.

Creación de vistas basadas en múltiples tablas

Las vistas son especialmente útiles para simplificar consultas complejas que involucran múltiples tablas:

CREATE VIEW detalles_pedidos AS
SELECT 
    p.id_pedido,
    c.nombre AS cliente,
    p.fecha_pedido,
    pr.nombre_producto,
    dp.cantidad,
    dp.precio_unitario,
    (dp.cantidad * dp.precio_unitario) AS subtotal
FROM 
    pedidos p
    JOIN clientes c ON p.id_cliente = c.id_cliente
    JOIN detalles_pedido dp ON p.id_pedido = dp.id_pedido
    JOIN productos pr ON dp.id_producto = pr.id_producto;

Esta vista combina información de cuatro tablas diferentes para proporcionar una visión completa de los pedidos y sus detalles.

Vistas con funciones agregadas

Podemos crear vistas que incluyan cálculos y funciones agregadas:

CREATE VIEW estadisticas_ventas_vendedor AS
SELECT 
    v.id_vendedor,
    e.nombre,
    e.apellido,
    COUNT(v.id_venta) AS total_ventas,
    SUM(v.monto) AS monto_total,
    AVG(v.monto) AS promedio_venta,
    MAX(v.monto) AS venta_maxima,
    MIN(v.monto) AS venta_minima
FROM 
    ventas v
    JOIN empleados e ON v.id_vendedor = e.id_empleado
GROUP BY 
    v.id_vendedor, e.nombre, e.apellido;

Esta vista proporciona estadísticas resumidas de ventas por vendedor, incluyendo totales, promedios y valores extremos.

Consideraciones importantes al crear vistas

• Nombres de columnas: Si no especificamos nombres de columnas en la definición de la vista, se utilizarán los nombres de las columnas de la consulta SELECT. Para expresiones o funciones, es recomendable usar alias.

• Rendimiento: Las vistas no mejoran automáticamente el rendimiento. De hecho, vistas muy complejas pueden afectar negativamente al rendimiento si no están bien diseñadas.

• Limitaciones en MySQL y PostgreSQL:

• En MySQL, las vistas no pueden usar subconsultas en la cláusula FROM de la consulta principal.

• En PostgreSQL, las vistas no pueden hacer referencia directa a secuencias.

• Vistas actualizables: No todas las vistas permiten operaciones de modificación de datos. En general, para que una vista sea actualizable debe:

• Estar basada en una sola tabla

• No incluir funciones agregadas (SUM, COUNT, etc.)

• No incluir DISTINCT, GROUP BY o HAVING

• No incluir subconsultas en la lista SELECT o en la cláusula WHERE

-- Esta vista permite operaciones INSERT, UPDATE y DELETE
CREATE VIEW empleados_ventas AS
SELECT id_empleado, nombre, apellido, email, telefono
FROM empleados
WHERE departamento = 'Ventas';

-- Esta vista NO permite modificaciones
CREATE VIEW resumen_ventas_departamento AS
SELECT departamento, COUNT(*) AS total_empleados, AVG(salario) AS salario_promedio
FROM empleados
GROUP BY departamento;

Buenas prácticas al crear vistas

• Nombres descriptivos: Utiliza nombres que indiquen claramente el propósito de la vista.

• Documentación: Añade comentarios en la base de datos para documentar el propósito y uso de cada vista.

CREATE VIEW clientes_recientes AS
SELECT id, nombre, apellido, fecha_registro
FROM clientes
WHERE fecha_registro > CURRENT_DATE - INTERVAL '30 days';

COMMENT ON VIEW clientes_recientes IS 'Clientes registrados en los últimos 30 días';

• Evitar vistas anidadas: Aunque es posible crear vistas basadas en otras vistas, esto puede afectar al rendimiento y dificultar el mantenimiento.

• Mantener la simplicidad: Las vistas deben simplificar el acceso a los datos, no complicarlo. Evita crear vistas excesivamente complejas.

Las vistas son herramientas poderosas que, cuando se utilizan correctamente, pueden mejorar significativamente la organización y accesibilidad de nuestros datos en MySQL y PostgreSQL, proporcionando una capa de abstracción que facilita el trabajo con bases de datos complejas.

Consultas con vistas

Una vez que hemos creado nuestras vistas en la base de datos, podemos utilizarlas en consultas SQL de la misma manera que usaríamos tablas normales. Esta es una de las principales ventajas de las vistas: nos permiten trabajar con datos complejos mediante una interfaz simplificada.

Las vistas se comportan como tablas virtuales en nuestras consultas, lo que significa que podemos seleccionar datos de ellas, filtrarlos, ordenarlos e incluso combinarlas con otras tablas o vistas mediante operaciones JOIN.

Consultas básicas sobre vistas

Para consultar una vista, utilizamos la misma sintaxis que emplearíamos con una tabla normal:

SELECT * FROM nombre_vista;

Por ejemplo, si tenemos una vista llamada empleados_marketing que muestra solo los empleados del departamento de marketing:

SELECT nombre, apellido, email FROM empleados_marketing
WHERE fecha_contratacion > '2022-01-01'
ORDER BY apellido;

Esta consulta selecciona nombre, apellido y email de los empleados de marketing contratados después del 1 de enero de 2022, ordenados por apellido.

Filtrado de datos en consultas con vistas

Podemos aplicar condiciones de filtrado adicionales a las vistas, incluso si la vista ya tiene sus propios filtros:

-- Vista que ya filtra por departamento
CREATE VIEW empleados_ventas AS
SELECT id, nombre, apellido, salario, fecha_contratacion
FROM empleados
WHERE departamento = 'Ventas';

-- Consulta que aplica filtros adicionales a la vista
SELECT nombre, apellido, salario
FROM empleados_ventas
WHERE salario > 30000 AND fecha_contratacion > '2020-01-01';

En este ejemplo, la consulta final solo mostrará empleados del departamento de ventas (filtro de la vista) que además tengan un salario superior a 30.000 y hayan sido contratados después de 2020 (filtros adicionales de la consulta).

Combinación de vistas con tablas

Una de las operaciones más potentes es la capacidad de combinar vistas con tablas mediante JOINs:

SELECT c.nombre AS cliente, r.fecha, r.total
FROM clientes_premium c
JOIN resumen_ventas r ON c.id = r.cliente_id
WHERE r.fecha >= '2023-01-01'
ORDER BY r.total DESC;

En este ejemplo, combinamos la vista clientes_premium con la vista resumen_ventas para obtener información sobre las ventas realizadas a clientes premium desde el inicio de 2023, ordenadas por el total de venta.

Uso de vistas en subconsultas

Las vistas también pueden utilizarse dentro de subconsultas:

SELECT nombre, apellido, departamento
FROM empleados
WHERE id IN (
    SELECT id_empleado 
    FROM ventas_destacadas
    WHERE monto_venta > 10000
);

Esta consulta selecciona los empleados cuyos IDs aparecen en la vista ventas_destacadas con montos superiores a 10.000.

Agregaciones sobre vistas

Podemos aplicar funciones de agregación sobre los datos de una vista:

SELECT 
    departamento,
    COUNT(*) AS total_empleados,
    AVG(salario) AS salario_promedio,
    MAX(salario) AS salario_maximo
FROM empleados_activos
GROUP BY departamento
HAVING COUNT(*) > 5
ORDER BY salario_promedio DESC;

Esta consulta calcula estadísticas por departamento a partir de la vista empleados_activos, mostrando solo departamentos con más de 5 empleados.

Combinación de múltiples vistas

Podemos combinar varias vistas en una sola consulta para obtener información más completa:

SELECT 
    p.nombre AS producto,
    c.nombre AS categoria,
    v.total_vendido,
    i.stock_actual
FROM productos_populares p
JOIN categorias_productos c ON p.categoria_id = c.id
JOIN ventas_por_producto v ON p.id = v.producto_id
JOIN inventario_actual i ON p.id = i.producto_id
WHERE v.total_vendido > 100 AND i.stock_actual < 50;

Esta consulta combina cuatro vistas diferentes para identificar productos populares con ventas superiores a 100 unidades pero con un stock inferior a 50 unidades.

Optimización de consultas con vistas

Aunque las vistas pueden simplificar nuestras consultas, es importante entender cómo afectan al rendimiento:

• Plan de ejecución: Cuando consultamos una vista, el motor de base de datos sustituye la referencia a la vista por su consulta subyacente y luego optimiza la consulta completa.

• Índices: Las vistas no tienen sus propios índices, sino que dependen de los índices de las tablas base.

Para verificar cómo se ejecuta una consulta sobre una vista, podemos usar la sentencia EXPLAIN:

EXPLAIN SELECT * FROM ventas_mensuales WHERE año = 2023;

Este comando nos mostrará el plan de ejecución que utilizará el motor de base de datos para resolver nuestra consulta.

Actualización de datos a través de vistas

En algunos casos, podemos utilizar vistas no solo para consultar datos sino también para actualizarlos:

-- Actualizar datos a través de una vista
UPDATE empleados_departamento
SET salario = salario * 1.1
WHERE rendimiento = 'Excelente';

Sin embargo, como mencionamos anteriormente, no todas las vistas permiten operaciones de modificación. Para que una vista sea actualizable, generalmente debe cumplir ciertos requisitos como estar basada en una sola tabla y no incluir funciones agregadas.

Consultas comunes con vistas en entornos de producción

Las vistas son especialmente útiles en escenarios como:

• Informes periódicos: Crear vistas que encapsulen la lógica de informes complejos.

SELECT 
    region, 
    SUM(total_ventas) AS ventas_totales,
    COUNT(DISTINCT cliente) AS numero_clientes
FROM resumen_ventas_regional
WHERE año = EXTRACT(YEAR FROM CURRENT_DATE) AND mes = EXTRACT(MONTH FROM CURRENT_DATE) - 1
GROUP BY region
ORDER BY ventas_totales DESC;

• Dashboards: Proporcionar datos resumidos para paneles de control.

SELECT 
    producto,
    ventas_actuales,
    ventas_previas,
    ((ventas_actuales - ventas_previas) / ventas_previas) * 100 AS porcentaje_cambio
FROM comparativa_ventas_mensual
WHERE categoria = 'Electrónica'
ORDER BY porcentaje_cambio DESC
LIMIT 10;

• APIs de datos: Simplificar la estructura de datos expuesta a aplicaciones externas.

SELECT id, nombre, descripcion, precio, disponibilidad
FROM api_productos
WHERE disponibilidad = true AND categoria_id = 5;

Ejemplos prácticos de consultas con vistas

Ejemplo 1: Análisis de ventas por región y categoría

-- Primero, creamos una vista que resume las ventas
CREATE VIEW resumen_ventas_categoria AS
SELECT 
    r.nombre AS region,
    c.nombre AS categoria,
    SUM(v.monto) AS total_ventas,
    COUNT(v.id) AS numero_transacciones
FROM ventas v
JOIN regiones r ON v.region_id = r.id
JOIN productos p ON v.producto_id = p.id
JOIN categorias c ON p.categoria_id = c.id
GROUP BY r.nombre, c.nombre;

-- Ahora podemos hacer consultas más simples sobre esta vista
SELECT 
    region,
    categoria,
    total_ventas,
    RANK() OVER (PARTITION BY region ORDER BY total_ventas DESC) AS ranking
FROM resumen_ventas_categoria
WHERE total_ventas > 10000
ORDER BY region, ranking;

Ejemplo 2: Seguimiento de inventario y alertas

-- Vista para monitorear el inventario
CREATE VIEW estado_inventario AS
SELECT 
    p.id,
    p.nombre AS producto,
    p.precio,
    i.cantidad AS stock_actual,
    p.stock_minimo,
    (i.cantidad <= p.stock_minimo) AS necesita_reposicion
FROM productos p
JOIN inventario i ON p.id = i.producto_id;

-- Consulta para generar alertas de reposición
SELECT 
    producto,
    stock_actual,
    stock_minimo,
    (stock_minimo - stock_actual) AS unidades_faltantes
FROM estado_inventario
WHERE necesita_reposicion = true
ORDER BY unidades_faltantes DESC;

Ejemplo 3: Análisis de rendimiento de empleados

-- Vista de rendimiento de ventas por empleado
CREATE VIEW rendimiento_empleados AS
SELECT 
    e.id,
    e.nombre,
    e.apellido,
    e.departamento,
    COUNT(v.id) AS total_ventas,
    SUM(v.monto) AS monto_total,
    AVG(v.monto) AS ticket_promedio
FROM empleados e
LEFT JOIN ventas v ON e.id = v.empleado_id
WHERE e.departamento = 'Ventas'
GROUP BY e.id, e.nombre, e.apellido, e.departamento;

-- Consulta para identificar empleados destacados
SELECT 
    nombre,
    apellido,
    total_ventas,
    monto_total,
    ticket_promedio,
    CASE 
        WHEN monto_total > 100000 THEN 'Excelente'
        WHEN monto_total > 50000 THEN 'Bueno'
        ELSE 'Regular'
    END AS desempeño
FROM rendimiento_empleados
WHERE total_ventas > 0
ORDER BY monto_total DESC;

Las consultas con vistas nos permiten trabajar con datos complejos de manera más sencilla y estructurada, facilitando el análisis y la toma de decisiones basada en datos. Al encapsular la complejidad en las vistas, nuestras consultas finales resultan más legibles, mantenibles y eficientes.