CORR

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

CORR returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.

Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) after eliminating the pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:

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

The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.

See Also: "Aggregate Functions".

Aggregate Example

The following example calculates the coefficient of correlation between the salaries and commissions of the employees whose manager is 7698 from the EMP table:

SELECT mgr, CORR(sal, comm) FROM EMP
   GROUP BY mgr
   HAVING mgr = 7698;

       MGR CORR(SAL,COMM)
---------- --------------
      7698     -.69920974

Analytic Example

The following example returns the cumulative coefficient of correlation of monthly sales and monthly profits from the SALES table for year 1998:

SELECT s_month, CORR(SUM(s_amount), SUM(s_profit))
   OVER (ORDER BY s_month) AS CUM_CORR
   FROM sales
   WHERE s_year=1998
   GROUP BY s_month
   ORDER BY s_month;

 S_MONTH    CUM_CORR  
---------- ----------
         6           
         8          1
        11 .860554259

Correlation functions require more than one row on which to operate, so the first row in the preceding example has no value calculated for it.