|Oracle8i SQL Reference
Release 2 (8.1.6)
Functions, 17 of 121
For information on syntax and semantics, see "Analytic Functions".
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:
The function returns a value of type
NUMBER. If the function is applied to an empty set, it returns null.
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
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.