Introducción al uso de las funciones agregadas de SQL con JOINs

Anteriormente, hemos hablado de la función uso de funciones agregadas en SQL con la sentencia GROUP BY. Los lectores habituales de nuestro blog también recordarán nuestro reciente tutorial sobre JOINs. Si estás un poco oxidado en cualquiera de estos temas, te animo a que los revises antes de continuar con este artículo. Porque vamos a profundizar en funciones agregadas emparejándolas con JOINs. Este dúo da rienda suelta a todas las posibilidades de las funciones agregadas de SQL y nos permite realizar cálculos en varias tablas en una sola consulta.

¿Qué hacen las funciones agregadas de SQL?

A continuación se presenta un rápido resumen de las funciones agregadas más comunes de SQL:

También es importante recordar que la sentencia GROUP BY, cuando se utiliza con agregados, calcula valores que han sido agrupados por columna. (Para más información, consulte Guía para principiantes sobre las funciones agregadas de SQL.) Podemos utilizar GROUP BY con cualquiera de las funciones anteriores. Por ejemplo, en el siguiente ejemplo utilizamos la función MIN():

SELECT MIN(column_name)
FROM table_name 
GROUP BY group_column

Esto recuperaría el valor mínimo encontrado en column_name para cada conjunto de valores en un grupo basado en la columna group_column. La misma idea se aplica para las funciones MAX, SUM, AVG, y COUNT.

JOINs Padre-Hijo

Ahora vamos a profundizar en algunas situaciones comunes en las que utilizará grupos por JOINs con funciones de agregación. Si ha leído la Guía para principiantes de las funciones agregadas de SQL, el siguiente diagrama le resultará familiar:

Si ha utilizado este modelo antes (por ejemplo, haciendo los ejemplos del artículo anterior) asegúrese de borrar cualquier registro existente de su tabla. Puede hacerlo ejecutando los siguientes comandos:

TRUNCATE cities;
TRUNCATE users;

Introduzcamos algunos datos frescos en las tablas:

INSERT INTO `cities` VALUES
    (1,'Miami'),
    (2,'Orlando'),
    (3,'Las Vegas'),
    (4,'Coyote Springs');
INSERT INTO `users` VALUES
    (1,1,'John','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);

Tenemos una tabla llamada users y otra tabla llamada cities. Estas dos tablas tienen algo en común: un valor numérico de identificación de la ciudad. Este valor se almacena en la columna id de la tabla cities y en la columna city_id de la tabla users tabla . La columna city_id contiene una referencia (también conocida como clave externa) que conecta un registro de usuario con una ciudad. Estos registros coincidentes nos permiten JOIN ambas tablas juntas.

En otras palabras, conocemos la ciudad de un usuario cuando tomamos el registro de la tabla cities tabla que tiene un valor de id igual al valor de users.city_id. En la siguiente consulta, podemos ver esto en acción:

SELECT  cities.*, users.*
FROM cities
JOIN users
  ON cities.id = users.city_id;

Dado que la tabla users se conecta con una ciudad a través de la clave foránea city_id, podemos decir que un usuario pertenece a una ciudad y, por tanto, la ciudad tiene muchos usuarios. Se trata de una relación padre-hijo (ciudades-usuarios); la tabla users comparte un vínculo con la tabla cities tabla.

Con esta relación en mente, sigamos adelante y veamos cómo podemos calcular algunos datos resumidos interesantes que relacionen ambas tablas.

Aggregate + GROUP BY + JOIN

Ahora vamos a abordar algunas situaciones prácticas en las que vamos a GROUPde los valores de las tablas JOINde las tablas.

MIN + GROUP BY + JOIN

Calcular valores basados en registros hijos que están agrupados por una columna padre es bastante común. Construyamos una consulta que recupere el menor users.age (registro hijo) para cada cityname (registro padre):

SELECT cities.cityname, MIN(users.age)
FROM cities
JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Esto devolverá:

Hay algo muy importante que señalar sobre la forma en que funciona el JOIN. Será más obvio si miramos todas las ciudades:

SELECT cities.cityname
FROM cities

Como puede ver, "Coyote Springs" no aparecía antes porque no tiene usuarios. Si quiere que esa ciudad aparezca en los resultados resumidos, deberá utilizar un LEFT JOIN en su lugar:

SELECT cities.cityname, MIN(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Esto devolverá:

Si esto tiene sentido o no dependerá de tu caso de uso, pero es importante que tengas en cuenta esta situación al unir tablas.

MAX + GROUP BY + JOINS

Podemos encontrar la mayor edad de cada ciudad utilizando la función MAX():

SELECT cities.cityname, MAX(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

La consulta anterior recuperará:

Nótese que he utilizado LEFT JOIN. Quiero una lista de todas las ciudades, no sólo de las que tienen registros de usuario asociados.

SUMA + GROUP BY + JOIN

Veamos ahora cómo sumar las edades de cada ciudad. Para ello podemos utilizar la función SUM():

SELECT cities.cityname, SUM(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Que devuelve:

COUNT + GROUP BY + JOIN

Supongamos que queremos ver el número de usuarios de cada ciudad. Utilizaríamos la función COUNT(), así:

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Que devuelve:

MEDIA + GRUPO POR + JOIN

Utilizando el número de usuarios de cada ciudad (COUNT) y el SUM de las edades combinadas de los usuarios de cada ciudad, podemos calcular la edad media de cada ciudad. Simplemente dividimos la edad sumada por el número de usuarios de cada ciudad:

SELECT 
    cities.cityname,
    SUM(users.age) AS sum,
    COUNT(users.id) AS count,
    SUM(users.age) / COUNT(users.id) AS average
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Devuelve:

Observa cómo la suma y el promedio calculado dan como resultado un valor NULL para Coyote Springs. Esto se debe a que Coyote Springs no tiene usuarios y, por lo tanto, la columna resumida no puede calcular un valor numérico.

AVG + GROUP BY + JOINS

El ejemplo anterior utilizó un cálculo que introdujimos para encontrar una edad media para cada ciudad. Podríamos haber utilizado la función AVG() en su lugar, como se muestra a continuación:

SELECT cities.cityname, AVG(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Esto da como resultado los mismos valores que el ejemplo anterior:

Filtrar resultados

A veces es necesario filtrar las filas en función de ciertas condiciones. En este tipo de consulta, hay tres etapas en las que puede hacerlo: WHERE, HAVING, y JOIN.

Dependiendo de la situación, cada una de estas opciones puede tener un resultado diferente. Es importante entender cuál usar cuando se quiere un resultado específico. Veamos algunos ejemplos para ilustrar esto.

Uso del predicado JOIN

Obtengamos el número de usuarios menores de 30 años en cada ciudad. Utilizaremos LEFT JOIN para recuperar las ciudades sin registros de usuarios:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
  AND users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

La condición para incluir sólo usuarios con edades inferiores a 30 años se establece en el predicado JOIN. El resultado es el siguiente:

Todas las ciudades aparecen en la lista, y sólo los usuarios con edades dentro del rango devuelven un número distinto de cero. Las ciudades que no tienen usuarios que coincidan con nuestros criterios devuelven un cero.

¿Qué habría pasado si pusiéramos la misma condición de filtrado en la cláusula WHERE?

Uso de las condiciones WHERE

Si colocamos las mismas condiciones en la cláusula WHERE, se vería así:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id 
WHERE users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

Esto dará como resultado:

Esto no es lo que esperaba; quería obtener TODAS las ciudades y un recuento de sus respectivos usuarios menores de 30 años. Aunque una ciudad no tuviera usuarios, debería haber aparecido con un recuento cero, tal y como devuelve el ejemplo del predicado JOIN.

La razón por la que esto no devuelve esos registros es porque WHERE las condiciones se aplican después del JOIN. Como la condición users.age < 30 elimina todos los registros de "Coyote Springs" y "Orlando", el cálculo resumido no puede incluir estos valores. Sólo "Las Vegas" y "Miami" cumplen las condiciones de WHERE, por lo que sólo se devuelven "Las Vegas" y "Miami".

Por el contrario, cuando la condición se aplica en el predicado JOIN, los registros de usuarios sin edad coincidente se eliminan antes de unir las dos tablas. A continuación, todas las ciudades coinciden con las columnas de los usuarios, tal y como se espera cuando se utiliza un predicado LEFT JOIN. Esto significa que todas las ciudades formarán parte de los resultados; sólo se filtran los registros de usuarios que no cumplen la condición de users.age < 30. En este caso, el predicado JOIN devuelve el resultado deseado.

Uso de las condiciones HAVING

Lo mencionamos en el primer artículo, pero lo repetiremos aquí: usar la cláusula WHERE para filtrar columnas resumidas no funciona. Mira el ejemplo de abajo.

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
WHERE COUNT(users.id) > 2
GROUP BY cities.cityname
ORDER BY cities.cityname;

Esto hace que la base de datos emita una queja como esta de MySQL:

Error Code: 1111. Invalid use of group function

En su lugar, utilice la cláusula HAVING:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname
HAVING COUNT(users.id) > 2
ORDER BY cities.cityname;

Esto devuelve los registros previstos (sólo las ciudades con más de dos usuarios):

Cómo lidiar con los NULL

Además de los casos límite ya presentados, es importante considerar algo que no es tan obvio. Volvamos al ejemplo de COUNT():

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Este devuelve:

Si hubiera utilizado COUNT(*) en lugar de COUNT(users.id), se habría generado el recuento total de filas. Esto nos habría dado un valor no deseado - en este caso, un falso "1" para "Coyote Springs". Este resultado se debe a la naturaleza de LEFT JOIN. He aquí un ejemplo:

SELECT cities.cityname, COUNT(*)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Esto devolvería:

Así que COUNT(*) está contando un "1" para Coyote Springs porque el LEFT JOIN está devolviendo una fila con valores NULL. Recuerde que en COUNT(*), una fila con NULLs sigue contando.

Por la misma razón, COUNT(users.id) devuelve el recuento esperado de "0"; el valor de la columna users.id es nulo para Coyote Springs.

En otras palabras, utilice siempre Count(column) con este tipo de consultas.

Un último consejo para trabajar con las funciones agregadas de SQL

Por último, me gustaría añadir que para trabajar con las funciones agregadas de SQL, especialmente cuando se utiliza JOIN, es necesario entender el lenguaje SQL y los datos con los que se trabaja. Pruebe primero las consultas en un subconjunto más pequeño de sus datos para confirmar que todos los cálculos funcionan como se espera. Si es posible, compruebe algunas salidas con un valor de referencia para validar los resultados de sus consultas.

Tenga en cuenta que utilizar condiciones en el predicado JOIN (después de ON) no es lo mismo que filtrar en WHERE (o utilizar HAVING). Esto puede crear diferencias sutiles (o no tan sutiles) en los datos resumidos, lo que podría dar lugar a errores difíciles de detectar. Preste especial atención a sus opciones de filtrado.

Como siempre, gracias por leer y no dude en compartir sus propias experiencias en la sección de comentarios.