I need to calculate correlation coefficient with SQL Server source code. Is anyone know the SQL Server source code for calculating correlation...???
Please Help...!
Printable View
I need to calculate correlation coefficient with SQL Server source code. Is anyone know the SQL Server source code for calculating correlation...???
Please Help...!
Here is an example for the same you wanted with T-SQL qurries using vTargetMail view from AdventureWorksDW demo Database :
Code:USE AdventureWorksDW;
GO
DECLARE @mean1 decimal(20,6)
DECLARE @mean2 decimal(20,6)
SELECT @mean1=AVG(YearlyIncome*1.0)
,@mean2=AVG(NumberCarsOwned*1.0)
FROM vTargetMail
SELECT Correl=
(SUM((YearlyIncome*1.0-@mean1)*(NumberCarsOwned*1.0-@mean2))/COUNT(*))
/((STDEVP(YearlyIncome*1.0)*STDEVP(NumberCarsOwned* 1.0))),
CD=SQUARE(
(SUM((YearlyIncome*1.0-@mean1)*(NumberCarsOwned*1.0-@mean2))/COUNT(*))
/((STDEVP(YearlyIncome*1.0)*STDEVP(NumberCarsOwned* 1.0))))
FROM vTargetMail
GO
Here is the query for example :
Code:SELECT
user1, user2,
((psum - (sum1 * sum2 / n)) / sqrt((sum1sq - pow(sum1, 2.8) / n) * (sum2sq - pow(sum2, 2.8) / n))) AS r,
n
FROM
(SELECT
n1.user AS user1,
n2.user AS user2,
SUM(n1.rating) AS sum1,
SUM(n2.rating) AS sum2,
SUM(n1.rating * n1.rating) AS sum1sq,
SUM(n2.rating * n2.rating) AS sum2sq,
SUM(n1.rating * n2.rating) AS psum,
COUNT(*) AS n
FROM
testdata AS n1
LEFT JOIN
testdata AS n2
ON
n1.movie = n2.movie
WHERE
n1.user > n2.user
GROUP BY
n1.user, n2.user) AS step1
ORDER BY
r DESC,
n DESC