Este artículo ofrece una descripción general de las funciones analíticas AVG (media) y MEDIAN. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.
- Preparar
- AVG and MEDIAN como funciones agregadas
- Funcion Analitica AVG (Mean)
- Funcion Analitica MEDIAN
- Enlaces rapido
Preparar
Los ejemplos de este artículo requieren la siguiente tabla.
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
AVG y MEDIAN como funciones agregadas
Las funciones agregadas AVG y MEDIAN se utilizan para calcular los valores medio y mediano de un conjunto de datos, respectivamente. Como funciones agregadas, reducen el número de filas, de ahí el término «agregado». Si los datos no están agrupados, convertimos las 14 filas de la tabla EMP en una sola fila con los valores agregados.
SELECT AVG(sal) AS mean_sal,
MEDIAN(sal) AS media_sal
FROM emp;
MEAN_SAL MEDIA_SAL
---------- ----------
2073.21429 1550
SQL>
Podemos obtener más granularidad de la información al incluir una cláusula GROUP BY. En el siguiente ejemplo, vemos los valores de la media y la mediana por departamento.
SELECT deptno,
AVG(sal) AS mean_sal,
MEDIAN(sal) AS media_sal
FROM emp
GROUP BY deptno
ORDER BY deptno;
DEPTNO MEAN_SAL MEDIA_SAL
---------- ---------- ----------
10 2916.66667 2450
20 2175 2975
30 1566.66667 1375
SQL>
En ambos casos, hemos agregado los datos para obtener los valores, devolviendo menos filas de las que comenzamos. Las funciones analíticas nos permiten devolver estos valores agregados conservando los datos de la fila original.
Función analítica AVG (media)
La descripción básica de la función analítica de AVG se muestra a continuación.
AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
El uso de una cláusula OVER vacía convierte la función AVG en una función analítica. La falta de una cláusula de partición significa que todo el conjunto de resultados se trata como una sola partición, por lo que obtenemos el salario medio de todos los empleados, así como todos los datos originales.
SELECT empno,
ename,
deptno,
sal,
AVG(sal) OVER () AS mean_sal
FROM emp
ORDER BY deptno;
EMPNO ENAME DEPTNO SAL MEAN_SAL
---------- ---------- ---------- ---------- ----------
7782 CLARK 10 2450 2073.21429
7839 KING 10 5000 2073.21429
7934 MILLER 10 1300 2073.21429
7566 JONES 20 2975 2073.21429
7902 FORD 20 3000 2073.21429
7876 ADAMS 20 1100 2073.21429
7369 SMITH 20 800 2073.21429
7788 SCOTT 20 3000 2073.21429
7521 WARD 30 1250 2073.21429
7844 TURNER 30 1500 2073.21429
7499 ALLEN 30 1600 2073.21429
7900 JAMES 30 950 2073.21429
7698 BLAKE 30 2850 2073.21429
7654 MARTIN 30 1250 2073.21429
SQL>
Agregar la cláusula de partición nos permite mostrar el salario promedio por departamento, junto con los datos de los empleados de cada departamento.
SELECT empno,
ename,
deptno,
sal,
AVG(sal) OVER (PARTITION BY deptno) AS mean_sal_by_dept
FROM emp;
EMPNO ENAME DEPTNO SAL MEAN_SAL_BY_DEPT
---------- ---------- ---------- ---------- ----------------
7782 CLARK 10 2450 2916.66667
7839 KING 10 5000 2916.66667
7934 MILLER 10 1300 2916.66667
7566 JONES 20 2975 2175
7902 FORD 20 3000 2175
7876 ADAMS 20 1100 2175
7369 SMITH 20 800 2175
7788 SCOTT 20 3000 2175
7521 WARD 30 1250 1566.66667
7844 TURNER 30 1500 1566.66667
7499 ALLEN 30 1600 1566.66667
7900 JAMES 30 950 1566.66667
7698 BLAKE 30 2850 1566.66667
7654 MARTIN 30 1250 1566.66667
SQL>
Función analítica MEDIAN
La descripción básica de la función analítica MEDIAN se muestra a continuación.
MEDIAN(expr) [ OVER (query_partition_clause) ]
El uso de una cláusula OVER vacía convierte la función MEDIAN en una función analítica. La falta de una cláusula de partición significa que todo el conjunto de resultados se trata como una sola partición, por lo que obtenemos el salario medio de todos los empleados, así como todos los datos originales.
SELECT empno,
ename,
deptno,
sal,
MEDIAN(sal) OVER () AS median_sal
FROM emp;
EMPNO ENAME DEPTNO SAL MEDIAN_SAL
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 1550
7900 JAMES 30 950 1550
7876 ADAMS 20 1100 1550
7521 WARD 30 1250 1550
7654 MARTIN 30 1250 1550
7934 MILLER 10 1300 1550
7844 TURNER 30 1500 1550
7499 ALLEN 30 1600 1550
7782 CLARK 10 2450 1550
7698 BLAKE 30 2850 1550
7566 JONES 20 2975 1550
7788 SCOTT 20 3000 1550
7902 FORD 20 3000 1550
7839 KING 10 5000 1550
SQL>
Agregar la cláusula de partición nos permite mostrar el salario medio por departamento, junto con los datos de los empleados de cada departamento.
SELECT empno,
ename,
deptno,
sal,
MEDIAN(sal) OVER (PARTITION BY deptno) AS median_sal_by_dept
FROM emp;
EMPNO ENAME DEPTNO SAL MEDIAN_SAL_BY_DEPT
---------- ---------- ---------- ---------- ------------------
7934 MILLER 10 1300 2450
7782 CLARK 10 2450 2450
7839 KING 10 5000 2450
7369 SMITH 20 800 2975
7876 ADAMS 20 1100 2975
7566 JONES 20 2975 2975
7788 SCOTT 20 3000 2975
7902 FORD 20 3000 2975
7900 JAMES 30 950 1375
7654 MARTIN 30 1250 1375
7521 WARD 30 1250 1375
7844 TURNER 30 1500 1375
7499 ALLEN 30 1600 1375
7698 BLAKE 30 2850 1375
SQL>
