Conslusiòn del 31 al 43

31 y 32 Tema 4. Las consultas de resumen (I) Introducción En SQL de Microsoft Jet 4.x y de la mayoría de los motores de bases de datos relacionales, podemos definir un tipo de consultas cuyas filas resultantes son un resumen de las filas de la tabla origen, por eso las denominamos consultas de resumen, también se conocen como consultas sumarias. Es importante entender que las filas del resultado de una consulta de resumen tienen una naturaleza distinta a las filas de las demás tablas resultantes de consultas, ya que corresponden a varias filas de la tabla orgen. Para simplificar, veamos el caso de una consulta basada en una sola tabla, una fila de una consulta 'no resumen' corresponde a una fila de la tabla origen, contiene datos que se encuentran en una sola fila del origen, mientras que una fila de una consulta de resumen corresponde a un resumen de varias filas de la tabla origen, esta diferencia es lo que va a originar una serie de restricciones que sufren las consultas de resumen y que veremos a lo largo del tema. En el ejemplo que viene a continuación tienes un ejemplo de consulta normal en la que se visualizan las filas de la tabla oficinas ordenadas por region, en este caso cada fila del resultado se corresponde con una sola fila de la tabla oficinas, mientras que la segunda consulta es una consulta resumen, cada fila del resultado se corresponde con una o varias filas de la tabla oficinas.

Las consultas de resumen introducen dos nuevas cláusulas a la sentencia SELECT, la cláusula GROUP BY y la cláusula HAVING, son cláusulas que sólo se pueden utilizar en una consulta de resumen, se tienen que escribir entre la cláusula WHERE y la cláusula ORDER BY y tienen la siguiente sintaxis:

Las detallaremos en la página siguiente del tema, primero vamos a introducir otro concepto relacionado con las consultas de resumen, las funciones de columna. 33, 34 y 35 Funciones de columna En la lista de selección de una consulta de resumen aparecen funciones de columna también denominadas funciones de dominio agregadas. Una función de columna se aplica a una columna y obtiene un valor que resume el contenido de la columna. Tenemos las siguientes funciones de columna:

El argumento de la función indica con qué valores se tiene que operar, por eso expresión suele ser un nombre de columna, columna que contiene los valores a resumir, pero también puede ser cualquier expresión válida que devuelva una lista de valores. La función SUM() calcula la suma de los valores indicados en el argumento. Los datos que se suman deben ser de tipo numérico (entero, decimal, coma flotante o monetario...). El resultado será del mismo tipo aunque puede tener una precisión mayor. Ejemplo: SELECT SUM(ventas) FROM oficinas Obtiene una sola fila con el resultado de sumar todos los valores de la columna ventas de la tabla oficinas. La función AVG() calcula el promedio (la media arimética) de los valores indicados en el argumento, también se aplica a datos numéricos, y en este caso el tipo de dato del resultado puede cambiar según las necesidades del sistema para representar el valor del resultado. StDev() y StDevP() calculan la desviación estándar de una población o de una muestra de la población representada por los valores contenidos en la columna indicada en el argumento. Si la consulta base (el origen) tiene menos de dos registros, el resultado es nulo. Es interesante destacar que el valor nulo no equivale al valor 0, las funciones de columna no consideran los valores nulos mientras que consideran el valor 0 como un valor, por lo tanto en las funciones AVG(), STDEV(), STDEVP() los resultados no serán los mismos con valores 0 que con valores nulos. Veámoslo con un ejemplo: Si tenemos esta tabla:

La consulta SELECT AVG(col1) AS media FROM tabla1 devuelve:

En este caso los ceros entran en la media por lo que sale igual a 4 (10+5+0+3+6+0)/6 = 4 Con esta otra tabla:

SELECT AVG(col1) AS media FROM tabla2 devuelve:

En este caso los ceros se han sustituido por valores nulos y no entran en el cálculo por lo que la media sale igual a 6 (10+5+3+6)/4 = 4 Las funciones MIN() y MAX() determinan los valores menores y mayores respectivamente. Los valores de la columna pueden ser de tipo numérico, texto o fecha. El resultado de la función tendrá el mismo tipo de dato que la columna. Si la columna es de tipo numérico MIN() devuelve el valor menor contenido en la columna, si la columna es de tipo texto MIN() devuelve el primer valor en orden alfabético, y si la columna es de tipo fecha, MIN() devuelve la fecha más antigua y MAX() la fecha más reciente. La función COUNT(nb columna) cuenta el número de valores que hay en la columna, los datos de la columna pueden ser de cualquier tipo, y la función siempre devuelve un número entero. Si la columna contiene valores nulos esos valores no se cuentan, si en la columna aparece un valor repetido, lo cuenta varias veces. COUNT(*) permite contar filas en vez de valores. Si la columna no contiene ningún valor nulo, COUNT(nbcolumna) y COUNT(*) devuelven el mismo resultado, mientras que si hay valores nulos en la columna, COUNT(*) cuenta también esos valores mientras que COUNT(nb columna) no los cuenta. Ejemplo: ¿Cuántos empleados tenemos? SELECT COUNT(numemp) FROM empleados o bien SELECT COUNT(*) FROM empleados En este caso las dos sentencias devuelen el mismo resultado ya que la columna numemp no contiene valores nulos (es la clave principal de la tabla empleados). ¿Cuántos empleados tienen una oficina asignada? SELECT COUNT(oficina) FROM empleados Esta sentencia por el contrario, nos devuelve el número de valores no nulos que se encuentran en la columna oficina de la tabla empleados, por lo tanto nos dice cuántos empleados tienen una oficina asignada. Se pueden combinar varias funciones de columna en una expresión pero no se pueden anidar funciones de columna, es decir: SELECT (AVG(ventas) * 3) + SUM(cuota) FROM ... es correcto SELECT AVG(SUM(ventas)) FROM ... NO es correcto, no se puede incluir una función de columna dentro de una función de columna 36 Selección en el origen de datos. Si queremos eliminar del origen de datos algunas filas, basta incluir la cláusula WHERE que ya conocemos después de la cláusula FROM. Ejemplo: Queremos saber el acumulado de ventas de los empleados de la oficina 12. SELECT SUM(ventas) FROM empleados WHERE oficina = 12 Origen múltiple. Si los datos que necesitamos utilizar para obtener nuestro resumen se encuentran en varias tablas, formamos el origen de datos adecuado en la cláusula FROM como si fuera una consulta multitabla normal. Ejemplo: Queremos obtener el importe total de ventas de todos los empleados y el mayor objetivo de las oficinas asignadas a los empleados: SELECT SUM(empleados.ventas), MAX(objetivo) FROM empleados LEFT JOIN oficinas ON empleados.oficina=oficinas.oficina NOTA: combinamos empleados con oficinas por un LEFT JOIN para que aparezcan en el origen de datos todos los empleados incluso los que no tengan una oficina asignada, así el origen de datos estará formado por una tabla con tantas filas como empleados hayan en la tabla empleados, con los datos de cada empleado y de la oficina a la que está asignado. De esta tabla sacamos la suma del campo ventas (importe total de ventas de todos los empleados) y el objetivo máximo. Observar que el origen de datos no incluye las oficinas que no tienen empleados asignados, por lo que esas oficinas no entran a la hora de calcular el valor máximo del objetivo. 37, 38, 39 Tema 4. Las consultas de resumen (II) La cláusula GROUP BY

Hasta ahora las consultas de resumen que hemos visto utilizan todas las filas de la tabla y producen una única fila resultado. Se pueden obtener subtotales con la cláusula GROUP BY. Una consulta con una cláusula GROUP BY se denomina consulta agrupada ya que agrupa los datos de la tabla origen y produce una única fila resumen por cada grupo formado. Las columnas indicadas en el GROUP BY se llaman columnas de agrupación. Ejemplo: SELECT SUM(ventas) FROM repventas Obtiene la suma de las ventas de todos los empleados. SELECT SUM(ventas) FROM repventas GROUP BY oficina Se forma un grupo para cada oficina, con las filas de la oficina, y la suma se calcula sobre las filas de cada grupo. El ejemplo anterior obtiene una lista con la suma de las ventas de los empleados de cada oficina. La consulta quedaría mejor incluyendo en la lista de selección la oficina para saber a qué oficina corresponde la suma de ventas: SELECT oficina,SUM(ventas) FROM repventas GROUP BY oficina Un columna de agrupación no puede ser de tipo memo u OLE. La columna de agrupación se puede indicar mediante un nombre de columna o cualquier expresión válida basada en una columna pero no se pueden utilizar los alias de campo. Ejemplo: SELECT importe/cant , SUM(importe) FROM pedidos GROUP BY importe/cant Está permitido, equivaldría a agrupar las líneas de pedido por precio unitario y sacar de cada precio unitario el importe total vendido. SELECT importe/cant AS precio, SUM(importe) FROM pedidos GROUP BY precio No está permitido, no se puede utilizar un alias campo. En la lista de selección sólo pueden aparecer : valores constantes funciones de columna columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) o cualquier expresión basada en las anteriores. SELECT SUM(importe),rep*10 FROM pedidos GROUP BY rep*10 Está permitido SELECT SUM(importe),rep FROM pedidos GROUP BY rep*10 No está permitido, rep es una columna simple que no está encerrrada en una función de columna, ni está en la lista de columnas de agrupación. Se pueden agrupar las filas por varias columnas, en este caso se indican las columnas separadas por una coma y en el orden de mayor a menor agrupación. Se permite incluir en la lista de agrupación hasta 10 columnas. Ejemplo: Queremos obtener la suma de las ventas de las oficinas agrupadas por region y ciudad: SELECT SUM(ventas) FROM oficinas GROUP BY region,ciudad Se agrupa primero por región, y dentro de cada región por ciudad Todas las filas que tienen valor nulo en el campo de agrupación, pasan a formar un único grupo. Es decir, considera el valor nulo como un valor cualquiera a efectos de agrupación. Ejemplo: SELECT oficina,SUM(ventas) AS ventas_totales FROM repventas GROUP BY oficina En el resultado aparece una fila con el campo oficina sin valor y a continuación una cantidad en el campo ventas_totales, esta cantidad corresponde a la suma de las ventas de los empleados que no tienen oficina asignada (campo oficina igual a nulo). 41, 43 La cláusula HAVING

La cláusula HAVING nos permite seleccionar filas de la tabla resultante de una consulta de resumen. Para la condición de selección se pueden utilizar los mismos tests de comparación descritos en la cláusula WHERE, también se pueden escribir condiciones compuestas (unidas por los operadores OR, AND, NOT), pero existe una restricción. En la condición de selección sólo pueden aparecer: valores constantes funciones de columna columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) o cualquier expresión basada en las anteriores. Ejemplo: Queremos saber las oficinas con un promedio de ventas de sus empleados mayor que 500.000 ptas. SELECT oficina FROM empleados GROUP BY oficina HAVING AVG(ventas) > 500000 NOTA: Para obtener lo que se pide hay que calcular el promedio de ventas de los empleados de cada oficina, por lo que hay que utilizar la tabla empleados.Tenemos que agrupar los empleados por oficina y calcular el promedio para cada oficina, por último nos queda seleccionar del resultado las filas que tengan un promedio superior a 500.000 ptas. Resumen del tema

¿Cómo se ejecuta internamente una consulta de resumen? - Primero se forma la tabla origen de datos según la cláusula FROM, - se seleccionan del origen de datos las filas según la cláusula WHERE, - se forman los grupos de filas según la cláusula GROUP BY, - por cada grupo se obtiene una fila en la tabla resultante con los valores que aparecen en las cláusulas GROUP BY, HAVING y en la lista de selección, - se seleccionan de la tabla resultante las filas según la cláusula HAVING, - se eliminan de la tabla resultante las columnas que no aparecen en la lista de selección, - se ordenan las filas de la tabla resultante según la cláusula ORDER BY Una consulta se convierte en consulta de resumen en cuanto aparece GROUP BY, HAVING o una función de columna. En una consulta de resumen, la lista de selección y la cláusula HAVING sólo pueden contener: valores constantes funciones de columna columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) o cualquier expresión basada en las anteriores. Recuerden que cuando hablamos de ptas. es hablando de pesetas, moneda que debe ser cambiada por pesos. ESTE MATERIAL FUE TOMADO DE CURSOS DE SQL PUBLICADOS ONLINE.