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>
