CORR聚集函数来计算相关系数
CORR:皮尔逊相关系数,是用于度量两个变量X和Y之间的相关(线性相关),其值介于-1与1之间。
CORR_S:斯皮尔曼等级相关
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;
SELECT deptno,
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 deptno
ORDER BY deptno;
SELECT empno, ename, deptno,
sal, job,
CORR(SYSDATE - hiredate, sal) OVER () AS corr_val
FROM emp;
SELECT empno, ename, deptno,
sal, job,
CORR(SYSDATE - hiredate, sal) OVER (PARTITION BY deptno) AS corr_val
FROM emp;
SELECT empno, ename, deptno,
sal,job,
CORR(SYSDATE - hiredate, sal) OVER (PARTITION BY job) AS corr_val
FROM emp
ORDER BY JOB, deptno;