Evolutionary algorithms

SQL incluye un conjunto de funciones estándar predefinidas que nos permiten obtener valores resumen sobre los resultados de una consulta. Se denominan funciones de agregado o de grupo.

7.1.1. Funciones de grupo estándar.

Las funciones de agregado estándar en SQL son:

▪ AVG. Calcula la media aritmética de un conjunto de valores contenidos en los registros especificados de una consulta. Su sintaxis es la siguiente

En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo con los datos de un registro. La media calculada por AVG es la media aritmética (la suma de los valores dividido por el número de valores). Para realizar el cálculo resumen, la función AVG no incluye a ningún registro con la expresión a NULL.

Por ejemplo, la siguiente consulta obtiene la media de las notas del alumno de nif '11111111A':

SELECT AVG(valor) AS "Nota media" FROM Nota WHERE nif='11111111A';

▪ COUNT. Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente

COUNT(expr)

En donde expr puede ser el símbolo *, el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario, pero no otras de las funciones de agregado de SQL). Puede contar cualquier tipo de datos, incluso texto.

Aunque expr puede realizar un cálculo sobre un campo, COUNT simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función COUNT no cuenta los registros cuya expresión evalúa a NULL a menos que expr sea el carácter comodín asterisco (*). En ese sentido,

COUNT(*) es considerablemente más eficiente que COUNT(expresión). La siguiente consulta calcula el número de alumnos registrados:

SELECT COUNT(*) AS Total FROM Alumno;

Si el campo localidad admite valores nulos, la siguiente consulta calcula el número de alumnos registrados que tengan asignada alguna localidad:

SELECT COUNT(localidad) AS Total FROM Alumno;

También se puede especificar la cláusula DISTICNT en la expresión para no contar aquellos registros con valores repetidos en dicha expresión. Por ejemplo, la siguiente consulta cuenta el número de alumnos que viven en localidades distintas:

SELECT COUNT( DISTINCT localidad) AS Total FROM Alumno;

▪ MAX, MIN. Devuelven el mínimo o el máximo de un conjunto de valores obtenidos de una expresión de una consulta. Su sintaxis es:

MIN(expr) MAX(expr)

En donde expr es el campo o expresión sobre el que se desea realizar el cálculo. Expr puede incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario, pero no otras de las funciones de agregado de SQL).

La siguiente consulta obtiene la nota mínima y máxima del alumno de nif '11111111A':

SELECT MIN(valor) AS 'Nota mínima', MAX(valor) AS 'Nota máxima' FROM Nota

WHERE nif = '11111111A';

▪ SUM. Devuelve la suma del conjunto de valores obtenidos de una expresión de una consulta. Su sintaxis es:

SUM(expr)

En donde expr representa el nombre de un campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo sobre un registro. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones de agregado de SQL).

La siguiente consulta obtiene la suma de las notas del alumno de nif '11111111A':

SELECT SUM(valor) AS 'Suma de notas' FROM Nota WHERE nif = '11111111A'; 7.1.2. Funciones de agregado propias de Oracle.

Además, Oracle añade otro conjunto propio de funciones de grupo:

▪ CORR(expr1, expr2). Calcula coeficientes de correlaciones de un conjunto de pares de números. La función CORR_K soporta correlaciones no parametrizadas, y la función CORR_S soporta correlaciones de fila.

▪ COVAR_POP(expr1, expr2). Calcula la covarianza demográfica de un conjunto de pares de valores. ▪ COVAR_SAMP(expr1, expr2). Calcula la covariancia típica de un conjunto de pares de valores. ▪ MEDIAN(expr). Retorna el valor medio de un grupo de valores ignorando los nulos.

▪ STDDEV(expr).Calcula la desviación estándar de todos los valores. ▪ STDDEV_POP(expr). Calcula de desviación demográfica estándar. ▪ STDDEV_SAMP(expr). Calcula la desviación típica.

▪ VAR_POP(expr). Calcula la variancia demográfica. ▪ VAR_SAMP(expr). Calcula la variancia típica.

▪ VARIANCE(expr). Calcula la variancia de todos los valores del grupo.

▪ WIDTH_BUCKET(expr,min,max,num). Permite crear histogramas por igualdad de tamaño.

7.1.3. Cómo afectan los nulos en valores de funciones de grupo.

Las funciones de grupo tratan los valores NULL de forma diferente a cómo lo hacen las funciones de valor simple. Las funciones de grupo ignoran los valores nulos y calculan el resultado sin tenerlos en cuenta. Sin embargo, una función de agregado que realiza cálculos sólo sobre valores nulos siempre retorna el valor NULL

(excepto COUNT).

Para analizar como afectan los nulos a las funciones de grupo, tomemos AVG como ejemplo. Supongamos que tenemos una lista de 100 amigos y sus edades. Si cogemos 20 al azar y calculamos el promedio de sus edades, nos encontraremos con un valor cercano a si calculamos el promedio de otra lista diferente de 20 amigos elegidos al azar o el promedio sobre los 100 amigos. Esto es así porque AVG es bastante insensible a la falta de registros, incluso si representan un alto porcentaje del número total de registros disponibles.

Esta relativa insensibilidad de AVG ante la falta de datos puede ser contrastada con la función SUM. Si calculamos la suma de edades sobre 20 amigos obtendremos valores muy diferentes si hacemos el cálculo sobre otros grupos.

Si ahora, de los 100 amigos, sólo 20 proporcionan una edad y el resto tienen la edad a valor NULL, ¿qué estadística sería más fiable sobre el grupo entero y menos sensible a la ausencia de datos? Si no sabemos cuántos registros están a NULL, podemos usar la siguiente consulta para obtener un resultado razonable respecto al promedio de edades:

SELECT AVG(edad) FROM Amigo;

Sin embargo, no podemos obtener un resultado razonable para sumas de edades con:

SELECT SUM(edad) FROM Amigo;

Otras funciones relativamente insensibles a los nulos son STDDEV y VARIANCE.

Las funcione MAX y MIN obtienen valores extremos sobre nuestros datos. Pueden fluctuar desordenadamente mientras que AVG se queda relativamente constante. Si añadimos un hombre de 100 años a un grupo de 99 personas que tienen 50 años, la edad media sólo se acerca a 50.5, pero la edad máxima se ha doblado. Si añadimos un bebé recién nacido, el promedio vuelve a 50, pero la edad mínima es ahora 0. Parece claro que la omisión o desconocimiento de valores nulos puede afectar profundamente a MAX, MIN y SUM, así que tenemos que ser cautelosos usándolos, en particular si un porcentaje significativo de los datos es NULL.

La función COUNT es un caso especial. Esta función siempre retorna un valor diferente de NULL. Si se evalúa sobre una expresión, cuenta el número de filas donde la expresión es distinta de NULL. Esto quiere decir que la consulta:

SELECT COUNT(edad) FROM Amigo;

Retorna el valor 20, puesto que en 80 filas el valor para edad es NULL. Sin embargo, la consulta:

SELECT COUNT(*) FROM Amigo;

Retorna el valor 100, puesto que no se tendrán en cuenta los nulos, y simplemente contará todas las filas existentes.

7.1.4. Combinando funciones de grupo y de valor simple.

Podemos combinar fácilmente funciones de grupo con funciones simples para obtener resultados más complejos o ajustados. Por ejemplo, supongamos que queremos obtener para el año 2010 el último mes en el cual un alumno faltó en cualquier módulo. La tabla FALTA proporciona un campo fecha sobre el cual podemos aplicar la función MAX para obtener la fecha más próxima, y después podemos aplicar una función simple como TO_CHAR para extraer el mes de la fecha. En este caso podemos obtener dos soluciones equivalentes:

SELECT MAX( TO_CHAR(fecha, 'MM') )

FROM FALTA WHERE idMatricula=1 AND TO_CHAR(fecha, 'YYYY')=2010; /

SELECT TO_CHAR( MAX(fecha), 'MM')

FROM FALTA WHERE idMatricula=1 AND TO_CHAR(fecha, 'YYYY')=2010;

Podemos combinar funciones de valor simple dentro de funciones de grupo y viceversa, aunque el orden de aplicación de las funciones no siempre produce los mismos resultados. En el ejemplo previo, la primera consulta aplica la función de valor simple sobre cada fila obteniendo un número de mes, y la función de grupo calcula el valor más alto. La segunda consulta aplica la función de grupo para obtener la fecha más

próxima, y la función de valor simple extrae el valor de mes de dicha fecha.

Sin embargo, si intentamos anidar funciones de grupo entre sí produciremos errores. Por ejemplo

SELECT SUM( AVG(valor)) FROM NOTA;

Provocará el error:

ORA-00978: función de grupo anidada sin GROUP BY

Además, si esto en realidad funcionase debería producir el mismo resultado que AVG(valor). Puesto que

AVG(valor) produce un resultado simple, el SUM de un valor simple es justo el propio valor simple.

Lo que sí podemos hacer es operar con los resultados de funciones de grupo. Por ejemplo, la siguiente consulta sería válida:

SELECT MAX(valor) - MIN(valor) FROM NOTA; 7.1.5. «DISTINCT» en funciones de grupo.

Todas las funciones de grupo admiten la opción DISTINCT o ALL. Cuando no se indica, se toma la opción ALL

por defecto.

El significado de estas opciones podemos verlo con un ejemplo de uso en la función COUNT:

SELECT COUNT(DISTINCT localidad) "Nº localidades", COUNT(localidad) "Nº registros", COUNT(*) FROM ALUMNO;

Un posible resultado podría ser el siguiente

Nº localidades Nº registros COUNT(*) --- --- ---

10 25 30

La opción DISTINCT fuerza que COUNT cuente sólo el número de filas con localidades diferentes; mientras que la opción ALL (por defecto) cuenta todas las filas donde el valor de localidad no sea NULL aunque se repitan nombres de localidades. En este ejemplo, por los resultados, se ve que hay 5 filas donde la localidad está a valor nulo.

Aunque puede hacerse, el uso de DISTINCT sobre las demás funciones de grupo suele ser raro, excepto para algún tipo de cálculo estadístico. MAX y MIN producen el mismo resultado con o sin DISTINCT.

7.2. La cláusula «Group by».

La cláusula GROUP BY permite realizara agrupaciones lógicas entre los registros de una tabla de consulta, de tal forma que dentro de cada grupo todas las filas tengan el mismo valor en los campos indicados con GROUP BY. Esto es sumamente útil cuando queremos utilizar funciones de agregado sobre grupos de registros en vez de sobre todos los registros.

Por ejemplo, para obtener los apellidos y nota media de los alumnos matriculados en el curso 2004:

SELECT A.apellidos, AVG(N.valor)

FROM Alumno A JOIN Matricula M ON A.nif=M.nif JOIN Nota N ON A.nif=N.nif WHERE M.año=2004

GROUP BY A.apellidos ;

La consulta realiza el join entre las tablas Alumno, Matricula y Nota, descartando aquellas filas que no cumplen la condición. Las filas válidas se agrupan por el mismo nombre de alumno. De cada grupo se calcula la media del campo valor. En la tabla resultante, por cada grupo, se muestra un registro con los apellidos y el promedio calculado.

GROUP BY no implica ordenación. Si queremos presentar el resultado ordenado por apellidos debemos especificar la cláusula «ORDER BY A.apellidos» después de la cláusula GROUP BY.

Cada expresión en las columnas de la cláusula SELECT debe producir un único valor por grupo; es decir, puede ser un campo (o alguna expresión sobre dicho campo) de GROUP BY, o un literal, o una función de agregado.

La cláusula GROUP BY también admite expresiones. Por ejemplo, la siguiente consulta obtiene el número de faltas de cada alumno por mes en el año 2008:

SELECT M.nif, EXTRACT(Month FROM fecha) AS "Mes", COUNT(*) AS "Nº de faltas" FROM Matricula M JOIN Falta F ON M.idMatricula=F.idMatricula

WHERE M.año=2008

GROUP BY EXTRACT(Month FROM fecha), M.nif ;

Una consideración: en la consulta anterior no podríamos incluir el campo fecha como una columna del

SELECT. Esto es así porque fecha no se corresponde con una expresión de la cláusula GROUP BY.

7.2.1. Empleo de HAVING.

también GROUP BY). Es decir, HAVING permite indicar condiciones para filtrar grupos de la misma manera que

WHERE nos permite filtrar las filas de las tablas base. Las expresiones de condición en la cláusula HAVING

deben producir un solo valor por grupo.

En el siguiente ejemplo se listan los alumnos matriculados y el número de faltas de asistencia cuando superan las 4 faltas:

SELECT M.nif, COUNT(*) AS "Nº de faltas"

FROM Matricula M JOIN Falta F ON M.idMatricula=F.idMatricula GROUP BY M.nif

HAVINGCOUNT(*) > 4

8. Subconsultas