Guía para principiantes sobre las funciones agregadas de SQL

Las funciones agregadas son poderosas SQL que realizan cálculos numéricos sobre los datos, permitiendo que la consulta devuelva información resumida sobre una columna o conjunto de resultados determinado. Estas funciones pueden utilizarse junto con la sentencia GROUP BY. Veamos cómo funcionan utilizando algunos sencillos ejemplos.

Funciones agregadas de SQL

Supongamos que tenemos usuarios que residen en una ciudad y almacenamos su información en dos tablas. Estas tablas y su relación se muestran a continuación:

Pongamos algunos datos en este modelo:

INSERT INTO `cities` VALUES (1,'Miami'),(2,'Orlando'),
(3,'Las Vegas'),(4,'Coyote Springs');
INSERT INTO `users` VALUES (1,1,'Jhon','Doe',22),
(2,1,'Albert','Thomson',15),(3,2,'Robert','Ford',65),(4,3,'Samantha','Simpson',9),(5,2,'Carlos','Bennet',42),
(6,2,'Mirtha','Lebrand',81),(7,3,'Alex','Gomez',31);

Ahora tenemos suficiente material para explicar el uso básico de las funciones agregadas de SQL. Empecemos con una sencilla.

MIN

Esta función devuelve el valor más pequeño de una columna dada. Por ejemplo, vamos a obtener la edad mínima de nuestro grupo de usuarios:

SELECT MIN(age) FROM users;

Esto devuelve un "9".

También puedes usar esta función para encontrar información alfabética. Probemos con la columna "last_name":

SELECT MIN(last_name) FROM users;

Esto devuelve "Bennet", que es el primero en orden alfabético.

Nota: La ordenación utilizada para clasificar los datos afectará a los resultados de esta consulta. Por ejemplo, en la compaginación danesa, la "A" se trata como "A", la última letra del alfabeto. En cambio, en el cotejo latino, la "A" se trata como la primera letra del alfabeto.

MAX

De forma similar pero opuesta a MIN, MAX devuelve el mayor valor de una columna. Obtengamos la edad máxima de nuestra lista de usuarios:

SELECT MAX(age) FROM users;

Esto devolverá un "81".

Intentemos lo mismo con la columna del apellido:

SELECT MAX(last_name) FROM users;

Devuelve "Thomson", que es el último en orden alfabético. Recuerda que esto puede cambiar dependiendo de la compaginación que estés utilizando.

SUMA

Esta función calcula la suma de todos los valores numéricos de una columna. Usémosla para obtener la suma de todas las edades de la tabla:

SELECT SUM(age) FROM users;

Devolverá "265".

AVG

Sirve para calcular el valor medio de una columna. Veámoslo en acción, recuperando la edad media de nuestros usuarios:

SELECT AVG(age) FROM users;

Devuelve un "27,75".

COUNT (columna)

Devuelve el número de valores no nulos en una columna determinada. Si quisiéramos saber cuántos usuarios nos han dicho su edad, escribiríamos:

SELECT COUNT(age) FROM users;

Devolverá un "7". Todos los registros de la tabla "users"tienen un valor de edad. Si un registro no tuviera un valor de edad, sería NULL (y no se incluiría en el resultado de COUNT). Si desea contar el número real de filas de la tabla independientemente del valor de la columna, entonces la función COUNT(*) es lo que necesita. En lugar de especificar un nombre de columna como argumento de la función, utilizamos un asterisco:

SELECT COUNT(*) FROM users;

En nuestro caso, esto sigue devolviendo un "7" porque la tabla tiene siete registros.

Los valores NULL pueden ser confusos, pero no te preocupes. Más adelante en este artículo mostraremos cómo se pueden tratar los valores NULL en las funciones agregadas de SQL.

Ahora que sabes lo que hacen estas funciones agregadas, veamos cómo hacerlas aún más útiles.

¿Quieres saber más sobre Tipos de JOIN en SQL? Mira un episodio de nuestra serie We Learn SQL en Youtube. Comprueba si ya lo sabes todo sobre los diferentes tipos de JOINs.

Uso de GROUP BY con funciones agregadas

La sentencia GROUP BY nos permite realizar agregaciones en un grupo de valores basados en columnas dadas. Utilizarás GROUP BY con funciones agregadas frecuentemente, por lo que estos ejemplos serán un poco más complicados y realistas que los simples que utilizamos antes.

Conceptualmente, "GROUP BY (column_x)" significa "poner todos los registros que comparten el mismo valor en "column_x" en un grupo". Veamos cómo funciona esto con cada una de las funciones que ya hemos comentado.

MIN + GROUP BY

Supongamos que queremos saber la edad de nuestro usuario más joven en cada ciudad. Podemos ver que la tabla "users"tiene una columna llamada "city_id" que identifica la ciudad donde vive cada usuario. Podemos utilizar esta columna con una sentencia GROUP BY para ver la edad más joven de cada ciudad:

SELECT 
    city_id, MIN(age)
FROM
    users
GROUP BY city_id;

Para entender mejor lo que está pasando, miremos los datos en bruto de la tabla "users":

El uso de MIN() con GROUP BY agrupará los registros por los valores de la columna "city_id" antes de calcular el agregado para cada grupo. Si pudieras ver los valores agrupados, se vería algo así:

Entonces la consulta tomará las edades más bajas dentro de cada grupo. Si pudiéramos ver este paso en acción, se vería así

Los valores resaltados representan los valores calculados en MIN() para cada grupo.

Finalmente, la consulta mostrará los siguientes resultados:

MAX + GROUP BY

Como ya habrá adivinado, el uso de MAX combinado con GROUP BY funciona de la misma manera que MIN. Simplemente devuelve el mayor valor de cada grupo. Podríamos calcular la edad máxima de cada ciudad de forma similar:

SELECT 
    city_id, MAX(age)
FROM
    users
GROUP BY city_id;

Esta consulta agrupará a los usuarios en función de su campo "city_id" y luego tomará el valor de la edad máxima de cada grupo. Nos da los siguientes resultados:

SUM + GROUP BY

También podemos calcular la suma de las edades de los usuarios en cada ciudad. Para ello, podemos ejecutar la siguiente consulta...

SELECT 
    city_id, SUM(age)
FROM
    users
GROUP BY city_id;

... que recuperará:

COUNT + GROUP BY

También podemos querer calcular el número de usuarios en cada ciudad. Es fácil hacerlo utilizando la función COUNT:

SELECT 
    city_id, COUNT(age)
FROM
    users
GROUP BY city_id;

Basándonos en los dos últimos ejemplos, podríamos calcular la edad media de cada ciudad dividiendo la suma de todas las edades de un grupo de ciudades entre el número de usuarios de esa ciudad. Así es como lo hacemos:

SELECT 
    city_id,
    SUM(age),
    COUNT(age),
    SUM(age) / COUNT(age) as average
FROM
    users
GROUP BY city_id;

Nota: También podríamos haber utilizado aquí COUNT(*), ya que no hay registros con valores NULL en la columna "age". En este caso, COUNT(age) funciona igual que COUNT(*). De lo contrario, el valor sería diferente, como explicaremos más adelante en la sección "Tratamiento de los NULL".

AVG + GROUP BY

En el ejemplo anterior, hemos calculado "manualmente" la edad media de cada ciudad. Podemos utilizar la función AVG() para que realice esta operación por nosotros, como se muestra a continuación:

SELECT 
    city_id,
    AVG(age)
FROM
    users
GROUP BY city_id;

El valor devuelto por AVG(age) es idéntico al resultado de la operación matemática que realizamos anteriormente.

Filtrar resultados agrupados

Hay ocasiones en las que es necesario filtrar aún más los resultados basándose en las condiciones generadas por los resultados agrupados. Añadir cualquier condición en el WHERE fallaría. ¿No me cree? Mira lo que ocurre cuando intentamos recuperar el número de usuarios sólo en las ciudades donde la edad media de los usuarios es superior a 20 años:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE AVG(age) >= 20
GROUP BY city_id;

Esto hará que el motor (en mi caso, MySQL) se queje. Dirá algo así

Código de error: 1111. Uso no válido de la función de grupo

Para filtrar los resultados de esta manera, necesitamos utilizar la cláusula HAVING. HAVING filtrará los resultados resumidos de GROUP BY; la cláusula WHERE sólo se aplica a los registros individuales. Si algún grupo no cumple los criterios de la cláusula HAVING, no será devuelto.

Por lo tanto, si quisiéramos obtener los COUNT de cada ciudad con una edad media de al menos 20 años, la forma correcta de manejar el filtrado sería la siguiente

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
GROUP BY city_id
HAVING AVG(age) >= 20

Nota: El grupo con city_id = 1 se descarta, ya que su AVG(age) es 18,5.

Por último, WHERE y HAVING pueden utilizarse simultáneamente sin ningún problema (si tiene sentido en su consulta, por supuesto). Observe cómo funciona esto en el siguiente ejemplo. Volveremos a realizar el cálculo, pero esta vez excluiremos a cualquier usuario cuyo apellido sea "Simpson":

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE last_name <> 'Simpson'
GROUP BY city_id
HAVING AVG(age) >= 20

Observe que el cálculo de COUNT y AVG difiere para city_id = 3. Hay un usuario con el apellido "Simpson" para city_id = 3, y ese registro fue descartado debido a la condición de WHERE last_name <> 'Simpson’.

Cómo lidiar con los NULL

Hasta ahora todas nuestras columnas se han llenado de datos, pero no siempre es así. Vamos a insertar algunos registros con edades NULL para explicar algunos casos extremos. Los siguientes dos INSERTs añadirán dos nuevos usuarios, con un ID de 8 y 9 respectivamente, que tienen un NULL en la columna de edad:

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`)
VALUES ('8', '2', 'Frederic', 'Scott',NULL);

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`) 
VALUES ('9', '4', 'Stacy', 'Roberts',NULL);

Los registros con valores nulos no se consideran en MIN, MAX, SUM, AVG y COUNT(column). Esos registros simplemente se ignoran en el cálculo. Una regla general es que si no espera tener valores nulos o si espera tratar los NULL como "0", entonces debe establecer las columnas como NOT NULL y establecer las columnas NULL como "0" o cualquier valor que tenga sentido para su caso de uso.

COUNT(columna) vs COUNT(*)

La función COUNT(column) no contará ningún registro con valores de edad NULL. Veamos esto en acción:

SELECT 
    COUNT(age)
FROM
    users;

Esta consulta devuelve un "7" de nuevo; los dos registros que hemos añadido tienen valores NULL en la columna "age", por lo que se ignoran. Si quisiéramos contar todos los registros independientemente del valor, utilizaríamos la función COUNT(*) function:


SELECT 
    COUNT(*)
FROM
    users;

Esto devuelve el resultado esperado de "9".

Veamos cómo afectan los NULL a la función AVG().

AVG + NULL

Debido a que AVG ignorará los registros con NULL en la columna especificada, el valor resultante puede no tener sentido. He aquí el motivo.

La función AVG(age) sumará sólo los usuarios con un valor no nulo en la columna "age" y dividirá ese número contra el COUNT de los usuarios que también tienen un valor no nulo "age". Esto es 7 en nuestro ejemplo. Si considera que los dos usuarios que acabamos de añadir con edades NULL deben ser considerados para el cálculo de la edad media, entonces el valor que obtendrá con AVG(age) será erróneo. La consulta siguiente mostrará la diferencia en los cálculos:

SELECT 
    SUM(age),
    COUNT(age),
    AVG(age),
    SUM(age) / COUNT(age),
    COUNT(*),
    SUM(age) / COUNT(*)
FROM
    users;

Observe cómo el valor de AVG(age) coincide con el valor calculado con SUM(age)/COUNT(age); ambas funciones no consideran los registros con valores NULL. Pero observe cómo cambia el valor medio cuando se utiliza COUNT(*), que incluye todos los registros.

Hemos visto que los valores NULL no se computan en las funciones MIN, MAX, AVG, SUM y COUNT(column_name). Si va a utilizar estas funciones y espera algunos campos NULL, asegúrese de establecer los NULL como algún valor específico.

Además, configure los tipos de columna de forma coherente con el valor que está almacenando. Por ejemplo, los números almacenados en una columna VARCHAR no serán tratados como numéricos y pueden causar resultados no deseados en sus cálculos de funciones agregadas.

Finalmente, me gustaría enfatizar la importancia de poner las condiciones de filtrado en los lugares correctos dentro de sus consultas. Utilice HAVING si necesita condiciones basadas en valores agregados.

¿Qué opinas de las funciones agregadas de SQL? ¿Tienes alguna experiencia o ejemplo sobre su uso? Por favor, comparte tus opiniones con nuestra comunidad. Y no te pierdas la segunda parte de este artículo, donde explicaré cómo integrar las funciones agregadas de SQL con los JOIN. Pronto lo publicaremos en el blog de Vertabelo.