Stepwise Linear Regression in t-SQL

The code below will perform a two step linear regression in t-sql.

-- The data table looks like x1,x2,y,dataid

DECLARE @Data TABLE (
	x FLOAT,
	y FLOAT
)

--step 1
DELETE FROM @Data

INSERT INTO @Data
SELECT x1,y FROM Data WHERE DataID>90

DECLARE @m FLOAT,
		@sumX FLOAT,
		@sumX2 FLOAT,
		@sumY FLOAT,
		@sumXY FLOAT,
		@D FLOAT,
		@A1 FLOAT,
		@A2 FLOAT,
		@B FLOAT

SELECT	@m = COUNT(1),
		@sumX = SUM(x), 
		@sumX2 = SUM(POWER(x,2)), 
		@sumY = SUM(Y), 
		@sumXY=SUM((x)*(y)) 
FROM @Data

SET @D=@sumX2*@m-POWER(@sumX,2)
SELECT @b=(@sumX2*@sumY-@sumX*@sumXY)/@D
SELECT @a1=(@m*@sumXY-@sumX*@sumY)/@D
PRINT @a1
PRINT @a2
PRINT @b

--step 2
DELETE FROM @Data

INSERT INTO @Data
SELECT x2,y-(@A1*x1+@b) FROM Data WHERE DataID>90

SELECT	@m = COUNT(1),
		@sumX = SUM(x), 
		@sumX2 = SUM(POWER(x,2)), 
		@sumY = SUM(Y), 
		@sumXY=SUM((x)*(y)) 
FROM @Data

SET @D=@sumX2*@m-POWER(@sumX,2)

-- y = a1 * x1 + a2 * x2 + x
SELECT @a2=(@m*@sumXY-@sumX*@sumY)/@D
PRINT @a1
PRINT @a2
PRINT @b

Additional Interesting Articles

Pearson Coefficient
Looping Through a SQL Result in C#
C# Regular Expression Example
t-SQL Cursor
Regex C# HTML
Block File Leechers Using PHP

©2008 AndrewKimball.com