Este módulo te permitirá profundizar en el uso de consultas avanzadas en MariaDB, explorando funciones de agregación para obtener información resumida, agrupación de datos para realizar cálculos sobre subconjuntos, subconsultas para realizar consultas anidadas y técnicas de optimización para mejorar el rendimiento de tus consultas. Estos conceptos son fundamentales para trabajar con grandes volúmenes de datos y extraer la información de manera eficiente.
5.1 Uso de Funciones de Agregación: COUNT, SUM, AVG, MAX y MIN
Las funciones de agregación en SQL permiten realizar operaciones de cálculo y resumen sobre conjuntos de datos, lo cual es útil para obtener información consolidada de las tablas.
COUNT:
- Cuenta el número de registros en una columna o en toda la tabla.
- Ejemplo: Obtener el número total de empleados.
SELECT COUNT(*) AS total_empleados FROM empleados; - También puedes contar los valores no nulos de una columna específica.
SELECT COUNT(puesto) AS total_puestos FROM empleados;
SUM:
- Suma los valores numéricos de una columna.
- Ejemplo: Calcular el total de salarios en una tabla de empleados.
SELECT SUM(salario) AS total_salarios FROM empleados;
AVG:
- Calcula el valor promedio de una columna numérica.
- Ejemplo: Calcular el salario promedio.
SELECT AVG(salario) AS salario_promedio FROM empleados;
MAX y MIN:
- MAX devuelve el valor máximo de una columna y MIN el valor mínimo.
- Ejemplo: Obtener el salario más alto y más bajo.
SELECT MAX(salario) AS salario_máximo, MIN(salario) AS salario_mínimo FROM empleados;
Estas funciones son útiles en reportes y análisis de datos, ya que permiten obtener estadísticas importantes.
5.2 Agrupación de Datos con GROUP BY y HAVING
El uso de GROUP BY permite agrupar filas que comparten un mismo valor en columnas específicas y aplicar funciones de agregación a cada grupo.
GROUP BY:
Se usa para agrupar filas y aplicar una función de agregación en cada grupo.
Ejemplo: Calcular el salario promedio por departamento.
SELECT departamento_id, AVG(salario) AS salario_promedio
FROM empleados
GROUP BY departamento_id;
Aquí, GROUP BY agrupa los registros de empleados por departamento_id y calcula el salario promedio de cada grupo.
HAVING:
HAVING se usa para filtrar los resultados después de la agrupación, aplicándose a los resultados de GROUP BY.
Ejemplo: Calcular el salario promedio por departamento, pero mostrar solo los departamentos con un salario promedio mayor a 3000.
SELECT departamento_id, AVG(salario) AS salario_promedio
FROM empleados
GROUP BY departamento_id
HAVING salario_promedio > 3000;
Mientras que WHERE se usa para filtrar registros antes de la agrupación, HAVING filtra los resultados después de la agregación.
Ejemplo Completo:
Obtener el número de empleados por departamento, pero mostrar solo aquellos departamentos que tienen más de 5 empleados.
SELECT departamento_id, COUNT(*) AS total_empleados
FROM empleados
GROUP BY departamento_id
HAVING total_empleados > 5;5.3 Subconsultas y Consultas Anidadas
Las subconsultas (o consultas anidadas) permiten utilizar una consulta dentro de otra, facilitando la extracción de información más compleja.
Subconsulta en el SELECT:
Puedes incluir una subconsulta en la cláusula SELECT para calcular valores derivados.
Ejemplo: Mostrar cada empleado con el salario promedio del departamento al que pertenece.
SELECT nombre,
salario,
(SELECT AVG(salario)
FROM empleados AS e2
WHERE e2.departamento_id = empleados.departamento_id) AS salario_promedio_departamento
FROM empleados;Subconsulta en el WHERE:
Las subconsultas en el WHERE permiten filtrar registros basándose en los resultados de otra consulta.
Ejemplo: Obtener empleados cuyo salario sea superior al salario promedio de todos los empleados.
SELECT nombre, salario
FROM empleados
WHERE salario > (SELECT AVG(salario) FROM empleados);Subconsulta en el FROM:
A veces, es útil usar una subconsulta en el FROM para crear una tabla temporal con un conjunto de datos específico.
Ejemplo: Calcular el salario promedio por departamento y listar aquellos departamentos que superan un promedio de 3000.
SELECT subquery.departamento_id, subquery.salario_promedio
FROM (SELECT departamento_id, AVG(salario) AS salario_promedio
FROM empleados
GROUP BY departamento_id) AS subquery
WHERE subquery.salario_promedio > 3000;Las subconsultas permiten hacer consultas complejas de forma modular y organizar datos en capas.
5.4 Optimización de Consultas Básicas
Optimizar consultas es crucial para mejorar el rendimiento de la base de datos, especialmente cuando los volúmenes de datos son grandes. Aquí hay algunas prácticas recomendadas para la optimización:
Uso de Índices:
Los índices aceleran las búsquedas en columnas específicas, como claves primarias y columnas comúnmente usadas en consultas.
Ejemplo: Crear un índice en la columna departamento_id de la tabla empleados.
CREATE INDEX idx_departamento_id ON empleados(departamento_id);Utilizar índices en columnas de filtrado frecuentes (WHERE) o de agrupación (GROUP BY) puede mejorar el rendimiento.
Evitar el Uso de SELECT *:
Usar SELECT * recupera todas las columnas, lo cual es ineficiente cuando solo necesitas algunas.
Ejemplo: En lugar de SELECT *, especifica solo las columnas necesarias.
SELECT nombre, salario FROM empleados;Filtrar Registros Temprano:
Aplicar filtros con WHERE antes de las agrupaciones o subconsultas reduce el número de registros procesados.
Ejemplo: Filtrar los empleados con salario mayor a 2000 antes de calcular el promedio.
SELECT AVG(salario) AS salario_promedio
FROM empleados
WHERE salario > 2000;Usar LIMIT en Consultas con Muchos Resultados:
LIMIT reduce el número de registros retornados, especialmente útil en grandes conjuntos de datos.
Ejemplo: Mostrar los 10 empleados con el salario más alto.
SELECT nombre, salario
FROM empleados
ORDER BY salario DESC
LIMIT 10;Revisión de Consultas con EXPLAIN:
EXPLAIN permite analizar el plan de ejecución de una consulta, mostrando cómo MariaDB procesará la consulta y dónde pueden mejorarse los índices.
Ejemplo: Analizar una consulta.
EXPLAIN SELECT nombre, salario
FROM empleados
WHERE departamento_id = 1;EXPLAIN revela el uso de índices y ayuda a identificar consultas lentas o cuellos de botella.
Utilizar Caching de Resultados:
MariaDB almacena resultados en caché cuando se realizan consultas repetitivas, acelerando el tiempo de respuesta.