Función analítica CORR

Este artículo ofrece una descripción general de la función analítica CORR. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.

• Preparar
• CORR como una funcion agregada
• Funcion Analitica CORR
• Enlace 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;

CORR como función agregada

La función agregada CORR se utiliza para calcular el coeficiente de correlación o coeficiente de correlación de Pearson. Como función agregada, reduce 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. El siguiente ejemplo incluye las funciones CORR_S (coeficiente de correlación rho de Spearman) y CORR_K (coeficiente de correlación tau-b de Kendall), que solo están disponibles como funciones agregadas. En el siguiente ejemplo, estamos correlacionando la duración del empleo con el salario.

SELECT CORR(SYSDATE - hiredate, sal) AS corr_val,
       CORR_S(SYSDATE - hiredate, sal) AS corr_s_val,
       CORR_K(SYSDATE - hiredate, sal) AS corr_k_val
FROM emp;

  CORR_VAL CORR_S_VAL CORR_K_VAL
---------- ---------- ----------
-.02116564 .082690238 .111733587

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,
       CORR(SYSDATE - hiredate, sal) AS corr_val_by_job,
       CORR_S(SYSDATE - hiredate, sal) AS corr_s_val_by_job,
       CORR_K(SYSDATE - hiredate, sal) AS corr_k_val_by_job
FROM emp
GROUP BY job
ORDER BY job;

JOB       CORR_VAL_BY_JOB CORR_S_VAL_BY_JOB CORR_K_VAL_BY_JOB
--------- --------------- ----------------- -----------------
ANALYST
CLERK           -.3250778                .4        .333333333
MANAGER        .978243943                 1                 1
PRESIDENT
SALESMAN       .205987887        .210818511        .182574186

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 CORR

La función analítica CORR se utiliza para calcular el coeficiente de correlación o coeficiente de correlación de Pearson. La función calcula lo siguiente en las filas del conjunto sin valores nulos para ninguna de las expresiones.

COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))

La función devuelve un valor entre +1 y -1, que representan lo siguiente, junto con una interpretación de ejemplo.

• +1: Correlación lineal positiva total. Cuanto más tiempo haya estado empleado, más le pagarán proporcionalmente.
•  0: Sin correlación lineal. No existe relación entre el tiempo cumplido y el pago.
•  -1: Correlación lineal negativa total. Cuanto más tiempo haya estado empleado, menos le pagarán proporcionalmente.

La descripción básica de la función analítica CORR se muestra a continuación.

CORR(expr1, expr2) [ OVER (analytic_clause) ]

El uso de una cláusula OVER vacía convierte la función CORR 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 correlación de la duración del empleo con el salario de todos los empleados, así como todos los datos originales.

SELECT empno,
       ename,
       deptno,
       sal,
       job,
       CORR(SYSDATE - hiredate, sal) OVER () AS corr_val
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL JOB         CORR_VAL
---------- ---------- ---------- ---------- --------- ----------
      7369 SMITH              20        800 CLERK     -.02116564
      7499 ALLEN              30       1600 SALESMAN  -.02116564
      7521 WARD               30       1250 SALESMAN  -.02116564
      7566 JONES              20       2975 MANAGER   -.02116564
      7654 MARTIN             30       1250 SALESMAN  -.02116564
      7698 BLAKE              30       2850 MANAGER   -.02116564
      7782 CLARK              10       2450 MANAGER   -.02116564
      7788 SCOTT              20       3000 ANALYST   -.02116564
      7839 KING               10       5000 PRESIDENT -.02116564
      7844 TURNER             30       1500 SALESMAN  -.02116564
      7876 ADAMS              20       1100 CLERK     -.02116564
      7900 JAMES              30        950 CLERK     -.02116564
      7902 FORD               20       3000 ANALYST   -.02116564
      7934 MILLER             10       1300 CLERK     -.02116564

SQL>

Agregar la cláusula de partición nos permite mostrar la correlación por departamento, junto con los datos de los empleados.

SELECT empno,
       ename,
       deptno,
       sal,
       job,
       CORR(SYSDATE - hiredate, sal) OVER (PARTITION BY deptno) AS corr_val
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL JOB         CORR_VAL
---------- ---------- ---------- ---------- --------- ----------
      7782 CLARK              10       2450 MANAGER   .074769211
      7839 KING               10       5000 PRESIDENT .074769211
      7934 MILLER             10       1300 CLERK     .074769211
      7566 JONES              20       2975 MANAGER   .044630566
      7902 FORD               20       3000 ANALYST   .044630566
      7876 ADAMS              20       1100 CLERK     .044630566
      7369 SMITH              20        800 CLERK     .044630566
      7788 SCOTT              20       3000 ANALYST   .044630566
      7521 WARD               30       1250 SALESMAN  .417964304
      7844 TURNER             30       1500 SALESMAN  .417964304
      7499 ALLEN              30       1600 SALESMAN  .417964304
      7900 JAMES              30        950 CLERK     .417964304
      7698 BLAKE              30       2850 MANAGER   .417964304
      7654 MARTIN             30       1250 SALESMAN  .417964304

SQL>

Alternativamente, podemos mostrar la correlación por tipo de trabajo, junto con los datos del empleado.

SELECT empno,
       ename,
       deptno,
       sal,
       job,
       CORR(SYSDATE - hiredate, sal) OVER (PARTITION BY job) AS corr_val
FROM   emp
ORDER BY JOB, deptno;

     EMPNO ENAME          DEPTNO        SAL JOB         CORR_VAL
---------- ---------- ---------- ---------- --------- ----------
      7902 FORD               20       3000 ANALYST
      7788 SCOTT              20       3000 ANALYST
      7934 MILLER             10       1300 CLERK      -.3250778
      7369 SMITH              20        800 CLERK      -.3250778
      7876 ADAMS              20       1100 CLERK      -.3250778
      7900 JAMES              30        950 CLERK      -.3250778
      7782 CLARK              10       2450 MANAGER   .978243943
      7566 JONES              20       2975 MANAGER   .978243943
      7698 BLAKE              30       2850 MANAGER   .978243943
      7839 KING               10       5000 PRESIDENT
      7654 MARTIN             30       1250 SALESMAN  .205987887
      7844 TURNER             30       1500 SALESMAN  .205987887
      7521 WARD               30       1250 SALESMAN  .205987887
      7499 ALLEN              30       1600 SALESMAN  .205987887

SQL>

Enlace rapido

Para mas información:

CORR