SQL 2008 Excel-like LINEST Function

Once again, I find myself replicating functionality in SQL that Excel provides out of the box. A necessary step if you find yourself analyzing large data sets with rigorous math. This time, it's the LINEST function, Excel's tried and true linear regression function. Admittedly, what I built does not provide the same level of information, but it does provide the big three: slope, y-intercept, and r-Squared. Once the base linear regression function is built, extending to handle exponential regression becomes straightforward, but I will save that for a different post.

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