Funciones analíticas COVAR_POP y COVAR_SAMP

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>

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *