Agregación

• Presentación
• Cuenta las lecturas de temperatura
• Solución
• Promedio con NULL
• Solución
• ¿Qué hace esta consulta?
• Solución
• Ordenación al Concatenar
• Puntos clave

Presentación

Enseñanza: 10 min
Ejercicios: 10 min

Preguntas

• ¿Cómo puedo calcular sumas, promedios y otros valores de resumen?

Objetivos

• Definir la agregación y dar ejemplos de su uso.

• Escribir consultas que calculen valores agregados.

• Rastrear la ejecución de una consulta que realice la agregación.

• Explicar cómo se manejan los datos faltantes durante la agregación.

Ahora queremos calcular rangos y promedios para nuestros datos.Sabemos cómo seleccionar todas las fechas de la tabla Visited:

SELECT dated FROM Visited;

-nula-

pero para combinarlas, debemos utilizar una función de agregación como min o max.Cada una de estas funciones toma un conjunto de registros como entrada, y produce un único registro como salida:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min y max son sólo dos de las funciones de agregación integradas en SQL.Otras tres son avg, count y sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

Aquí utilizamos count(reading),pero también podríamos haber contado quant o cualquier otro campo de la tabla, o incluso utilizar count(*), ya que a la función no le importan los valores en sí, sino cuántos hay.

SQL nos permite hacer varias agregaciones a la vez.Podemos, por ejemplo, encontrar el rango de medidas de salinidad sensible:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

También podemos combinar los resultados agregados con los resultados brutos, aunque la salida podría sorprenderle:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

¿Por qué aparece el nombre de Lake y no el de Roerich o Dyer?La respuesta es que cuando tiene que agregar un campo, pero no se le dice cómo hacerlo, el gestor de la base de datos elige un valor real del conjunto de entrada.Puede utilizar el primero procesado, el último, o algo totalmente distinto.

Otro hecho importante es que cuando no hay valores para agregar -por ejemplo, cuando no hay filas que satisfagan la cláusula WHERE – el resultado de la agregación es «no sé» en lugar de cero o algún otro valor arbitrario:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

Una última característica importante de las funciones de agregación es que son inconsistentes con el resto de SQL de una manera muy útil.Si sumamos dos valores y uno de ellos es nulo, el resultado es nulo. Por extensión, si utilizamos sum para sumar todos los valores de un conjunto y alguno de ellos es nulo, el resultado también debería ser nulo.Este comportamiento nos permite escribir nuestras consultas como:

SELECT min(dated) FROM Visited;

en lugar de tener que filtrar siempre explícitamente:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

Agregar todos los registros a la vez no siempre tiene sentido.Por ejemplo, supongamos que sospechamos que hay un sesgo sistemático en nuestros datos, y que las lecturas de radiación de algunos científicos son más altas que otras.Sabemos que esto no funciona:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

porque el gestor de la base de datos selecciona un único nombre de científico arbitrario en lugar de agregarlo por separado para cada científico.Como sólo hay cinco científicos, podríamos escribir cinco consultas de la forma

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

Pero esto sería tedioso, y si alguna vez tuviéramos un conjunto de datos con cincuenta o quinientos científicos, las probabilidades de que acertáramos todas esas consultas son pequeñas.

Lo que tenemos que hacer es decirle al gestor de la base de datos que agregue las horas de cada científico por separado utilizando una cláusula GROUP BY:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

GROUP BY hace exactamente lo que su nombre indica: agrupa todos los registros con el mismo valor para el campo especificado para que la agregación pueda procesar cada lote por separado.Como todos los registros de cada lote tienen el mismo valor para person, ya no importa que el gestor de la base de datos elija uno arbitrario para mostrarlo junto a los valores agregados de reading.

Al igual que podemos ordenar por múltiples criterios a la vez, también podemos agrupar por múltiples criterios.Para obtener la lectura media por científico y cantidad medida, por ejemplo, sólo tenemos que añadir otro campo a la cláusula GROUP BY:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

Nótese que hemos añadido quant a la lista de campos mostrados, ya que de otra forma los resultados no tendrían mucho sentido.

Vayamos un paso más allá y eliminemos todas las entradas en las que no sabemos quién ha tomado la medida:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

Mirando más de cerca, esta consulta:

1. seleccionó registros de la tabla Survey en los que el campo person no era nulo;

2. agrupó esos registros en subconjuntos para que los valores person y quant de cada subconjunto fueran los mismos;

3. ordenó esos subconjuntos primero por person, y luego dentro de cada subgrupo por quant; y

4. contó el número de registros en cada subconjunto, calculó la media reading en cada uno, y eligió un valor person y quant de cada uno (no importa cuáles, ya que todos son iguales).

Cuenta las lecturas de temperatura

¿Cuántas lecturas de temperatura registró Frank Pabodie, y cuál fue su valor medio?

Solución

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

contar(lectura)	avg(lectura)
2	-20.0

Promedio con NULL

La media de un conjunto de valores es la suma de los valoresdividida por el número de valores.¿Significa esto que la función avg devuelve 2,0 o 3,0 cuando se dan los valores 1,0, null y 5,0?

Solución

La respuesta es 3,0.NULL no es un valor; es la ausencia de un valor.Como tal, no se incluye en el cálculo.

Puedes confirmarlo ejecutando este código:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

¿Qué hace esta consulta?

Queremos calcular la diferencia entre cada lectura de radiación individual y la media de todas las lecturas de radiación.Escribimos la consulta:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

¿Qué produce esto realmente, y puedes pensar por qué?

Solución

La consulta produce sólo una fila de resultados cuando lo que realmente queremos es un resultado para cada una de las lecturas.La función avg() produce sólo un único valor, y porque se ejecuta primero, la tabla se reduce a una sola fila.El valor reading es simplemente arbitrario.

Para conseguir lo que queríamos, tendríamos que ejecutar dos consultas:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Esto produce el valor medio (6.5625), que luego podemos insertar en una segunda consulta:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Esto produce lo que queremos, pero podemos combinarlo en una sola consulta utilizando subconsultas.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

De esta forma no tenemos que ejecutar dos consultas.

En resumen lo que hemos hecho es sustituir avg(reading) por (SELECT avg(reading) FROM Survey WHERE quant='rad') en la consulta original.

Ordenación al Concatenar

La función group_concat(field, separator)concatena todos los valores de un campo utilizando el carácter separador especificado(o ‘,’ si no se especifica el separador).Utilícela para producir una lista de una línea con los nombres de los científicos, como por ejemplo:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

¿Puede encontrar una forma de ordenar la lista por apellidos?

Puntos clave

• Utiliza las funciones de agregación para combinar múltiples valores.

• Las funciones de agregación ignoran los valores null.

• La agregación se produce después del filtrado.

• Utilizar GROUP BY para combinar subconjuntos por separado.

• Si no se especifica ninguna función de agregación para un campo, la consulta puede devolver un valor arbitrario para ese campo.