Correlation(CORR) Analytic Functions in Oracle SQL

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.