Cerrar

Enviar mensaje

twitter FaceBook You Tube Git Hub Docs.com Enviar email

Manual de SQL (Oracle SQL)

[8]
Agrupaciones. Consultas de totales

Publicidad

[8.1] funciones de cálculo con grupos

Ya hemos visto cómo hacer cálculos con los datos de una consulta e incluso como utilizar funciones en esos cálculos. Pero hasta ahora las funciones utilizadas solo podían usar información procedente de datos de la misma fila. Es decir, no podíamos sumar, por ejemplo, datos procedentes de distintas filas. Sin embargo, hacer cálculos con datos de diferentes filas es una necesidad muy habitual.

Las funciones de cálculo con grupos son las encargadas de realizar cálculos en vertical (usando datos de diferentes filas) en lugar de en horizontal (usando datos procedentes de la misma fila en la que vemos el resultado).

Las funciones de cálculos de totales para datos agrupados son las siguientes:

Función

Significado

COUNT(expresion)

Cuenta los elementos de un grupo. Se suele indicar un asterisco (COUNT(*))en lugar de una expresión, ya que la cuenta no varía por indicar una expresión concreta; el resultado siempre es el número de elementos del grupo.

Hay que tener en cuenta que esta función ignora los valores nulos a la hora de contar, por lo que la expresión COUNT(telefono) cuenta la cantidad de teléfonos que hay (ignorando los nulos).

SUM(expresión)

Suma los valores de la expresión

AVG(expresión)

Calcula la media aritmética sobre la expresión indicada

MIN(expresión)

Mínimo valor que toma la expresión indicada

MAX(expresión)

Máximo valor que toma la expresión indicada

STDDEV(expresión)

Calcula la desviación estándar

VARIANCE(expresión)

Calcula la varianza

Todas ellas requieren trabajar con grupos (más adelante se explica cómo agrupar filas), si no se indican grupos, las funciones trabajan sobre todos los datos de una tabla.

Por ejemplo si tenemos esta tabla:

Cod_trabajador

Nombre

Salario

1

Pedro

1200

2

Ana

1500

3

Raquel

1650

4

Sebastián

980

5

Marcos

1200

6

Mónica

1650

7

Raúl

1200

8

Mireia

980

9

Gorka

1650

10

Maia

1650

Suponiendo que son los datos de la tabla trabajadores, la consulta:

SELECT MAX(salario) FROM trabajadores;

Devuelve el valor 1650, que es el máximo salario. Mientras que la consulta:

SELECT COUNT(*) FROM trabajadores;

Devolvería 10, puesto que hay 10 trabajadores en la tabla.

[8.1.1]manejo de los valores nulos

Las funciones de cálculo agrupado ignoran los valores NULL. Si no deseamos ignorarles, deberemos utilizar funciones de nulos como COALESCE, NVL o NVL2 para manejar apropiadamente el valor nulo.

Sin embargo, la función COUNT, se suele usar con asterisco (*) como objeto de cálculo, lo que hace que cuente filas independientemente de su contenido. Si usamos una expresión, como COUNT(salario), no contaría las filas que tengan un salario nulo en ellas.

[8.1.2]uso de DISTINCT en las funciones de totales

Las funciones anteriores admiten que antepongamos el término DISTINCT antes de la expresión. De ese forma solo se tienen en cuenta los valores distintos. Por ejemplo en:

SELECT COUNT(DISTINCT salario)

El resultado es 4 porque solo hay 4 salarios distintos. Lo mismo ocurre con el resto de funciones, DISTINCT hace que se ignoren los valores repetidos.

[8.2] agrupaciones

Lo normal es utilizar las funciones anteriores, no para una tabla completa sino para grupos de filas en base a un criterio. Esta técnica es la que se conoce como agrupación de filas y provoca que, de cada grupo, se muestre una sola fila.

Para ello se utiliza la cláusula GROUP BY que permite indicar en base a qué registros se realiza la agrupación.

[8.2.1]sintaxis completa de la instrucción SELECT

Con GROUP BY la sintaxis completa instrucción SELECT queda de esta forma:

SELECT listaDeExpresiones
FROM listaDeTablas
[JOIN tablasRelacionadasYCondicionesDeRelación]
[WHERE condiciones]
[GROUP BY grupos]
[HAVING condicionesDeGrupo]
[ORDER BY listaDeExpresiones];

[8.2.2]uso de GROUP BY

En el apartado GROUP BY, se indica el nombre de las columnas (o expresiones más complejas) por las que se agrupa. La función de este apartado es crear una única fila por cada valor distinto en las columnas del grupo. Si por ejemplo agrupamos en base a las columnas tipo y modelo en una tabla de existencias, se creará un único registro por cada tipo y modelo distintos:

Si la tabla de existencias sin agrupar es:

Tipo

Modelo

N_Almacen

Cantidad

AR

6

1

2500

AR

6

2

5600

AR

6

3

2430

AR

9

1

250

AR

9

2

4000

AR

9

3

678

AR

15

1

5667

AR

20

3

43

BI

10

2

340

BI

10

3

23

BI

38

1

1100

BI

38

2

540

BI

38

3

700

Si ahora ejecutamos la siguiente instrucción:

SELECT tipo,modelo
FROM existencias
GROUP BY tipo,modelo;

Obtendremos este resultado:

Tipo

Modelo

AR

6

AR

9

AR

15

AR

20

BI

10

BI

38

Los datos se resumen. Por cada tipo y modelo distintos se creará un grupo de modo que solo aparece una fila por cada grupo. Los datos n_almacen y cantidad no están disponibles ya que varían en cada grupo. Solo se podrá mostrar los datos agrupados o datos sobre los que realicemos cálculos.

Es decir, esta consulta es errónea:

SELECT tipo,modelo, cantidad
FROM existencias
GROUP BY tipo,modelo;
SELECT tipo,modelo, cantidad
                    *
ERROR en línea 1: 
ORA-00979: no es una expresión GROUP BY

La razón es el uso de la columna cantidad, en el SELECT como no se ha agrupado por ella y no es un cálculo sobre tipo y/o modelo (las columnas por la que ese está agrupando), se produce el error.

[8.2.3]uso de funciones de totales con grupos

Lo normal es agrupar para obtener cálculos sobre cada grupo. Por ejemplo podemos modificar la consulta anterior de esta forma:

SELECT tipo,modelo, cantidad, SUM(Cantidad)
FROM existencias
GROUP BY tipo,modelo;

y se obtiene este resultado:

Tipo

Modelo

SUM(Cantidad)

AR

6

10530

AR

9

4928

AR

15

5667

AR

20

43

BI

10

363

BI

38

1740

Se suman las cantidades para cada grupo. Igualmente podríamos realizar cualquier otro tipo de cálculo (AVG, COUNT,…)

[8.3] condiciones HAVING

A veces se desea restringir el resultado de una expresión agrupada, por ejemplo podríamos tener esta idea:

SELECT tipo,modelo, cantidad, SUM(Cantidad)
FROM existencias
WHERE SUM(Cantidad)>500
GROUP BY tipo,modelo;

Lo que se espera es que solo aparezcan los grupos cuya suma de la cantidad sea menor de 500. Pero, en su lugar, Oracle devolvería este error:

WHERE SUM(Cantidad)>500
      *
ERROR en línea 3: 
ORA-00934: función de grupo no permitida aquí

La razón reside en el orden en el que se ejecutan las cláusulas de la instrucción SELECT. Oracle calcula primero el WHERE y luego los grupos (cláusula GROUP BY); por lo que no podemos usar en el WHERE la función de cálculo de grupos SUM, porque es imposible en ese momento conocer el resultado de la SUMA al no haberse establecido aún los grupos.

La solución es utilizar otra cláusula: HAVING, que se ejecuta una vez realizados los grupos. Es decir, si queremos ejecutar condiciones sobre las funciones de totales, se debe hacer en la cláusula HAVING.

La consulta anterior quedaría:

SELECT tipo,modelo, cantidad, SUM(Cantidad)
FROM existencias
GROUP BY tipo,modelo
HAVING SUM(Cantidad)>500;

Eso no implica que no se pueda usar WHERE. Ésta expresión sí es válida:

SELECT tipo,modelo, cantidad, SUM(Cantidad)
FROM existencias
WHERE tipo!=’AR’
GROUP BY tipo,modelo
HAVING SUM(Cantidad)>500;

En definitiva, el orden de ejecución de la consulta marca lo que se puede utilizar con WHERE y lo que se puede utilizar con HAVING:

[8.3.1]orden de ejecución de ínstrucción SELECT

Para evitar problemas conviene conocer la forma de trabajar de una instrucción SELECT. Estos son los pasos en la ejecución de una instrucción de agrupación por parte del gestor de bases de datos:

[1]Seleccionar las filas deseadas utilizando WHERE. Esta cláusula eliminará columnas en base a la condición indicada

[2]Se establecen los grupos indicados en la cláusula GROUP BY

[3]Se calculan los valores de las funciones de totales (COUNT, SUM, AVG,...)

[4]Se filtran los registros que cumplen la cláusula HAVING

[5]El resultado se ordena en base al apartado ORDER BY.

[8.4] funciones de agrupación anidadas

Es posible obtener resultados muy interesantes gracias a la posibilidad de anidar funciones de totales. Así podemos ejecutar esta instrucción:

SELECT AVG(SUM(Cantidad))
FROM existencias
GROUP BY tipo,modelo;

Para ello hay que tener en cuenta que la consulta funciona así:

[1]Se calcula la suma de la cantidad para cada grupo, el resultado es una cantidad por cada tipo y modelo distintos en la tabla

[2]A partir de esa resultado se calcula la media de las cantidades obtenidas en el punto anterior.

Por lo tanto el resultado es la media de la suma de cantidades de cada grupo.