Tutorial SQL: Uso de funciones agregadas: COUNT, SUM, AVG, MAX, MIN

COUNT, SUM y AVG

Las funciones de agregación son herramientas fundamentales en SQL que nos permiten realizar cálculos sobre conjuntos de filas, devolviendo un único valor como resultado. Entre las más utilizadas se encuentran COUNT, SUM y AVG, que nos ayudan a contar registros, sumar valores y calcular promedios respectivamente.

Función COUNT

La función COUNT nos permite contar el número de filas que cumplen con una condición específica o simplemente el total de filas en una tabla. Es una de las funciones de agregación más básicas y útiles.

Existen dos formas principales de utilizar COUNT:

• COUNT(*): Cuenta todas las filas, incluyendo aquellas con valores NULL.
• COUNT(columna): Cuenta solo las filas donde la columna especificada no es NULL.

Veamos algunos ejemplos:

-- Contar todos los registros de la tabla clientes
SELECT COUNT(*) FROM clientes;

-- Contar cuántos clientes tienen email registrado
SELECT COUNT(email) FROM clientes;

-- Contar cuántos clientes hay por país
SELECT pais, COUNT(*) AS total_clientes
FROM clientes
GROUP BY pais;

Un caso de uso común es contar valores distintos utilizando DISTINCT:

-- Contar cuántos países diferentes hay en la tabla clientes
SELECT COUNT(DISTINCT pais) AS total_paises FROM clientes;

Función SUM

La función SUM calcula la suma de los valores en una columna numérica. Esta función ignora los valores NULL y solo puede aplicarse a columnas con tipos de datos numéricos.

Ejemplos de uso:

-- Calcular el total de ventas
SELECT SUM(importe) AS ventas_totales FROM ventas;

-- Calcular el total de ventas por producto
SELECT id_producto, SUM(importe) AS total_ventas
FROM ventas
GROUP BY id_producto;

-- Calcular el total de ventas por año
SELECT YEAR(fecha_venta) AS año, SUM(importe) AS ventas_anuales
FROM ventas
GROUP BY YEAR(fecha_venta);

Si intentamos aplicar SUM a una columna no numérica, obtendremos un error:

-- Esto generará un error
SELECT SUM(nombre) FROM clientes;

Función AVG

La función AVG calcula el valor promedio de una columna numérica, ignorando los valores NULL. Es especialmente útil para análisis estadísticos básicos.

Ejemplos prácticos:

-- Calcular el precio promedio de todos los productos
SELECT AVG(precio) AS precio_promedio FROM productos;

-- Calcular el precio promedio por categoría
SELECT categoria, AVG(precio) AS precio_promedio
FROM productos
GROUP BY categoria;

-- Calcular el importe promedio de ventas por cliente
SELECT id_cliente, AVG(importe) AS importe_promedio
FROM ventas
GROUP BY id_cliente;

Es importante tener en cuenta que AVG solo considera los valores no NULL en su cálculo. Si necesitamos incluir los valores NULL como ceros, podemos usar la función COALESCE:

-- Calcular promedio incluyendo NULL como 0
SELECT AVG(COALESCE(importe, 0)) AS promedio_ajustado
FROM ventas;

Combinando funciones de agregación

Estas funciones pueden combinarse en una misma consulta para obtener diferentes métricas simultáneamente:

-- Análisis completo de ventas por categoría
SELECT 
    categoria,
    COUNT(*) AS total_productos,
    SUM(stock) AS stock_total,
    AVG(precio) AS precio_promedio
FROM productos
GROUP BY categoria;

También podemos aplicar filtros a los resultados de las funciones de agregación:

-- Categorías con más de 5 productos y precio promedio superior a 100
SELECT 
    categoria,
    COUNT(*) AS total_productos,
    AVG(precio) AS precio_promedio
FROM productos
GROUP BY categoria
HAVING COUNT(*) > 5 AND AVG(precio) > 100;

Consideraciones importantes

Al trabajar con estas funciones, debemos tener en cuenta:

• Las funciones COUNT, SUM y AVG ignoran los valores NULL (excepto COUNT(*)).
• SUM y AVG solo funcionan con columnas numéricas.
• Estas funciones devuelven un único valor por grupo cuando se utilizan con GROUP BY.
• Para filtrar resultados de funciones de agregación, debemos usar HAVING en lugar de WHERE.

En MySQL y PostgreSQL, estas funciones tienen un comportamiento similar, aunque pueden existir pequeñas diferencias en el manejo de tipos de datos específicos o en funciones auxiliares disponibles para cada sistema.

Funciones MIN y MAX

Las funciones MIN y MAX son herramientas esenciales dentro del conjunto de funciones de agregación en SQL. Estas funciones nos permiten encontrar los valores mínimos y máximos dentro de un conjunto de datos, lo que resulta extremadamente útil para análisis estadísticos y reportes empresariales.

Función MIN

La función MIN devuelve el valor más pequeño de la columna especificada. Funciona con diversos tipos de datos:

• Con valores numéricos: devuelve el número más pequeño
• Con texto: devuelve el valor alfabéticamente primero
• Con fechas: devuelve la fecha más antigua

Sintaxis básica:

SELECT MIN(columna) FROM tabla;

Ejemplos prácticos:

-- Encontrar el precio del producto más barato
SELECT MIN(precio) AS precio_minimo FROM productos;

-- Encontrar la fecha de la primera venta
SELECT MIN(fecha_venta) AS primera_venta FROM ventas;

-- Encontrar el cliente alfabéticamente primero
SELECT MIN(nombre) AS primer_cliente FROM clientes;

La función MIN también puede utilizarse con GROUP BY para encontrar valores mínimos por grupos:

-- Encontrar el precio mínimo por categoría de producto
SELECT 
    categoria,
    MIN(precio) AS precio_minimo
FROM productos
GROUP BY categoria;

-- Encontrar la primera venta por vendedor
SELECT 
    id_vendedor,
    MIN(fecha_venta) AS primera_venta
FROM ventas
GROUP BY id_vendedor;

Función MAX

La función MAX opera de manera opuesta a MIN, devolviendo el valor más grande de la columna especificada:

• Con valores numéricos: devuelve el número más grande
• Con texto: devuelve el valor alfabéticamente último
• Con fechas: devuelve la fecha más reciente

Sintaxis básica:

SELECT MAX(columna) FROM tabla;

Ejemplos de uso:

-- Encontrar el precio del producto más caro
SELECT MAX(precio) AS precio_maximo FROM productos;

-- Encontrar la fecha de la última venta
SELECT MAX(fecha_venta) AS ultima_venta FROM ventas;

-- Encontrar el cliente alfabéticamente último
SELECT MAX(nombre) AS ultimo_cliente FROM clientes;

Al igual que MIN, la función MAX puede combinarse con GROUP BY:

-- Encontrar el precio máximo por categoría
SELECT 
    categoria,
    MAX(precio) AS precio_maximo
FROM productos
GROUP BY categoria;

-- Encontrar el importe de la venta más alta por cliente
SELECT 
    id_cliente,
    MAX(importe) AS venta_maxima
FROM ventas
GROUP BY id_cliente;

Combinando MIN y MAX

Una de las aplicaciones más potentes es combinar ambas funciones para obtener rangos o intervalos de valores:

-- Obtener el rango de precios por categoría
SELECT 
    categoria,
    MIN(precio) AS precio_minimo,
    MAX(precio) AS precio_maximo,
    MAX(precio) - MIN(precio) AS rango_precio
FROM productos
GROUP BY categoria;

-- Obtener el período de ventas por vendedor
SELECT 
    id_vendedor,
    MIN(fecha_venta) AS primera_venta,
    MAX(fecha_venta) AS ultima_venta
FROM ventas
GROUP BY id_vendedor;

También podemos usar estas funciones para identificar registros específicos:

-- Encontrar productos con el precio mínimo y máximo
SELECT * FROM productos 
WHERE precio = (SELECT MIN(precio) FROM productos)
   OR precio = (SELECT MAX(precio) FROM productos);

Casos de uso avanzados

Las funciones MIN y MAX son particularmente útiles en escenarios analíticos más complejos:

-- Encontrar el producto más vendido por mes
SELECT 
    EXTRACT(YEAR FROM fecha_venta) AS año,
    EXTRACT(MONTH FROM fecha_venta) AS mes,
    id_producto,
    MAX(cantidad_total) AS max_ventas
FROM (
    SELECT 
        fecha_venta,
        id_producto,
        SUM(cantidad) AS cantidad_total
    FROM ventas
    GROUP BY fecha_venta, id_producto
) AS ventas_por_producto
GROUP BY año, mes;

En PostgreSQL, podemos aprovechar la cláusula DISTINCT ON junto con ORDER BY para obtener registros con valores máximos o mínimos:

-- Obtener el producto más caro de cada categoría en PostgreSQL
SELECT DISTINCT ON (categoria) 
    categoria,
    nombre_producto,
    precio
FROM productos
ORDER BY categoria, precio DESC;

Comportamiento con valores NULL

Es importante destacar que tanto MIN como MAX ignoran los valores NULL en sus cálculos. Si una columna contiene solo valores NULL, ambas funciones devolverán NULL:

-- Si todos los valores son NULL, el resultado será NULL
SELECT MIN(comentario) FROM pedidos;

Consideraciones de rendimiento

Cuando trabajamos con tablas grandes, las funciones MIN y MAX pueden beneficiarse significativamente de la presencia de índices:

-- Crear un índice para mejorar el rendimiento de MIN/MAX
CREATE INDEX idx_productos_precio ON productos(precio);

En MySQL y PostgreSQL, un índice en la columna evaluada puede acelerar dramáticamente las consultas que utilizan MIN o MAX, ya que el motor de base de datos puede encontrar el valor mínimo o máximo directamente desde el índice sin necesidad de escanear toda la tabla.

Diferencias entre MySQL y PostgreSQL

Aunque el comportamiento básico de MIN y MAX es similar en MySQL y PostgreSQL, existen algunas diferencias sutiles:

• PostgreSQL ofrece la cláusula DISTINCT ON que facilita encontrar registros con valores máximos o mínimos por grupo.
• MySQL tiene la extensión LIMIT que, combinada con ORDER BY, puede usarse para obtener resultados similares.

-- Encontrar el producto más caro de cada categoría en MySQL
SELECT p1.*
FROM productos p1
LEFT JOIN productos p2 
    ON p1.categoria = p2.categoria AND p1.precio < p2.precio
WHERE p2.id IS NULL
ORDER BY p1.categoria;

Las funciones MIN y MAX son herramientas fundamentales para el análisis de datos en SQL, permitiéndonos identificar rápidamente valores extremos y rangos en nuestros conjuntos de datos.

Funciones estadísticas

Además de las funciones básicas de agregación como COUNT, SUM, AVG, MIN y MAX, SQL ofrece un conjunto de funciones estadísticas más avanzadas que permiten realizar análisis de datos más sofisticados. Estas funciones son especialmente útiles para el análisis exploratorio de datos y la generación de informes estadísticos.

Función STDDEV y STDDEV_POP

La función STDDEV (o STDDEV_POP en PostgreSQL) calcula la desviación estándar de un conjunto de valores numéricos. La desviación estándar es una medida que indica cuánto tienden a alejarse los valores de la media.

-- Calcular la desviación estándar de los precios de productos
SELECT STDDEV(precio) AS desviacion_estandar_precios
FROM productos;

-- Calcular la desviación estándar de precios por categoría
SELECT 
    categoria,
    STDDEV(precio) AS desviacion_estandar
FROM productos
GROUP BY categoria;

En MySQL, podemos usar STDDEV() o STD() indistintamente, mientras que en PostgreSQL se utiliza STDDEV_POP(). Una desviación estándar baja indica que los valores tienden a estar cerca de la media, mientras que una desviación alta indica mayor dispersión.

Función STDDEV_SAMP

La función STDDEV_SAMP calcula la desviación estándar muestral, que es ligeramente diferente a STDDEV_POP. Se utiliza cuando estamos trabajando con una muestra de datos en lugar de la población completa.

-- Calcular la desviación estándar muestral de los precios
SELECT STDDEV_SAMP(precio) AS desviacion_muestral
FROM productos;

La diferencia entre STDDEV_POP y STDDEV_SAMP está en el divisor utilizado en el cálculo: n para la población y n-1 para la muestra.

Función VAR_POP y VARIANCE

La función VAR_POP (o VARIANCE en MySQL) calcula la varianza poblacional de un conjunto de valores numéricos. La varianza es el cuadrado de la desviación estándar y mide la dispersión de los valores respecto a la media.

-- Calcular la varianza de los precios en PostgreSQL
SELECT VAR_POP(precio) AS varianza_precios
FROM productos;

-- En MySQL
SELECT VARIANCE(precio) AS varianza_precios
FROM productos;

La varianza es útil para entender la distribución de los datos y es la base para muchos análisis estadísticos más avanzados.

Función VAR_SAMP

Similar a STDDEV_SAMP, la función VAR_SAMP calcula la varianza muestral, que es apropiada cuando trabajamos con una muestra en lugar de la población completa.

-- Calcular la varianza muestral
SELECT VAR_SAMP(precio) AS varianza_muestral
FROM productos;

Función COVAR_POP y COVAR_SAMP

Las funciones de covarianza miden la relación entre dos variables numéricas. La covarianza positiva indica que las variables tienden a moverse en la misma dirección, mientras que la negativa indica movimientos en direcciones opuestas.

-- Calcular la covarianza entre precio y stock (PostgreSQL)
SELECT COVAR_POP(precio, stock) AS covarianza
FROM productos;

-- Covarianza muestral
SELECT COVAR_SAMP(precio, stock) AS covarianza_muestral
FROM productos;

MySQL no incluye funciones de covarianza nativas, pero podemos calcularla manualmente:

-- Cálculo manual de covarianza en MySQL
SELECT 
    SUM((precio - avg_precio) * (stock - avg_stock)) / COUNT(*) AS covarianza
FROM 
    productos,
    (SELECT AVG(precio) AS avg_precio, AVG(stock) AS avg_stock FROM productos) AS medias;

Función CORR

La función CORR calcula el coeficiente de correlación de Pearson entre dos conjuntos de valores. Este coeficiente varía entre -1 y 1, donde:

• 1 indica correlación positiva perfecta
• -1 indica correlación negativa perfecta
• 0 indica ausencia de correlación lineal

-- Calcular la correlación entre precio y ventas (PostgreSQL)
SELECT CORR(p.precio, v.cantidad) AS correlacion
FROM productos p
JOIN ventas v ON p.id = v.id_producto;

Esta función es especialmente útil para análisis de mercado, donde podemos determinar si existe relación entre variables como precio y demanda.

Función PERCENTILE_CONT y PERCENTILE_DISC

PostgreSQL ofrece funciones para calcular percentiles, que son valores que dividen un conjunto de datos en cien partes iguales.

-- Calcular la mediana (percentil 50) de los precios en PostgreSQL
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY precio) AS mediana
FROM productos;

-- Calcular varios percentiles
SELECT 
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY precio) AS percentil_25,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY precio) AS mediana,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY precio) AS percentil_75
FROM productos;

La diferencia entre PERCENTILE_CONT y PERCENTILE_DISC es que la primera interpola valores para obtener el percentil exacto, mientras que la segunda devuelve un valor real del conjunto de datos.

Función REGR (Regresión lineal)

PostgreSQL incluye un conjunto de funciones REGR para análisis de regresión lineal, que permiten modelar la relación entre dos variables:

-- Calcular la pendiente de la línea de regresión
SELECT REGR_SLOPE(ventas, precio) AS pendiente
FROM estadisticas_producto;

-- Calcular el intercepto de la línea de regresión
SELECT REGR_INTERCEPT(ventas, precio) AS intercepto
FROM estadisticas_producto;

-- Calcular el coeficiente de determinación (R²)
SELECT REGR_R2(ventas, precio) AS r_cuadrado
FROM estadisticas_producto;

El coeficiente R² indica qué proporción de la varianza en la variable dependiente es predecible a partir de la variable independiente.

Funciones de ventana para estadísticas

Las funciones estadísticas pueden combinarse con la cláusula OVER para realizar cálculos estadísticos sobre ventanas o particiones de datos:

-- Calcular la desviación estándar móvil de ventas por mes
SELECT 
    fecha,
    ventas,
    STDDEV(ventas) OVER (
        ORDER BY fecha 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS desviacion_movil
FROM ventas_mensuales;

-- Calcular percentiles por categoría
SELECT 
    categoria,
    precio,
    PERCENT_RANK() OVER (PARTITION BY categoria ORDER BY precio) AS percentil
FROM productos;

Caso práctico: Análisis de outliers

Un uso común de las funciones estadísticas es la detección de valores atípicos (outliers):

-- Detectar productos con precios atípicos usando la regla de las 3 sigmas
WITH estadisticas AS (
    SELECT 
        AVG(precio) AS media,
        STDDEV(precio) AS desviacion
    FROM productos
)
SELECT 
    id,
    nombre,
    precio
FROM 
    productos, 
    estadisticas
WHERE 
    precio > media + 3 * desviacion OR
    precio < media - 3 * desviacion;

Caso práctico: Análisis de distribución

Podemos usar funciones estadísticas para analizar la distribución de nuestros datos:

-- Crear un histograma de precios
SELECT 
    FLOOR(precio / 100) * 100 AS rango_precio,
    COUNT(*) AS cantidad,
    REPEAT('■', (COUNT(*) * 100 / (SELECT COUNT(*) FROM productos))::integer) AS histograma
FROM 
    productos
GROUP BY 
    rango_precio
ORDER BY 
    rango_precio;

Consideraciones sobre rendimiento

Las funciones estadísticas pueden ser computacionalmente intensivas, especialmente en conjuntos de datos grandes:

• Considerar el uso de tablas materializadas o vistas indexadas para cálculos estadísticos frecuentes.
• Algunas funciones estadísticas requieren múltiples pasadas sobre los datos, lo que puede afectar el rendimiento.
• En PostgreSQL, considerar el uso de extensiones como tablefunc o pg_stat_statements para análisis estadísticos avanzados.

-- Crear una vista materializada para estadísticas de productos
CREATE MATERIALIZED VIEW estadisticas_productos AS
SELECT 
    categoria,
    COUNT(*) AS total,
    AVG(precio) AS precio_medio,
    STDDEV(precio) AS desviacion_precio,
    VAR_SAMP(precio) AS varianza_precio
FROM productos
GROUP BY categoria;

Diferencias entre MySQL y PostgreSQL

Es importante destacar algunas diferencias clave entre estos sistemas:

• PostgreSQL ofrece un conjunto más amplio de funciones estadísticas nativas.
• MySQL tiene algunas limitaciones en funciones estadísticas avanzadas, pero muchas pueden implementarse con expresiones personalizadas.
• PostgreSQL incluye soporte para análisis de regresión y percentiles, mientras que MySQL requiere implementaciones personalizadas.

Las funciones estadísticas en SQL proporcionan herramientas valiosas para el análisis de datos, permitiendo extraer información significativa y patrones de nuestros conjuntos de datos sin necesidad de exportarlos a herramientas especializadas de estadística.