Usar funciones de grupo

Informes de datos agregados mediante las funciones de grupo

SQL tiene numerosas funciones agregadas predefinidas que se pueden usar para escribir consultas para producir exactamente este tipo de información.La cláusula GROUP BY especifica cómo agrupar filas de una tabla de datos al agregar información, mientras que la cláusula HAVING filtra las filas que no pertenecen a grupos especificados.

Las funciones de agregación realizan una variedad de acciones, como contar todas las filas de una tabla, promediar los datos de una columna y sumar datos numéricos. Los agregados también pueden buscar en una tabla para encontrar los valores "MAX" más altos o "MIN" más bajos en una columna. Al igual que con otros tipos de consultas, puede restringir o filtrar las filas en las que actúan estas funciones con la cláusula WHERE. Por ejemplo, si un gerente necesita saber cuántos empleados trabajan en una organización, la función agregada denominada COUNT (*) se puede utilizar para producir esta información. La función COUNT (*) que se muestra en la declaración SELECT a continuación cuenta todas las filas en un mesa.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

La tabla de resultados para la función COUNT (*) es una sola columna de una sola fila conocida como resultado o valor escalar. Observe que la tabla de resultados tiene un encabezado de columna que corresponde al nombre de la función agregada especificada en la cláusula SELECT.

Algunas de las funciones agregadas comúnmente utilizadas son las siguientes:

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

Las palabras clave ALL y DISTINCT son opcionales y funcionan como lo hacen con las cláusulas SELECT que ha aprendido a escribir. La palabra clave ALL es la predeterminada donde se permite la opción. La expresión listada en la sintaxis puede ser una constante, una función, o cualquier combinación de nombres de columna, constantes y funciones conectadas por operadores aritméticos. Sin embargo, las funciones agregadas se usan con mayor frecuencia con un nombre de columna. Excepto la función COUNT, todas las funciones agregadas no consideran valores NULL.

Hay dos reglas que debe comprender y seguir al usar agregados:

• Las funciones agregadas se pueden utilizar en las cláusulas SELECT y HAVING (la cláusula HAVING se trata más adelante en este capítulo).

• Las funciones agregadas no se pueden utilizar en una cláusula WHERE. Su infracción producirá el mensaje de error de la función de grupo Oracle ORA-00934 no se permite aquí.

Ilustraciones

La siguiente consulta SELECT cuenta el número de empleados de la organización.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

La siguiente consulta SELECT devuelve el promedio de los salarios de los empleados de la organización.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

La siguiente consulta SELECT devuelve la suma de los salarios de los empleados de la organización.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

La siguiente consulta SELECT devuelve las fechas de contratación más antiguas y más recientes de los empleados de la organización.

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

AGRUPAR POR

Las funciones agregadas se utilizan normalmente junto con una cláusula GROUP BY. La cláusula GROUP BY le permite utilizar funciones agregadas para responder preguntas de gestión más complejas como:

¿Cuál es el salario medio de los empleados de cada departamento?

¿Cuántos empleados trabajan en cada departamento?

¿Cuántos empleados están trabajando en un proyecto en particular?

Agrupar por función establece grupos de datos basados ​​en columnas y agrega la información dentro de un grupo solamente. El criterio de agrupación se define mediante las columnas especificadas en la cláusula GROUP BY. Siguiendo esta jerarquía, los datos se organizan primero en los grupos y luego la cláusula WHERE restringe las filas en cada grupo.

Directrices para el uso de la cláusula GROUP BY

(1) Todas las columnas dependientes o columnas utilizadas en la función GROUP BY deben formar la base de la agrupación, por lo tanto, deben incluirse también en la cláusula GROUP BY.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) La cláusula GROUP BY no admite el uso de alias de columna, sino los nombres reales.

(3) La cláusula GROUP BY solo se puede usar con funciones agregadas como SUM, AVG, COUNT, MAX y MIN. Si se usa con funciones de fila única, Oracle lanza una excepción como "ORA-00979: no es una expresión GROUP BY" .

(4) Las funciones agregadas no se pueden utilizar en una cláusula GROUP BY. Oracle devolverá el mensaje de error "ORA-00934: función de grupo no permitida" aquí.

La siguiente consulta enumera el recuento de empleados que trabajan en cada departamento.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

De manera similar, consulte a continuación para encontrar la suma de los salarios para los respectivos identificadores de trabajo en cada departamento. Tenga en cuenta que el grupo se establece según el departamento y la identificación del trabajo. Entonces aparecen en la cláusula GROUP BY.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

La siguiente consulta también produce el mismo resultado. Tenga en cuenta que la agrupación se basa en las columnas de identificación de departamento y de identificación de trabajo, pero no se utiliza para fines de visualización.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Uso de DISTINCT, ALL palabras clave con funciones agregadas

Al especificar la palabra clave DISTINCT con el parámetro de entrada, el grupo por función considera solo el valor único de la columna para la agregación. Al especificar la palabra clave ALL con el parámetro de entrada, el grupo por función considera todos los valores de la columna para la agregación, incluidos los nulos y los duplicados. TODO es la especificación predeterminada.

La cláusula HAVING

La cláusula HAVING se usa para funciones agregadas de la misma manera que una cláusula WHERE se usa para nombres de columna y expresiones Básicamente, las cláusulas HAVING y WHERE hacen lo mismo, es decir, filtrar filas para que no se incluyan en una tabla de resultados según una condición . Si bien puede parecer que una cláusula HAVING filtra grupos, no lo hace, sino que una cláusula HAVING filtra filas.

Cuando se eliminan todas las filas de un grupo, también lo es el grupo. Para resumir, las diferencias importantes entre las cláusulas WHERE y HAVING son:

Una cláusula WHERE se usa para filtrar filas ANTES de la acción GROUPING (es decir, antes del cálculo de las funciones agregadas).

Una cláusula HAVING filtra filas DESPUÉS de la acción GROUPING (es decir, después del cálculo de las funciones agregadas).

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

La cláusula HAVING es una opción condicional que está directamente relacionada con la opción de la cláusula GROUP BY porque una cláusula HAVING elimina filas de una tabla de resultados según el resultado de una cláusula GROUP BY.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function