Sean bienvenidos a esta sección donde se revisa de como realizar Informes de Datos Agregados con Funciones de Grupo, recuerden estas sentencias estan siendo ejecutadas en SQL developer administrador de la Base de datos Oracle.
Objetivos de la publicación:
Al finalizar esta lección, debería estar capacitado para lo siguiente:
- Identificar las funciones de grupo disponibles
- Describir los usos de las funciones de grupo
- Agrupar datos con la cláusula GROUP BY
- Incluir o excluir filas agrupadas con la cláusula HAVING
Funciones de grupo
Tipos y sintaxis
- Uso de AVG, SUM, MIN, MAX, COUNT
- Uso de la palabra clave DISTINCT en funciones de grupo
- Valores NULL en una función de grupo
¿Qué Son las Funciones de Grupo?
Las funciones de grupo funcionan en juegos de filas para proporcionar un resultado por grupo.
A diferencia de las funciones de una sola fila, las funciones de grupo funcionan en juegos de filas para proporcionar un resultado por grupo. Estos juegos pueden formar la tabla completa o la tabla dividida en grupos.
Tipos de Funciones de Grupo
AVG
MAX
MIN
STDDEV
SUM
VARIANCE
Cada una de las funciones acepta un argumento. La siguiente tabla identifica las opciones que se pueden utilizar en la sintaxis:
Funciones de Grupo: Sintaxis
La función de grupo se coloca después de la palabra clave SELECT. Puede que tenga varias funciones de grupo separadas por comas.
Instrucciones para utilizar las funciones de grupo:
DISTINCT hace que la función considere sólo los valores no duplicados; ALL hace que considere cada valor, incluyendo los duplicados. El valor por defecto es ALL y, por lo tanto, no es necesario especificarlo.
Los tipos de dato para las funciones con el argumento expr pueden ser CHAR, VARCHAR2, NUMBER o DATE.
Todas las funciones de grupo ignoran los valores nulos. Para sustituir un valor para valores nulos, utilice las funciones NVL, NVL2, COALESCE, CASE o DECODE.
Uso de las Funciones AVG y SUM
Puede utilizar AVG y SUM para datos numéricos.
Puede utilizar las funciones AVG, SUM, MIN y MAX en las columnas que pueden almacenar datos numéricos. El ejemplo de la diapositiva muestra la media, el valor más alto, más bajo y la suma de los salarios mensuales de todos los vendedores.
Puede utilizar las funciones MAX y MIN para tipos de dato numéricos, de caracteres y de fecha. El ejemplo muestra los empleados más y menos experimentados.
El siguiente ejemplo muestra el apellido del empleado que está en primer lugar y del que está en último lugar en una lista de todos los empleados ordenada alfabéticamente:
SELECT MIN(last_name), MAX(last_name)
FROM hr.employees;
Nota: las funciones AVG, SUM, VARIANCE y STDDEV se pueden utilizar sólo con los tipos de dato numéricos. MAX and MIN no se pueden utilizar con tipos de dato LOB o LONG.
Uso de la Función COUNT
COUNT(*) devuelve el número de filas en una tabla:
COUNT(expr) devuelve el número de filas con valores no nulos para la expresión expr:
La función COUNT tiene tres formatos:
- COUNT(*)
- COUNT(expr)
- COUNT(DISTINCT expr)
COUNT(*) devuelve el número de filas en una tabla que cumplan con el criterio de la sentencia SELECT, incluyendo las filas duplicadas y las filas que contengan valores nulos en cualquiera de las columnas. Si se incluye una cláusulaWHERE en la sentencia SELECT, COUNT(*) devuelve el número de filas que cumpla con la condición de la cláusula WHERE.
Por el contrario, COUNT(expr) devuelve el número de valores no nulos que están en la columna identificada con expr.
COUNT(DISTINCT expr) devuelve el número de valores únicos no nulos que están en la columna identificada con expr.
Ejemplos:
1. El ejemplo muestra el número de empleados del departamento 50.
2. También muestra el número de empleados del departamento 80 que pueden percibir una comisión.
Uso de la Palabra Clave DISTINCT
COUNT(DISTINCT expr) devuelve el número con valores distintos no nulos de expr.
Para mostrar el número de valores distintos de departamento en la tabla EMPLOYEES:
Utilice la palabra clave DISTINCT para suprimir el recuento de cualquier valor duplicado en una columna.
El ejemplo muestra el número de valores de departamento distintos que están en la tabla EMPLOYEES.
Funciones de Grupo y Valores Nulos
Las funciones de grupo ignoran los valores nulos de la columna:
La función NVL fuerza las funciones de grupo para que incluyan valores nulos.
Todas las funciones de grupo ignoran los valores nulos de la columna.
Sin embargo, NVL fuerza las funciones de grupo para que incluyan valores nulos.
Ejemplos:
1. La media se calcula únicamente en base a las filas de la tabla en las que se almacena un valor válido en la columna COMMISSION_PCT. La media se calcula con la comisión total pagada a todos los empleados dividida entre el número de empleados que perciben una comisión (cuatro).
2. La media se calcula en base a todas las filas de la tabla, independientemente de si los valores nulos se almacenan en la columna COMMISSION_PCT. La media se calcula con la comisión total pagada a todos los empleados dividida entre el número de empleados de la compañía (20).
Agrupar filas
- Cláusula GROUP BY
- Cláusula HAVING
Creación de Grupos de Datos
Hasta este punto, todas las funciones de grupo han considerado la tabla como un grupo de información de gran tamaño. Sin embargo, en ocasiones es necesario dividir la tabla de información en grupos más pequeños. Para ello, hay que utilizar la cláusula GROUP BY.
Creación de Grupos de Datos: Sintaxis de la Cláusula GROUP BY
Puede dividir las filas de una tabla en grupos más pequeños utilizando la cláusula GROUP BY.
Puede utilizar la cláusula GROUP BY para dividir las filas de la tabla en grupos. A continuación puede utilizar las funciones de grupo para devolver información de resumen de cada grupo.
En la sintaxis:
group_by_expression Especifica columnas cuyos valores determinan la base para agrupar filas
Instrucciones
Si incluye una función de grupo en una cláusula SELECT, no puede seleccionar también resultados individuales a menos que la columna individual aparezca en la cláusula GROUP BY. Recibirá un mensaje de error si no puede incluir la lista de columnas en la cláusula GROUP BY.
Al utilizar la cláusula WHERE, puede excluir las filas antes de dividirlas en grupos.
Debe incluir las columnas en la cláusula GROUP BY.
No puede utilizar un alias de columna en la cláusula GROUP BY.
Uso de la Cláusula GROUP BY
Todas las columnas de la lista SELECT que no están incluidas en las funciones de grupo deben estar en la cláusula GROUP BY.
Al utilizar la cláusula GROUP BY, asegúrese de que todas las columnas de la lista SELECT que no están en las funciones de grupo están incluidas en la cláusula GROUP BY. El ejemplo muestra el número de departamento y el salario medio de cada departamento. A continuación se muestra cómo se evalúa esta sentencia SELECT que contiene una cláusula GROUP BY:
La cláusula SELECT especifica las columnas que se van a recuperar de la siguiente forma:
Columna del número de departamento de la tabla EMPLOYEES
Media de todos los salarios del grupo especificada en la cláusula GROUP BY
La cláusula FROM especifica las dos tablas a las que la base de datos debe acceder: tabla EMPLOYEES.
La cláusula WHERE especifica las filas que se van a recuperar. Ya que no existe ninguna cláusula WHERE, por defecto se recuperarán todas las filas.
La cláusula GROUP BY especifica cómo se deben agrupar las filas. Las filas se agrupan por número de departamento, por lo tanto, la función AVG aplicada a la columna de salario calcula el salario medio de cada departamento.
Nota: para ordenar los resultados de las consultas en orden ascendente o descendente, incluya la cláusula ORDER BY en la consulta.
Uso de la Cláusula GROUP BY
No es necesario que la columna GROUP BY esté en la lista SELECT.
No es necesario que la columna GROUP BY esté en la cláusula SELECT. Por ejemplo, la sentencia SELECT muestra los salarios medios de cada departamento sin mostrar los respectivos números de departamento. Sin embargo, sin los números de departamento, los resultados no parecen significativos.
También puede utilizar la función de grupo en la cláusula ORDER BY:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);
Agrupación de Más de Una Columna
En ocasiones necesitará ver los resultados de grupos dentro de grupos. La imagen muestra un informe que muestra el salario total pagado a cada puesto de cada departamento.
Según ese agrupamiento, la tabla EMPLOYEES se agrupa en primer lugar por número de departamento y, a continuación, por puesto. Por ejemplo, los cuatro oficinistas en el departamento de stock del departamento 50 se agrupan conjuntamente y se produce un resultado único (salario total) para todos los oficinistas en el departamento de stock del grupo.
La siguiente sentencia SELECT devuelve el resultado mostrado:
SELECT department_id, job_id, sum(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY job_id;
Uso de la Cláusula GROUP BY en Varias Columnas
Puede devolver resultados de resumen para grupos y subgrupos mostrando varias columnas GROUP BY. La cláusula GROUP BY agrupa filas pero no garantiza el orden del juego de resultados. Para ordenar los agrupamientos, utilice la cláusula ORDER BY.
En el ejemplo, la sentencia SELECT que contiene una cláusula GROUP BY se evalúa de la siguiente forma:
La cláusula SELECT especifica la columna que se van a recuperar:
ID de departamento en la tabla EMPLOYEES
ID de cargo de la tabla EMPLOYEES
Suma de todos los salarios del grupo especificada en la cláusula GROUP BY
La cláusula FROM especifica las dos tablas a las que la base de datos debe acceder: tabla EMPLOYEES.
La cláusula WHERE reduce el juego de resultados a aquellas filas en las que el ID de departamento es mayor de 40.
La cláusula GROUP BY especifica cómo debe agrupar las filas resultantes:
En primer lugar, las filas se agrupan por ID de departamento.
En segundo lugar, las filas se agrupan por ID de cargo en los grupos de ID de departamento.
La cláusula ORDER BY ordena los resultados por ID de departamento.
Nota: la función SUM se aplica a la columna de salario de todos los ID de cargo en el juego de resultados de cada grupo de ID de departamento. Además, tenga en cuenta que la fila SA_REP no se devuelve. El ID de departamento para esta fila es NULL y, por lo tanto, no cumple la condición WHERE.
Consultas No Válidas Realizadas con las Funciones de Grupo
Cualquier columna o expresión de la lista SELECT que no sea una función de agregación debe estar en la cláusula GROUP BY:
Cuando utilice una mezcla de elementos individual (DEPARTMENT_ID) y funciones de grupo (COUNT) en la misma sentencia SELECT debe incluir una cláusula GROUP BY que especifique los elementos individuales (en este caso, DEPARTMENT_ID). Si falta la cláusula GROUP BY, aparecerá el mensaje de error “not a single-group group function” y la columna incorrecta estará indicada con un asterisco (*). Puede corregir el error del primer ejemplo agregando la cláusula GROUP BY:
SELECT department_id, count(last_name)
FROM employees
GROUP BY department_id ;
Cualquier columna o expresión de la lista SELECT que no sea una función de agregación debe estar en la cláusula GROUP BY. En el segundo ejemplo , job_id no está en la cláusula GROUP BY ni la utiliza una función de grupo, por lo que se produce un error “not a GROUP BY expression”. Puede corregir el error del segundo ejemplo agregando job_id en la cláusula GROUP BY.
SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id, job_id;
Consultas No Válidas Realizadas con las Funciones de Grupo
No puede utilizar la cláusula WHERE para restringir grupos.
Debe utilizar la cláusula HAVING para restringir grupos.
No puede utilizar las funciones de grupo de la cláusula WHERE.
No se puede utilizar la cláusula WHERE para restringir grupos. La sentencia SELECT del ejemplo muestra un error porque se está utilizando la cláusula WHERE para restringir la visualización de los salarios medios de los departamentos que tienen un salario medio superior a 8.000 dólares.
Puede corregir el error del ejemplo utilizando la cláusula HAVING para restringir grupos:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;
Restricción de Resultados de Grupo
Utilice la cláusula HAVING para restringir grupos de la misma forma que utiliza la cláusula WHERE para restringir las filas seleccionadas. Para buscar el salario máximo de cada uno de los departamentos que tienen un salario máximo superior a 10.000 dólares, necesitará realizar las siguientes acciones:
1. Buscar el salario medio de cada departamento realizando una agrupación por número de departamento.
2. Restringir los grupos a los departamentos con un salario máximo superior a 10.000 dólares.
Restricción de Resultados de Grupo con la Cláusula HAVING
Al utilizar la cláusula HAVING, el servidor de Oracle restringe los grupos de la siguiente forma:
1. Agrupa las filas.
2. Aplica la función de grupo.
3. Muestra los grupos que coinciden con la cláusula HAVING.
Puede utilizar la cláusula HAVING para especificar los grupos que se van a mostrar y, por lo tanto, restringir los grupos según la información de agregación.
En la sintaxis, group_condition restringe los grupos de filas de aquellos grupos para los que la condición especificada es verdadera.
El servidor de Oracle realiza los siguientes pasos al utilizar la cláusula HAVING:
1. Agrupa las filas.
2. Aplica la función de grupo al grupo.
3. Muestra los grupos que coinciden con los criterios de la cláusula HAVING.
La cláusula HAVING puede preceder a la cláusula GROUP BY, pero se recomienda que coloque la cláusula GROUP BY primero porque es más lógico. Los grupos están formados y las funciones de grupo se calculan antes de aplicar la cláusula HAVING a los grupos de la lista SELECT.
Nota: la cláusula WHERE restringe filas, mientras que la cláusula HAVING restringe grupos.
Uso de la Cláusula HAVING
El ejemplo muestra los número de departamento y los salarios máximos de los departamentos cuyo salario máximo sea superior a 10.000 dólares.
Puede utilizar la cláusula GROUP BY sin utilizar una función de grupo en la lista SELECT. Si restringe las filas según el resultado de una función de grupo, debe tener una cláusula GROUP BY y una cláusula HAVING.
El siguiente ejemplo muestra los números de departamento y los salarios medios de los departamentos cuyo salario máximo sea superior a 10.000 dólares:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING max(salary)>10000;
El ejemplo muestra el ID de cargo y el salario mensual total de cada cargo que tiene una nómina total excedente de 13.000 dólares. El ejemplo excluye a los vendedores y ordena la lista por el salario mensual total.
Anidamiento de funciones de grupo
Mostrar el salario máximo medio:
Las funciones de grupo se pueden anidar en una profundidad de dos. El ejemplo de la diapositiva calcula el salario medio para cada department_id y, a continuación, muestra el salario máximo medio.
Tenga en cuenta que la cláusula GROUP BY es obligatoria al anidar funciones de grupo.
Resumen
En esta lección, debe haber aprendido lo siguiente:
- Utilizar funciones de grupo COUNT, MAX, MIN, SUM y AVG
- Escribir consultas que utilicen la cláusula GROUP BY
- Escribir consultas que utilicen la cláusula HAVING
Existen diferentes funciones de grupo disponibles en SQL, como AVG, COUNT, MAX, MIN, SUM, STDDEV y VARIANCE.
Puede crear subgrupos utilizando la cláusula GROUP BY. Además, los grupos se pueden restringir utilizando la cláusula HAVING.
Coloque las cláusulas HAVING y GROUP BY después de la cláusula WHERE en una sentencia. El orden de las cláusulas GROUP BY y HAVING después de la cláusula WHERE no es importante. Coloque la cláusula ORDER BY al final.
El servidor de Oracle evalúa las cláusulas en el siguiente orden:
1. Si la sentencia contiene una cláusula WHERE, el servidor establece las filas candidatas.
2. El servidor identifica los grupos que están especificados en la cláusula GROUP BY.
3. La cláusula HAVING restringe aún más los grupos de resultados que no cumplen con los criterios de grupo en la cláusula HAVING.
Nota: para obtener una lista completa de las funciones de grupo, consulte Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).
