Updated: 9 August 2010
Use CORREL_q to calculate the correlation coefficient between two datasets. The equation for the correlation coefficient is
Syntax
SELECT [wctStatistics].[wct].[CORREL_q] (
<@Known_y_Known_x_RangeQuery, nvarchar(4000),>)
Arguments
@Known_y_Known_x_RangeQuery
the select statement, as text, used to determine the known y- and x-values to be used in the CORREL_q calculation.
Return Types
float
Remarks
· If the number of known-x data points is different than the number of known-y data points, CORREL_q will return an error
· If the standard deviation of either set of data points is zero, then CORREL_q will return an error.
· This function works with normalized and de-normalized tables.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
To determine the correlation between grades in math and grades in science on a normalized table:
select wct.CORREL_q('Select a.grade, b.grade
from #s1 a, #s1 b
where a.subject = ' + Char(39) + 'Math' + Char(39) + ' and
b.subject = ' + Char(39) + 'Science' + Char(39) + 'and
a.student = b.student')
This produces the following result
----------------------
0.999999494275124
(1 row(s) affected)
To determine the correlation between grades in math and grades in science on a de-normalized table:
SELECT wct.CORREL_q('SELECT math, science from #s2')
This produces the following result
----------------------
0.999999494275124
(1 row(s) affected)