Usar funciones de grupo
AREAtutorial » Bases de Datos » Usar funciones de grupo
SQL tiene muchas funciones agregadas predefinidas que puede usar para escribir consultas para obtener ese tipo de información. La cláusula GROUP BY define cómo agrupar filas de la tabla de datos al agregar información, y la cláusula HAVING filtra las filas que no pertenecen a los grupos especificados.
Las funciones de agregación realizan muchas cosas, como contar todas las filas de una tabla, promediar los datos de las columnas y sumar datos numéricos. Los agregados también pueden buscar en la tabla para encontrar el valor máximo «MAX» o el valor mínimo «MIN» en una columna. Al igual que con otros tipos de consultas, puede limitar o filtrar las filas con las que trabajan estas funciones mediante la cláusula WHERE. Por ejemplo, si un gerente necesita saber cuántos empleados trabajan en una organización, la función agregada COUNT
SELECT COUNT(*)
FROM employees;
COUNT(*)
----------
24
se puede utilizar para obtener esta información.
La función que se muestra en la instrucción SELECT a continuación cuenta todas las filas de la tabla.
SUM( [ALL | DISTINCT] expression ) AVG( [ALL | DISTINCT] expression ) COUNT( [ALL | DISTINCT] expression ) COUNT(*) MAX(expression) MIN(expression)
Tabla de resultados de COUNT
una función es una sola columna de una fila conocida como resultado o valor escalar. Tenga en cuenta que la tabla de resultados tiene un encabezado de columna que coincide con el nombre de la función agregada especificada en la cláusula SELECT.
Algunas de las funciones agregadas comúnmente utilizadas se enumeran a continuación:
Las palabras clave ALL y DISTINCT son opcionales y funcionan de la misma manera que con las cláusulas SELECT que aprendió a escribir. La palabra clave ALL es la predeterminada cuando la opción está habilitada. La expresión especificada en la sintaxis puede ser una constante, una función o cualquier combinación de nombres de columna, constantes y funciones asociadas con operadores aritméticos. Sin embargo, las funciones agregadas se usan más comúnmente con un nombre de columna. A excepción de la función COUNT, todas las funciones agregadas reconocen nulos.
Las funciones agregadas se pueden usar tanto en cláusulas SELECT como en cláusulas HAVING (la cláusula HAVING se analiza más adelante en este capítulo).
SELECT COUNT(*) Count FROM employees; COUNT ----- 24
Las funciones agregadas no se pueden utilizar en una cláusula WHERE. Violarlo causará que la función de grupo Oracle ORA-00934 no esté permitida aquí mensaje de error.
SELECT AVG(Salary) average_sal
FROM employees;
AVERAGE_SAL
-----------
15694
Ilustraciones
SELECT SUM(Salary) total_sal
FROM employees;
TOTAL_SAL
---------
87472
La consulta SELECT a continuación cuenta el número de empleados en una organización.
SELECT MIN (hire_date) oldest, MAX (hire_date) latest FROM employees; OLDEST LATEST --------- ----------- 16-JAN-83 01-JUL-2012
La siguiente consulta SELECT devuelve la suma de los salarios de los empleados de una organización.
La siguiente consulta SELECT devuelve las fechas de contratación más antiguas y más recientes para los empleados de una organización.
AGRUPAR POR
Las funciones agregadas se usan comúnmente junto con la 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 están trabajando en un proyecto específico?
SELECT DEPARTMENT_ID, SUM(SALARY) FROM employees; DEPARTMENT_ID, * ERROR at line 2: ORA-00937: not a single-group group function
Agrupar por función crea grupos de datos basados en columnas y agrega información solo dentro del grupo. El criterio de agrupación está determinado por las columnas especificadas en la cláusula GROUP BY. Siguiendo esta jerarquía, los datos se ordenan primero en grupos y luego la cláusula WHERE delimita las filas de cada grupo.
Recomendaciones para usar 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, también deben incluirse en la cláusula GROUP BY.
(2) La cláusula GROUP BY no admite el uso de un alias de columna, pero admite nombres reales.
SELECT DEPARTMENT_ID, COUNT (*) FROM employees GROUP BY DEPARTMENT_ID;
(3) La cláusula GROUP BY solo se puede utilizar con funciones agregadas como SUM, AVG, COUNT, MAX y MIN. Si se utiliza con funciones de una sola línea, Oracle lanza una excepción como «ORA-00979: no expresión GROUP BY». …
SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID;
(4) Las funciones agregadas no se pueden utilizar en la cláusula GROUP BY. Oracle devolverá el mensaje de error «ORA-00934: función de grupo no permitida» aquí.
SELECT SUM (SALARY) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID;
Del mismo modo, la consulta se encuentra a continuación para encontrar la suma del salario para los respectivos ID de trabajo en cada departamento. Tenga en cuenta que el grupo se crea según el ID y el título del departamento. Por tanto, aparecen en la cláusula GROUP BY.
Usando DISTINCT, TODAS las palabras clave con funciones agregadas
Cuando especifica la palabra clave DISTINCT como parámetro de entrada, la agrupación por función solo considera el valor único de la columna que se agregará. Si especifica la palabra clave ALL como parámetro de entrada, la agrupación por función tiene en cuenta todos los valores de columna para la agregación, incluidos los ceros y los duplicados. TODO es la especificación predeterminada.
TENIENDO cláusula
La cláusula HAVING se usa para funciones agregadas de la misma manera que la cláusula WHERE se usa para nombres y expresiones de columna. Básicamente, las cláusulas HAVING y WHERE hacen lo mismo, es decir, filtran filas para que no se incluyan en la tabla de resultados según una condición. Si bien la cláusula HAVING puede parecer que filtra grupos, no lo es. Más bien, la cláusula HAVING filtra filas.
SELECT JOB_ID, SUM (SALARY) FROM employees GROUP BY JOB_ID HAVING SUM (SALARY) > 10000;
Cuando se eliminan todas las filas de un grupo, el grupo también se elimina. En resumen, las diferencias importantes entre las cláusulas WHERE y HAVING son las siguientes:
SELECT department_id, AVG(Salary) FROM employees HAVING AVG(Salary) > 33000; ERROR at line 1: ORA-00937: not a single-group group function
La cláusula WHERE se usa para filtrar filas ANTES de una acción GROUPING (es decir, antes de evaluar funciones agregadas). La cláusula HAVING filtra filas DESPUÉS de la acción GROUPING (es decir, después de evaluar las funciones agregadas). La cláusula HAVING es un parámetro condicional que está directamente relacionado con el parámetro de la cláusula GROUP BY, porque la cláusula HAVING elimina filas de la tabla de resultados según el resultado de la cláusula GROUP BY.
🚫