Este artículo ofrece una descripción general de las funciones analíticas COVAR_POP y COVAR_SAMP. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.
- Preparar
- COVAR_POP and COVAR_SAMP como Funiones Agregadas
- Funcion Analitica COVAR_POP
- Funcion Analitica COVAR_SAMP
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;
COVAR_POP y COVAR_SAMP como funciones agregadas
Las funciones agregadas COVAR_POP y COVAR_SAMP se utilizan para calcular la covarianza poblacional y la covarianza muestral de un conjunto de datos, respectivamente. Como funciones agregadas, reducen el número de filas, de ahí el término «aggregate». Si los datos no están agrupados, convertimos las 14 filas de la tabla EMP en una sola fila con los valores agregados. En el siguiente ejemplo mostramos la covarianza de la duración del empleo con el salario.
SELECT COVAR_POP(SYSDATE - hiredate, sal) AS covar_pop_val, COVAR_SAMP(SYSDATE - hiredate, sal) AS covar_samp_val FROM emp; COVAR_POP_VAL COVAR_SAMP_VAL ------------- -------------- -18125.255 -19519.505 SQL>
Podemos obtener más granularidad de la información al incluir una cláusula GROUP BY. En el siguiente ejemplo, vemos los valores por trabajo.
SELECT job, COVAR_POP(SYSDATE - hiredate, sal) AS covar_pop_val_by_job, COVAR_SAMP(SYSDATE - hiredate, sal) AS covar_samp_val_by_job FROM emp GROUP BY job ORDER BY job; JOB COVAR_POP_VAL_BY_JOB COVAR_SAMP_VAL_BY_JOB --------- -------------------- --------------------- ANALYST 1.0000E-31 2.0000E-31 CLERK -55390.625 -73854.167 MANAGER 6102.77778 9154.16667 PRESIDENT 0 SALESMAN 3325 4433.33333 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 COVAR_POP
La función COVAR_POP devuelve la covarianza de la población después de descartar los valores nulos. La función realiza el siguiente cálculo.
(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n
La descripción básica de la función analítica COVAR_POP se muestra a continuación.
COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ]
El uso de una cláusula OVER vacía convierte la función COVAR_POP 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 la covarianza poblacional de la duración del empleo con el salario después de descartar los valores nulos, así como todos los datos originales.
SELECT empno, ename, deptno, sal, job, COVAR_POP(SYSDATE - hiredate, sal) OVER () AS covar_pop_val FROM emp; EMPNO ENAME DEPTNO SAL JOB COVAR_POP_VAL ---------- ---------- ---------- ---------- --------- ------------- 7369 SMITH 20 800 CLERK -18125.255 7499 ALLEN 30 1600 SALESMAN -18125.255 7521 WARD 30 1250 SALESMAN -18125.255 7566 JONES 20 2975 MANAGER -18125.255 7654 MARTIN 30 1250 SALESMAN -18125.255 7698 BLAKE 30 2850 MANAGER -18125.255 7782 CLARK 10 2450 MANAGER -18125.255 7788 SCOTT 20 3000 ANALYST -18125.255 7839 KING 10 5000 PRESIDENT -18125.255 7844 TURNER 30 1500 SALESMAN -18125.255 7876 ADAMS 20 1100 CLERK -18125.255 7900 JAMES 30 950 CLERK -18125.255 7902 FORD 20 3000 ANALYST -18125.255 7934 MILLER 10 1300 CLERK -18125.255 SQL>
Agregar la cláusula de partición nos permite mostrar la covarianza poblacional de la duración del empleo con el salario por trabajo, junto con los datos del empleado.
SELECT empno, ename, deptno, sal, job, COVAR_POP(SYSDATE - hiredate, sal) OVER (PARTITION BY job) AS covar_pop_val_by_job FROM emp; EMPNO ENAME DEPTNO SAL JOB COVAR_POP_VAL_BY_JOB ---------- ---------- ---------- ---------- --------- -------------------- 7788 SCOTT 20 3000 ANALYST 5.0000E-32 7902 FORD 20 3000 ANALYST 5.0000E-32 7934 MILLER 10 1300 CLERK -55390.625 7900 JAMES 30 950 CLERK -55390.625 7369 SMITH 20 800 CLERK -55390.625 7876 ADAMS 20 1100 CLERK -55390.625 7698 BLAKE 30 2850 MANAGER 6102.77778 7566 JONES 20 2975 MANAGER 6102.77778 7782 CLARK 10 2450 MANAGER 6102.77778 7839 KING 10 5000 PRESIDENT 0 7844 TURNER 30 1500 SALESMAN 3325 7654 MARTIN 30 1250 SALESMAN 3325 7521 WARD 30 1250 SALESMAN 3325 7499 ALLEN 30 1600 SALESMAN 3325 SQL>
Función analítica COVAR_SAMP
La función COVAR_SAMP devuelve la covarianza de muestra después de descartar los valores nulos. Si no hay registros en el conjunto después de descartar los valores nulos, el valor devuelto es NULL. La función realiza el siguiente cálculo.
(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
La descripción básica de la función analítica COVAR_SAMP se muestra a continuación.
COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
El uso de una cláusula OVER vacía convierte la función COVAR_SAMP 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 la covarianza de muestra de la duración del empleo con el salario después de descartar los valores nulos, así como todos los datos originales.
SELECT empno, ename, deptno, sal, job, COVAR_SAMP(SYSDATE - hiredate, sal) OVER () AS covar_samp_val FROM emp; EMPNO ENAME DEPTNO SAL JOB COVAR_SAMP_VAL ---------- ---------- ---------- ---------- --------- -------------- 7369 SMITH 20 800 CLERK -19519.505 7499 ALLEN 30 1600 SALESMAN -19519.505 7521 WARD 30 1250 SALESMAN -19519.505 7566 JONES 20 2975 MANAGER -19519.505 7654 MARTIN 30 1250 SALESMAN -19519.505 7698 BLAKE 30 2850 MANAGER -19519.505 7782 CLARK 10 2450 MANAGER -19519.505 7788 SCOTT 20 3000 ANALYST -19519.505 7839 KING 10 5000 PRESIDENT -19519.505 7844 TURNER 30 1500 SALESMAN -19519.505 7876 ADAMS 20 1100 CLERK -19519.505 7900 JAMES 30 950 CLERK -19519.505 7902 FORD 20 3000 ANALYST -19519.505 7934 MILLER 10 1300 CLERK -19519.505 SQL>
Agregar la cláusula de partición nos permite mostrar la covarianza de muestra de la duración del empleo con el salario por trabajo, junto con los datos del empleado.
SELECT empno, ename, deptno, sal, job, COVAR_SAMP(SYSDATE - hiredate, sal) OVER (PARTITION BY job) AS covar_samp_val_by_dept FROM emp; EMPNO ENAME DEPTNO SAL JOB COVAR_SAMP_VAL_BY_DEPT ---------- ---------- ---------- ---------- --------- ---------------------- 7788 SCOTT 20 3000 ANALYST 1.0000E-31 7902 FORD 20 3000 ANALYST 1.0000E-31 7934 MILLER 10 1300 CLERK -73854.167 7900 JAMES 30 950 CLERK -73854.167 7369 SMITH 20 800 CLERK -73854.167 7876 ADAMS 20 1100 CLERK -73854.167 7698 BLAKE 30 2850 MANAGER 9154.16667 7566 JONES 20 2975 MANAGER 9154.16667 7782 CLARK 10 2450 MANAGER 9154.16667 7839 KING 10 5000 PRESIDENT 7844 TURNER 30 1500 SALESMAN 4433.33333 7654 MARTIN 30 1250 SALESMAN 4433.33333 7521 WARD 30 1250 SALESMAN 4433.33333 7499 ALLEN 30 1600 SALESMAN 4433.33333 SQL>