Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E1711803 


View PDF 
The CORR_
* functions are:
CORR_S
CORR_K
Syntax
correlation::=
Purpose
The CORR
function (see CORR) calculates the Pearson's correlation coefficient and requires numeric expressions as input. The CORR_
* functions support nonparametric or rank correlation. They let you find correlations between expressions that are ordinal scaled (where ranking of the values is possible). Correlation coefficients take on a value ranging from 1 to 1, where 1 indicates a perfect relationship, 1 a perfect inverse relationship (when one variable increases as the other decreases), and a value close to 0 means no relationship.
These functions takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, makes the calculation, and returns NUMBER
.
See Also:
Table 310, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedenceexpr1
and expr2
are the two variables being analyzed. The third argument is a return value of type VARCHAR2
. If you omit the third argument, then the default is COEFFICIENT
. The meaning of the return values is shown in the table that follows:
Table 52 CORR_* Return Values
Return Value  Meaning 


Coefficient of correlation 

Positive onetailed significance of the correlation 

Same as 

Negative onetailed significance of the correlation 

Twotailed significance of the correlation 
CORR_S
calculates the Spearman's rho correlation coefficient. The input expressions should be a set of (x_{i}, y_{i}) pairs of observations. The function first replaces each value with a rank. Each value of x_{i} is replaced with its rank among all the other x_{i}s in the sample, and each value of y_{i} is replaced with its rank among all the other y_{i}s. Thus, each x_{i} and y_{i} take on a value from 1 to n
, where n
is the total number of pairs of values. Ties are assigned the average of the ranks they would have had if their values had been slightly different. Then the function calculates the linear correlation coefficient of the ranks.
CORR_S Example Using Spearman's rho correlation coefficient, the following example derives a coefficient of correlation for each of two different comparisons  salary
and commission_pct
, and salary
and employee_id
:
SELECT COUNT(*) count, CORR_S(salary, commission_pct) commission, CORR_S(salary, employee_id) empid FROM employees; COUNT COMMISSION EMPID    107 .735837022 .04473016
CORR_K
calculates the Kendall's taub correlation coefficient. As for CORR_S
, the input expressions are a set of (x_{i}, y_{i}) pairs of observations. To calculate the coefficient, the function counts the number of concordant and discordant pairs. A pair of observations is concordant if the observation with the larger x also has a larger value of y. A pair of observations is discordant if the observation with the larger x has a smaller y.
The significance of taub is the probability that the correlation indicated by taub was due to chance—a value of 0 to 1. A small value indicates a significant correlation for positive values of taub (or anticorrelation for negative values of taub).
CORR_K Example Using Kendall's taub correlation coefficient, the following example determines whether a correlation exists between an employee's salary and commission percent:
SELECT CORR_K(salary, commission_pct, 'COEFFICIENT') coefficient, CORR_K(salary, commission_pct, 'TWO_SIDED_SIG') two_sided_p_value FROM employees; COEFFICIENT TWO_SIDED_P_VALUE   .603079768 3.4702E07