Caveat

This will work out of the box for SQL 2008. You'll need to modify for lower versions of SQL Server.

First create a new Table type (only available in SQL 2008)

CREATE TYPE dbo.RegressionInput AS TABLE

(

x DECIMAL(18, 6),

y DECIMAL(18, 6)

)

GO

Then create the function

CREATE FUNCTION dbo.LinearRegression

(

@RegressionInputs AS dbo.RegressionInput READONLY

)

RETURNS @RegressionOutput TABLE

(

Slope DECIMAL(18, 6),

Intercept DECIMAL(18, 6),

RSquare DECIMAL(18, 6)

)

AS

BEGIN

DECLARE @Xaverage AS DECIMAL(18, 6)

DECLARE @Yaverage AS DECIMAL(18, 6)

DECLARE @slope AS DECIMAL(18, 6)

DECLARE @intercept AS DECIMAL(18, 6)

DECLARE @rSquare AS DECIMAL(18, 6)

SELECT

@Xaverage = AVG(x),

@Yaverage = AVG(y)

FROM

@RegressionInputs

SELECT

@slope = SUM((x - @Xaverage) * (y - @Yaverage))/SUM(POWER(x - @Xaverage, 2))

FROM

@RegressionInputs

SELECT

@intercept = @Yaverage - (@slope * @Xaverage)

SELECT @rSquare = 1 - (SUM(POWER(y - (@intercept + @slope * x), 2))/(SUM(POWER(y - (@intercept + @slope * x), 2)) + SUM(POWER(((@intercept + @slope * x) - @Yaverage), 2))))

FROM

@RegressionInputs

INSERT INTO

@RegressionOutput

(

Slope,

Intercept,

RSquare

)

SELECT

@slope,

@intercept,

@rSquare

RETURN

END

GO

## 11 comments:

Hello. I'd like to know if you have this sql function to calculate the LINEST function using multiples values...

daniel_m_vaz@yahoo.com.br

Daniel,

I have no idea if you are still tracking this as I have been away from it for a while now, but to answer your question (if I think I understand it), no I have not implemented multi-linear (LINEST with multiple values) regression in SQL. That said, I do have a C# implementation since multi-linear regression involves linear algebra and I found SQL to be a bit "clunky" when I thought about it as an implementation language. I would be more than willing to share it with you if you'd like.

Hi!!

My name is Julia, and I would love to see your c# implementation for multiple regression.

Thanks!

Julia

Hi!!

My name is Chitra, and I would love to see your c# implementation for multiple regression.

Thanks!

Chitra

Chitra and Julia, I obviously have not attended to this blog in over a year, so apologies. If you are still interested, drop me a note and I'll get you the multiple regression implementation.

Hi,

I would Like to see C# or SQL implementation of Multiple Regression. Can you please let me know how to achieve this?

Hello

Can you please share your c# or SQL implementation of multi linear regression?

Can you please share your c# or SQL implementation of multi linear regression

Can you please share your c# or SQL implementation of multi linear regression

I have developed multiple regression in SQL. Haven't put it into a procedure, but that wouldn't be hard. it is at my blog In the SQL Data Mine:

http://sqldatamine.blogspot.com/2013/12/true-multiple-regression-using-sql.html

Post a Comment