Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 05-06-2009
Member
 
Join Date: Apr 2009
Posts: 24
sql server correlation coefficient

I need to calculate correlation coefficient with SQL Server source code. Is anyone know the SQL Server source code for calculating correlation...???

Please Help...!
Reply With Quote
  #2  
Old 05-06-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: sql server correlation coefficient

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
Reply With Quote
  #3  
Old 05-06-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: sql server correlation coefficient

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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "sql server correlation coefficient"
Thread Thread Starter Forum Replies Last Post
?Server browser is reporting with 0 Server found and 0 filtered? in Red Orchestra 2: Heroes Of Stalingrad Adamya Video Games 5 03-09-2011 10:59 PM
Med-V Inability to upload Packed Image to Server: could not connect to Images Server 2Fast Operating Systems 5 23-08-2010 08:13 AM
Slow Printer Performance on a Clustered Print Server in Windows Server 2003 Nathen Windows Software 5 19-03-2010 01:17 AM
Coefficient Core 2 Duo E8500 blocked Elsie Overclocking & Computer Modification 4 20-03-2009 11:10 PM
install a xp pro terminal server with windows server 2003 server rossen Operating Systems 3 13-03-2009 05:12 PM


All times are GMT +5.5. The time now is 02:51 AM.