Cómo Combinar Dos Funciones Agregadas en SQL

¿Tiene problemas para utilizar dos funciones agregadas en una consulta? Este artículo le mostrará cómo hacerlo de la manera correcta - en realidad, de las dos maneras correctas.

En el análisis de datos y la elaboración de informes, a menudo necesitamos contar el número de registros o sumarlos y luego calcular la media de este recuento o suma.

Traducido a la lógica de SQL, esto es la agregación de datos agregados, o la agregación multinivel. Para la agregación, existen las funciones de agregación de SQL. Y para la agregación multinivel, utilizarías (al menos) dos funciones de agregación al mismo tiempo.

Si te interesa la calidad de los informes, necesitarás mucho más que las funciones de agregación de SQL. Sin embargo, son ciertamente la base de un buen informe. La mejor manera de construir sobre esa base es tomar nuestro curso interactivo Cómo crear informes básicos con SQL interactivo. Ofrece una sección entera dedicada exclusivamente a calcular agregaciones de varios niveles en SQL y a resumir datos utilizando funciones de agregación. También cubre la clasificación de datos utilizando CASE WHEN y GROUP BY, el cálculo de múltiples métricas en un informe, y la comparación de grupos dentro del mismo informe. Con 97 ejercicios interactivos, este curso le ayudará a dominar la elaboración de informes en SQL.

Para hacerlo aún más fácil, te mostraré aquí cómo combinar dos funciones de agregación en SQL. Si tu conocimiento de las funciones de agregación está oxidado, esta guía de funciones de agregación de SQL puede ayudarte a seguir este artículo. También puedes practicar con estos ejemplos de funciones agregadas.

Datos de ejemplo

Tenemos una tabla llamada new_users. Recoge datos sobre los nuevos usuarios de una app en el mercado sudamericano. Las columnas son:

• id - El ID de cada registro individual.
• date - La fecha de ingreso de los usuarios.
• number_of_new_users - El número de nuevos usuarios por fecha.
• city - La ciudad de los usuarios.
• country - El país de ubicación de los usuarios.

A continuación se muestran dos fechas en total, y cada fecha tiene un número diferente de usuarios de dos países y dos ciudades de cada país.

¿Cómo utilizarías las funciones AVG() y SUM() para calcular el número medio diario de nuevos usuarios por país?

La solución ingenua

La lógica detrás del enfoque ingenuo es, en realidad, bastante sólida. Es una pena que esta solución ingenua no sea una solución en absoluto. Pronto se dará cuenta de por qué.

Si sólo se piensa en el enfoque matemático, es sencillo: primero hay que sumar el número de nuevos usuarios por país cada día, y luego calcular la media de esa suma.

Trasladado a funciones SQL, parece lógico escribir algo así:

SELECT country,
 	 AVG(SUM(number_of_new_users)) AS average_new_daily_users
FROM new_users
GROUP BY country;

¿Por qué no? Hay una razón muy sencilla por la que no: SQL no permite el anidamiento de funciones de agregación. En otras palabras, no puedes usar una función agregada dentro de otra función agregada. Bueno, sí se puede, pero la consulta devolverá un error diciendo exactamente lo que acabo de decir:

¿Cómo se arregla esta consulta para combinar dos funciones agregadas en SQL?

Hay dos opciones: usar una subconsulta o usar Expresiones Comunes de Tabla (CTEs).

Solución real 1: Subconsulta

La primera opción es combinar dos funciones agregadas utilizando una subconsulta. La subconsulta es una consulta dentro de la consulta principal. Al crear informes, suelen encontrarse en las cláusulas SELECT, FROM, o WHERE.

En este ejemplo, pondré la subconsulta en la cláusula FROM.

SELECT country,
	 AVG(ds.sum_new_users) AS average_daily_new_users
FROM (SELECT date,
	  	 country,
	 	 SUM(number_of_new_users) AS sum_new_users
	  FROM new_users
	  GROUP BY date, country) AS ds
GROUP BY country;

El principio al combinar dos funciones de agregación es utilizar la subconsulta para calcular la estadística "interna". A continuación, el resultado se utiliza en las funciones de agregación de la consulta externa.

El código anterior selecciona la fecha y el país y calcula la suma de la columna number_of_new_users. Esto devuelve el número total diario de nuevos usuarios. He dado a la subconsulta el alias ds, que es la abreviatura de "suma diaria".

Una vez obtenida la suma diaria, la he referenciado en la consulta externa calculando la media de la columna ds.sum_new_users, es decir, la media de los nuevos usuarios diarios. Quiero que esta media sea por país; por eso he agrupado los datos por la columna país.

Solución real 2: CTE

La otra opción para combinar funciones de agregación en SQL es utilizar una CTE en lugar de una subconsulta. Una CTE es una versión más ordenada y "más cercana a la lógica matemática" de una subconsulta. Es una expresión que permite crear un resultado temporal, al que se puede hacer referencia en otra sentencia SELECT. Puede utilizar el resultado de una CTE como lo haría con cualquier otra tabla. La diferencia es que el resultado de la CTE sólo existe cuando se ejecuta una CTE junto con la consulta que utiliza la CTE. Puede encontrar una explicación más detallada de las CTEs en el artículo ¿Qué es una CTE?

La consulta siguiente le dará el mismo resultado que la solución de la subconsulta:

WITH ds AS (
  SELECT date, 
   country,
         SUM(number_of_new_users) AS sum_new_users
  FROM new_users
  GROUP BY date, country)

SELECT country,
       AVG(ds.sum_new_users) AS average_daily_new_users
FROM ds
GROUP BY country;

Cada CTE se introduce utilizando la palabra clave WITH. Lo mismo ocurre con mi CTE llamado ds. Después de la palabra clave AS viene la definición del CTE. En este caso, es la misma sentencia SELECT que en la subconsulta del ejemplo anterior. Aquí viene el sum(): devolverá, de nuevo, la suma de nuevos usuarios por país y fecha.

La segunda sentencia SELECT hace referencia a CTE y calcula la media de la suma devuelta por CTE.

A diferencia del anidamiento de la subconsulta, el uso del CTE permite utilizar las funciones de agregación en el orden lógico: primero SUM(), luego AVG(). La función "interna" se utiliza en el CTE, mientras que la segunda sentencia SELECT es para la función agregada "externa".

El resultado será el mismo que con la subconsulta; no hace falta que lo vuelva a mostrar. Sin embargo, siempre es bueno aprender más sobre las diferencias entre una subconsulta y una CTE y luego practicar el uso de CTEs en ejemplos de la vida real.

Conviértase en un maestro de la agregación de datos y los informes

El problema de utilizar dos funciones de agregación en SQL es omnipresente. Cuanto más compleja sea la elaboración de informes, más utilizará las agregaciones de varios niveles. Ahora sabes que las subconsultas y los CTEs son las dos soluciones para ello.

Todos los demás matices de los informes se pueden encontrar y practicar en nuestro Cómo crear informes básicos con SQL curso. Los CTEs pueden hacer que sus informes sean más fáciles y sofisticados, por lo que tomar el Consultas recursivas y expresiones de tabla comunes curso sería una sabia idea.