Correlation(CORR) Analytic Functions in Oracle SQL
CORR returns the coefficient of correlation of a set of number data.
Correlation coefficients are used in statistics to measure how strong a relationship is between two variables.
Correlation coefficient formulas are used to find how strong a relationship is between data. The formulas return a value between -1 and 1.
Output means:
1 indicates a strong positive relationship.
-1 indicates a strong negative relationship.
0 indicates no relationship at all.
Oracle Make following computation:
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
Example 1:
Correlation between duration at the company and salary by the employee’s position by job posisiton. It implicit convert to numeric value.
SELECT employee_id, job_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary,
CORR(SYSDATE-hire_date, salary)
OVER(PARTITION BY job_id) AS "Correlation"
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
EMPLOYEE_ID JOB_ID Yrs-Mns SALARY Correlation
----------- ---------- ------- ---------- -----------
145 SA_MAN +14-05 14000 .912385598
146 SA_MAN +14-02 13500 .912385598
147 SA_MAN +14-00 12000 .912385598
148 SA_MAN +11-05 11000 .912385598
149 SA_MAN +11-02 10500 .912385598
150 SA_REP +14-01 10000 .80436755
151 SA_REP +14-00 9500 .80436755
152 SA_REP +13-07 9000 .80436755
153 SA_REP +12-11 8000 .80436755
154 SA_REP +12-03 7500 .80436755
155 SA_REP +11-04 7000 .80436755
156 SA_REP +15-01 10000 .80436755
157 SA_REP +15-00 9500 .80436755
158 SA_REP +14-07 9000 .80436755
Example 2:
Correlation between duration at the company and salary by the employee’s position department wise.
SELECT employee_id, department_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary,
CORR(SYSDATE-hire_date, salary)
OVER(partition by department_id) AS "Correlation"
FROM employees
WHERE department_id in (50, 80)
ORDER BY department_id, employee_id;
EMPLOYEE_ID DEPARTMENT_ID Yrs-Mns SALARY Correlation
----------- ------------- ------- ---------- -----------
120 50 +14-08 8000 .500062192
121 50 +13-11 8200 .500062192
122 50 +15-10 7900 .500062192
123 50 +13-05 6500 .500062192
124 50 +11-04 5800 .500062192
125 50 +13-08 3200 .500062192
126 50 +12-06 2700 .500062192
127 50 +12-02 2400 .500062192
128 50 +11-00 2200 .500062192
