SQL - Tema 4 - Información sobre Datos Agregados mediante las Funciones de Grupo by Aula cero

Informaci贸n sobre Datos Agregados mediante las Funciones de Grupo

Objetivos Al finalizar esta lección, debería estar capacitado para: Identificar las funciones de grupo disponibles Describir el uso de las funciones de grupo Agrupar datos mediante la cláusula GROUP BY

Incluir o excluir filas agrupadas utilizando la cláusula HAVING

Objetivos Esta lección trata más exhaustivamente las funciones. Se centra en la obtención de información de resumen (como, por ejemplo, medias) para grupos de filas. Analiza cómo agrupar las filas de una tabla en juegos más pequeños y cómo especificar criterios de búsqueda para grupos de filas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-2

¿Qué son las Funciones de Grupo? Las funciones de grupo operan en juegos de filas para dar un resultado por grupo. EMPLOYEES

Salario máximo en la tabla EMPLOYEES

Funciones de Grupo A diferencia de las funciones de una sola fila, las funciones de grupo operan en juegos de filas para dar un resultado por grupo. Estos juegos pueden abarcar toda la tabla o la tabla dividida en grupos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-3

Tipos de Funciones de Grupo

AVG COUNT MAX MIN STDDEV SUM VARIANCE

Funciones de grupo

Tipos de Funciones de Grupo Cada una de las funciones acepta un argumento. La tabla siguiente identifica las opciones que puede utilizar en la sintaxis: Función AVG([DISTINCT|ALL]n)

Descripción Valor medio de n; se ignoran los valores nulos

COUNT({*|[DISTINCT|ALL]expr}) Número de filas, en las que expr se evalúa como algo no nulo (cuenta todas las filas seleccionadas que utilizan *, incluidas las duplicadas y las que contienen valores nulos) MAX([DISTINCT|ALL]expr) Valor máximo de expr; se ignoran los valores nulos MIN([DISTINCT|ALL]expr)

Valor mínimo de expr; se ignoran los valores nulos

STDDEV([DISTINCT|ALL]x)

Desviación estándar de n; se ignoran los valores nulos

SUM([DISTINCT|ALL]n)

Suma los valores de n; se ignoran los valores nulos

VARIANCE([DISTINCT|ALL]x)

Varianza de n; se ignoran los valores nulos

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-4

Funciones de Grupo: Sintaxis

SELECT FROM [WHERE [GROUP BY [ORDER BY

[column,] group_function(column), ... table condition] column] column];

Instrucciones para Utilizar Funciones de Grupo DISTINCT hace que la función considere únicamente valores no duplicados; ALL hace que considere todos los valores, incluidos los duplicados. El valor por defecto es ALL y, por tanto, no es necesario especificarlo. Los tipos de datos para las funciones con un argumento expr pueden ser CHAR, VARCHAR2, NUMBER o DATE. Todas las funciones de grupo ignoran los valores nulos. Para sustituir con un valor los valores nulos, utilice las funciones NVL, NVL2 o COALESCE.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-5

Uso de las Funciones AVG y SUM Puede utilizar AVG y SUM para datos numéricos. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

Uso de las Funciones de Grupo Puede utilizar las funciones AVG, SUM, MIN y MAX en columnas que puedan almacenar datos numéricos. El ejemplo de la diapositiva muestra la media, el más alto, el más bajo y la suma de los salarios mensuales de todos los representantes de ventas.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-6

Uso de las Funciones MIN y MAX Puede utilizar MIN y MAX para tipos de datos numéricos, de carácter y de fecha. SELECT MIN(hire_date), MAX(hire_date) FROM employees;

Uso de las Funciones de Grupo (continuación) Puede utilizar las funciones MAX y MIN para tipos de datos numéricos, de carácter y de fecha. El ejemplo de la diapositiva muestra al empleado más nuevo y al más antiguo. El ejemplo siguiente muestra el apellido del empleado primero y el del último de una lista alfabética de todos los empleados. SELECT MIN(last_name), MAX(last_name) FROM employees;

Nota: Las funciones AVG, SUM, VARIANCE y STDDEV se pueden utilizar sólo con tipos de datos numéricos. MAX y MIN no se pueden utilizar con tipos de datos LOB o LONG.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-7

Uso de la Función COUNT COUNT(*) devuelve el número de filas en una tabla:

SELECT COUNT(*) FROM employees WHERE department_id = 50;

COUNT(expr) devuelve el número de filas con valores no nulos para expr:

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

Función COUNT La función COUNT tiene tres formatos: COUNT(*) COUNT(expr) COUNT(DISTINCT expr) COUNT(*) devuelve el número de filas de una tabla que satisfacen el criterio de la sentencia SELECT, incluidas las filas duplicadas y las que contienen valores nulos de cualquiera de las columnas. Si se incluye una cláusula WHERE en la sentencia SELECT, COUNT(*) devuelve el número de filas que satisfacen la condición de la cláusula WHERE. Por el contrario, COUNT(expr) devuelve el número de valores no nulos que hay en la columna identificada con expr. COUNT(DISTINCT expr) devuelve el número de valores únicos no nulos que hay en la columna identificada con expr. Ejemplos 1. El ejemplo de la diapositiva muestra el número de empleados del departamento 50. 2. Este ejemplo muestra el número de empleados del departamento 80 que ganan comisión.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-8

Uso de la Palabra Clave DISTINCT

COUNT(DISTINCT expr) devuelve el número de valores no nulos distintos de expr. Para mostrar el número de valores de departamento distintos en la tabla EMPLOYEES:

SELECT COUNT(DISTINCT department_id) FROM employees;

Palabra Clave DISTINCT Utilice la palabra clave DISTINCT para suprimir el recuento de valores duplicados en una columna. El ejemplo de la diapositiva muestra el número de valores de departamento distintos que hay en la tabla EMPLOYEES.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-9

Funciones de Grupo y Valores Nulos Las funciones de grupo ignoran los valores nulos en la columna:

SELECT AVG(commission_pct) FROM employees;

La función NVL fuerza a las funciones de grupo a incluir valores nulos:

SELECT AVG(NVL(commission_pct, 0)) FROM employees;

Funciones de Grupo y Valores Nulos Todas las funciones de grupo ignoran los valores nulos de la columna. La función NVL fuerza a las funciones de grupo a incluir valores nulos. Ejemplos 1. La media se calcula basándose sólo en las filas de la tabla en la que se almacena un valor válido en la columna COMMISSION_PCT. La media se calcula como comisión total que se paga a todos los empleados dividida por el número de empleados que reciben una comisión (cuatro). 2. La media se calcula basándose en todas las filas de la tabla, independientemente de si se almacenan valores válidos en la columna COMMISSION_PCT. La media se calcula como comisión total que se paga a todos los empleados dividida por el número total de empleados de la compañía (20).

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-10

Creación de Grupos de Datos

EMPLOYEES 4400 9500

3500

6400

Salario medio de la tabla EMPLOYEES para cada departamento

10033

Creación de Grupos de Datos Hasta el momento, todas las funciones de grupo han tratado la tabla como un grupo de información de gran tamaño. Hay ocasiones, sin embargo, en que debe dividir la tabla de información en grupos más pequeños. Esto se puede conseguir mediante la cláusula GROUP BY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-11

Creación de Grupos de Datos: Sintaxis de la Cláusula GROUP BY SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

Puede dividir las filas de una tabla en grupos más pequeños mediante la cláusula GROUP BY.

Cláusula GROUP BY Puede utilizar la cláusula GROUP BY para dividir las filas de una tabla en grupos. Puede utilizar entonces las funciones de grupo para devolver información de resumen para 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 además resultados individuales, a menos que la columna individual aparezca en la cláusula GROUP BY. Recibirá un mensaje de error si no incluye la lista de columnas en la cláusula GROUP BY. Mediante una cláusula WHERE, puede excluir 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.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-12

Uso de la Cláusula GROUP BY Todas las columnas de la lista SELECT que no sean funciones de grupo se deben incluir en la cláusula GROUP BY. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

Uso de la Cláusula GROUP BY Cuando utilice la cláusula GROUP BY, asegúrese de que todas las columnas de la lista SELECT que no sean funciones de grupo se incluyan en la cláusula GROUP BY. El ejemplo de la diapositiva muestra el número de departamento y el salario medio de cada departamento. La sentencia SELECT, que contiene una cláusula GROUP BY, se evalúa así: La cláusula SELECT especifica las columnas que se recuperarán, de este modo: - Columna de números de departamento de la tabla EMPLOYEES - Media de todos los salarios del grupo que especificó en la cláusula GROUP BY La cláusula FROM especifica las tablas a las que debe acceder la base de datos: la tabla EMPLOYEES. La cláusula WHERE especifica las filas que se recuperarán. Como no hay cláusula WHERE, se recuperarán todas las filas por defecto. La cláusula GROUP BY especifica cómo se deben agrupar las filas. Las filas se agrupan por número de departamento, por lo que la función AVG que se aplica a la columna de salarios calcula el salario medio de cada departamento.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-13

Uso de la Cláusula GROUP BY La columna GROUP BY no tiene que estar en la lista SELECT. SELECT AVG(salary) FROM employees GROUP BY department_id ;

Uso de la Cláusula GROUP BY (continuación) La columna GROUP BY no tiene que estar en la cláusula SELECT. Por ejemplo, la sentencia SELECT de la diapositiva muestra los salarios medios de cada departamento sin mostrar los números respectivos de departamento. Sin embargo, sin los números de departamento, los resultados no parecen significativos. Puede utilizar la función de grupo en la cláusula ORDER BY. SELECT FROM GROUP BY ORDER BY

department_id, AVG(salary) employees department_id AVG(salary);

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-14

Agrupación por Más de Una Columna EMPLOYEES

Sume los salarios de la tabla EMPLOYEES para cada puesto, agrupados por departamento

Grupos Dentro de Grupos En ocasiones, necesita ver los resultados para grupos dentro de grupos. La diapositiva muestra un informe que detalla el salario total que se paga a cada cargo en cada departamento. La tabla EMPLOYEES se agrupa primero por número de departamento y después por cargo dentro de esa agrupación. Por ejemplo, los cuatro administrativos del departamento 50 se agrupan juntos y se crea un único resultado (salario total) para todos los administrativos del grupo.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-15

Uso de la Cláusula GROUP BY en Varias Columnas SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;

Grupos Dentro de Grupos (continuación) Puede devolver un resumen de resultados para grupos y subgrupos mostrando más de una columna GROUP BY. Puede ordenar por defecto de los resultados según el orden de las columnas en la cláusula GROUP BY. En el ejemplo de la diapositiva, la sentencia SELECT que contiene una cláusula GROUP BY se evalúa así: La cláusula SELECT especifica la columna que se recuperará: - Número de departamento de la tabla EMPLOYEES - Identificador de trabajo de la tabla EMPLOYEES - Suma de todos los salarios del grupo que especificó en la cláusula GROUP BY La cláusula FROM especifica las tablas a las que debe acceder la base de datos: la tabla EMPLOYEES. La cláusula GROUP BY especifica cómo se deben agrupar las filas: - En primer lugar, las filas se agrupan por número de departamento. - En segundo lugar, las filas se agrupan por identificador de trabajo de los grupos de número de departamento. Así pues, la función SUM se aplica a la columna de salarios para todos los identificadores de trabajo de cada grupo de números de departamento.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-16

Consultas Ilegales que Utilizan Funciones de Grupo Cualquier columna o expresión de la lista SELECT que no sea una función de agregación se debe incluir en la cláusula GROUP BY: SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function

Falta una columna en la cláusula GROUP BY

Consultas Ilegales que Utilizan Funciones de Grupo Siempre que utilice una mezcla de elementos individuales (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, aparece el mensaje de error not a single-group group function y un asterisco (*) indica la columna con el error. Puede corregir el error de la diapositiva 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 se debe incluir en la cláusula GROUP BY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-17

Consultas Ilegales que Utilizan Funciones de Grupo

No puede utilizar la cláusula WHERE para restringir grupos. Utilice la cláusula HAVING para restringir grupos. No puede utilizar funciones de grupo en la cláusula WHERE.

SELECT FROM WHERE GROUP BY

department_id, AVG(salary) employees AVG(salary) > 8000 department_id ;

WHERE

AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here

No se puede utilizar la cláusula WHERE para restringir grupos

Consultas Ilegales que Utilizan Funciones de Grupo (continuación) La cláusula WHERE no se puede utilizar para restringir grupos. La sentencia SELECT de la diapositiva da como resultado un error porque utiliza la cláusula WHERE para restringir la visualización de salarios medios de los departamentos que tienen un salario medio superior a 8.000 dólares. Puede corregir el error del ejemplo si utiliza la cláusula HAVING para restringir grupos. SELECT FROM HAVING GROUP BY

department_id, AVG(salary) employees AVG(salary) > 8000 department_id ;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-18

Restricción de Resultados de Grupos EMPLOYEES

Salario máximo por departamento cuando es superior a 10.000 dólares

Restricción de Resultados de Grupos De la misma forma que utiliza la cláusula WHERE para restringir las filas que se seleccionarán, utilice la cláusula HAVING para restringir grupos. Para buscar el salario máximo de cada departamento que tenga un salario máximo superior a 10.000 dólares, necesita: 1. Buscar el salario medio de cada departamento agrupando por número de departamento. 2. Restringir los grupos a los departamentos con un salario máximo superior a 10.000 dólares.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-19

Restricción de Resultados de Grupos con la Cláusula HAVING Si se utiliza la cláusula HAVING, Oracle Server restringe los grupos así: 1. Se agrupan las filas. 2. Se aplica la función de grupo. 3. Se muestran los grupos que satisfacen la cláusula HAVING. SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

Restricción de Resultados de Grupos con la Cláusula HAVING Utilice la cláusula HAVING para especificar los grupos que se deben mostrar, con los que se restringen más los grupos basándose en la información de agregación. En la sintaxis, group_condition restringe los grupos de filas devueltas a los grupos para los que es verdadera la condición especificada. Oracle Server sigue estos pasos si se utiliza la cláusula HAVING: 1. Se agrupan las filas. 2. Se aplica al grupo la función de grupo. 3. Se muestran los grupos que satisfacen los criterios de la cláusula HAVING. La cláusula HAVING puede ir delante de la cláusula GROUP BY, pero se recomienda poner primero la cláusula GROUP BY, porque resulta más lógico. Se forman los grupos y se calculan las funciones de grupo antes de que se aplique la cláusula HAVING a los grupos de la lista SELECT.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-20

Uso de la Cláusula HAVING

SELECT FROM GROUP BY HAVING

department_id, MAX(salary) employees department_id MAX(salary)>10000 ;

Uso de la Cláusula HAVING El ejemplo de la diapositiva muestra números de departamento y salarios máximos de los departamentos con un salario máximo superior a 10.000 dólares. Puede utilizar la cláusula GROUP BY sin utilizar la función de grupo de la lista SELECT. Si restringe filas basándose en el resultado de una función de grupo, debe tener una cláusula GROUP BY además de la cláusula HAVING. El ejemplo siguiente muestra los números de departamento y salarios medios de los departamentos con un salario máximo superior a 10.000 dólares.: SELECT FROM GROUP BY HAVING

department_id, AVG(salary) employees department_id max(salary)>10000;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-21

Uso de la Cláusula HAVING

SELECT FROM WHERE GROUP BY HAVING ORDER BY

job_id, SUM(salary) PAYROLL employees job_id NOT LIKE '%REP%' job_id SUM(salary) > 13000 SUM(salary);

Uso de la Cláusula HAVING (continuación) El ejemplo de la diapositiva muestra el identificador de trabajo y el salario mensual total de cada trabajo que tiene una nómina total superior a 13.000 dólares. El ejemplo excluye a los representantes de venta y ordena la lista por salario mensual total.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-22

Anidamiento de Funciones de Grupo Muestre al salario medio máximo: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id ;

Anidamiento de Funciones de Grupo Las funciones de grupo se pueden anidar hasta una profundidad de dos. El ejemplo de la diapositiva muestra el salario medio máximo.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-23

Resumen En esta lección ha aprendido a: Utilizar las funciones de grupo COUNT, MAX, MIN y AVG Escribir consultas que utilicen la cláusula GROUP BY Escribir consultas que utilicen la cláusula HAVING SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

column, group_function table condition] group_by_expression] group_condition] column];

Resumen Hay varias funciones de grupo disponibles en SQL, como éstas: AVG, COUNT, MAX, MIN, SUM, STDDEV y VARIANCE Puede crear subgrupos mediante la cláusula GROUP BY. Los grupos se pueden restringir mediante la cláusula HAVING. Ponga las cláusulas HAVING y GROUP BY después de la cláusula WHERE en una sentencia. El orden de las cláusulas HAVING y GROUP después de la cláusula WHERE no es importante. Ponga la cláusula ORDER BY en último lugar. Oracle Server evalúa las cláusulas por este orden: 1. Si la sentencia contiene una cláusula WHERE, Oracle Server establece las filas candidatas. 2. Oracle Server identifica los grupos especificados en la cláusula GROUP BY. 3. La cláusula HAVING restringe más los grupos resultantes que no cumplen los criterios de grupo de la cláusula HAVING. Nota: Para obtener una lista completa de las funciones de grupo, consulte Oracle SQL Reference.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-24

Práctica 4: Visión General Esta práctica cubre los temas siguientes: Escritura de consultas que utilicen las funciones de grupo Agrupación por filas para obtener más de un resultado Restricción de grupos mediante la cláusula HAVING

Práctica 4: Visión General Al finalizar esta práctica, debería estar familiarizado con el uso de funciones de grupo y la selección de grupos de datos.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-25

Práctica 4 Determine la validez de las tres siguientes afirmaciones. Seleccione Verdadero o Falso. 1. Las funciones de grupo trabajan a través de muchas filas para generar un resultado. Verdadero/Falso 2. Las funciones de grupo incluyen valores nulos en los cálculos. Verdadero/Falso 3. La cláusula WHERE restringe las filas antes de incluirlas en un cálculo de grupo. Verdadero/Falso El departamento de recursos humanos necesita estos informes: 4. Busque el salario más alto, el más bajo, la suma y el salario medio de todos los empleados. Etiquete las columnas como Maximum, Minimum, Sum y Average, respectivamente. Redondee los resultados al siguiente número entero. Guarde la sentencia SQL en un archivo de texto denominado lab_04_04.sql.

5. Modifique la consulta en lab_04_04.sql para mostrar el salario mínimo, el máximo, la suma y el salario medio para cada tipo de trabajo. Vuelva a guardar lab_04_04.sql como lab_04_05.sql. Ejecute la sentencia en lab_04_05.sql.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-26

Práctica 4 (continuación) 6. Escriba una consulta para mostrar el número de personas con el mismo puesto.

Generalice la consulta para que se le pida un cargo al usuario del departamento de recursos humanos. Guarde el archivo de comandos en un archivo denominado lab_04_06.sql. 7. Determine el número de supervisores sin listarlos. Etiquete la columna como Number of Managers. Indicación: Utilice la columna MANAGER_ID para determinar el número de supervisores.

8. Busque la diferencia entre el salario más alto y el más bajo. Etiquete la columna como DIFFERENCE.

Si le queda tiempo, realice los siguientes ejercicios: 9. Cree un informe para mostrar el número de supervisor y el salario del empleado peor pagado de ese supervisor. Excluya a los empleados cuyo supervisor no se conozca. Excluya los grupos en los que el salario mínimo sea 6.000 dólares o menos. Clasifique la salida en orden descendente de salario.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-27

Práctica 4 (continuación) Si desea afrontar un desafío mayor, realice estos ejercicios: 10. Cree una consulta que muestre el número total de empleados y, de dicho total, el número de empleados contratados en 1995, 1996, 1997 y 1998. Cree las cabeceras de columna adecuadas.

11. Cree una consulta de matriz para mostrar el trabajo, el salario de dicho puesto basado en el número de departamento y el salario total de ese puesto de los departamentos 20, 50, 80 y 90, y proporcione una cabecera apropiada a cada columna.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I 4-28