linear regression - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

linear regression

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-11-26 : 16:45:51

Hi,

I'm looking to see if anyone knows how to perform linear regression in T-SQL. Haven't found anything online. I know FMS sells Total SQL Statistics which contains the T-SQL code for linear regression but I'm not exactly willing to drop $999 for a license. Any help?

Bill

baldeep
Starting Member

18 Posts

Posted - 2002-11-26 : 17:59:07

I believe you can create a Data Mining Model to do this using Analysis Services.

--Baldeep

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 18:23:56

Damn! And I just figured out how to do it in T-SQL:

set nocount on
declare @n int
create table #lr (x real not null, y real not null)
--insert sample data
insert into #lr values (0,1)
insert into #lr values (4,9)
insert into #lr values (2,5)
insert into #lr values (3,7)

select @n=count(*) from #lr --this is just for convenience, you can substitute count(*) for @n in the code

--meat of the code begins here
select
((@n * sum(x*y)) - (sum(x)*sum(y)))/
((@n * sum(Power(x,2)))-Power(Sum(x),2)) AS M,
avg(y) - ((@n * sum(x*y)) - (sum(x)*sum(y)))/
((@n * sum(Power(x,2)))-Power(Sum(x),2)) * avg(x) as B
from #lr

drop table #lr

This is the most basic least squares method, based on the formula posted here:

http://www.futuresource.com/industry/lin.asp

The formal terminology differs from what's presented on the page, the formula should be y = mx + b, where m is the slope and b is the y-intercept; my code uses this convention.

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-11-27 : 10:32:12

That's okay, Rob. Using the Analysis Services solution wouldn't have worked for me since I needed a T-SQL solution. I actually did what you did... scripted a regression function from scratch. Thanks!

Bill

linus1976
Starting Member

1 Post

Posted - 2011-11-17 : 23:33:48

Here is a pretty neat solution using cursor to perform rolling regression over a dataset:
http://knol.google.com/k/linear-regression-in-t-sql
Enjoy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources