Updated: 01 Oct 2017
Use the SQL Server tablevalued function WLS to calculate the Ordinary Least Squares (OLS) solution for a series of x and yvalues and an associated column of weights; sometimes referred to as Weighted LeastSquares (WLS).
WLS returns the coefficients of regression, standard errors, Student’s T and associated pvalue for each of the independent variables. It also returns summary statistics about the regression including the standard error of y, R^{2}, adjusted R^{2}, the Fstatistic and its pvalue, the regression sum of squares, the residual sum of squares and the quartiles of the residuals.
WLS is closely related to LINEST and the regression coefficients and their standard errors, T statistics, and pvalues can be calculated in LINEST, though LINEST will not produce the correct summary statistics. See Example 1 to find out more.
Syntax
SELECT * FROM [wct].[WLS] (
<@TableName, nvarchar(max),>
,<@ColumnNames, nvarchar(max),>
,<@GroupedColumnName, nvarchar(max),>
,<@GroupedColumnValue, sql_variant,>
,<@LConst, bit,>
,<@y_Column, nvarchar(4000),>
,<@w_Column, nvarchar(4000),>)
Arguments
Input Name  Definition 
@TableName  The name, as text, of the table or view that contains the values used in the calculation. 
@ColumnNames  The names, as text, of the columns in @TableName that contain the values used in the calculation. The column names include the independent variables (x0,x1…xn), the dependent variable (y) and the weight (w). Data returned from the @ColumnNames must be of a type float or of a type that intrinsically converts to float. 
@GroupedColumnName  The name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results. 
@GroupedColumnValue  The column value to do the grouping on. 
@LConst  A bit value specifying the calculation of a yintercept (@LConst =1) or regression through the origin (@LConst = 0). 
@y_Column  The column name or column number containing the dependent (y) variable. 
@w_Column  The column name or column number containing the weight (w) variable. 
Return Type
RETURNS TABLE (
[stat_name] [nvarchar](20) NULL,
[idx] [int] NULL,
[stat_val] [float] NULL,
[col_name] [nvarchar](128) NULL
)
Table Description
Column  Definition 
stat_name  Identifies the statistic being returned:
 m – estimated coefficient
 se –standard error of the estimated coefficient
 tstat – Student’s T statistic
 pval – pvalues of the tstat
 rsq – R^{2}
 rsqa – adjusted R^{2}
 rsqm – multiple R^{2}
 sey – standard error for the y estimate
 F – F statistic
 F_pval – pvalue of F
 df – residual degrees of freedom
 ss_resid – weighted sum of squares
 mss – modified sumofsquares
 w_resid_quart – weighted residual quartile

idx  Uniquely identifies a return value for the stat_names where multiple values are returned: m, se, tstat, pval, and w_resid_quart.
For m, se, tstat, and pval, idx identifies that subscript of the estimated coefficient. For example, the stat_name m with an idx of 0, specifies that the stat_val is for m0, or the yintercept (which is b in y = mx + b). An idx of 1 for the same stat_name identifies m1.
For w_resid_quart idx identifies the quartile being returned.
For all other stat_names returning a single value, the idx will be NULL. 
stat_val  The return value. 
col_name  The column name from the resultant table produced by the dynamic SQL for the m, se, tstat, and pval stat_names. 
Remarks
 If @y_Column is NULL then @y_Column is the leftmost column in @ColumnNames.
 If @w_Column is NULL the @w_Column is the rightmost column in @ColumnNames.
 If @y_Column = @w_Column then no rows are returned.
 If @y_Column is numeric and less than 1 or greater than the number of columns in @ColumnNames then no rows are returned.
 If @w_Column is numeric and less than 1 or greater than the number of columns in @ColumnNames then no rows are returned.
 Weight values must be greater than zero.
 The number of rows in the regression must be greater than or equal to the number of columns.
 Available in XLeratorDB / statistics 2008 only
Examples
Example #1
This example explains the calculation of the regression coefficients and the summary statistics in WLS. Let’s set up an example in SQL Server and take a closer look at those calculation. We will put the WLS results into a temp table, #wls.
SELECT
*
INTO
#t
FROM (VALUES
(103,126.8,62.3,0.420928305104083)
,(127.2,115.7,98,0.642347072957175)
,(118,103.4,92.2,0.503672280805613)
,(121.8,95.2,74.2,0.349193063289055)
,(106.1,96,78.9,0.321793289794097)
,(124.6,124.7,96.1,1.34249371606786)
,(116.9,122.2,94.1,0.401800920329203)
,(118.6,128.2,79.2,0.67140606947821)
,(125.2,116.9,79.6,0.336969408869812)
,(123.3,112.3,87.8,0.556210387357181)
)n(y,x1,x2,w)
Use the WLS function to calculate the coefficients of regression and the associated statistics.
SELECT
*
INTO
#wls
FROM
wct.WLS('#t','y,x1,x2,w','',NULL,1,'y','w')
SELECT
*
FROM
#wls
This produces the following result.
There is nothing further that needs to be done in terms of getting the regression results. The rest of this example serves as an explanation of how the results are calculated.
To calculate weighted least squares the dependent variable and all the independent variables are multiplied by the square root of the weights. We can achieve that result in LINEST by setting @LConst = 0 and by manually creating the intercept in the result table. The following SQL does that and puts the results into a temp tale #ols.
This produces the following result.
The following SQL produces a sidebyside comparison of the regression results.
SELECT
w.stat_name
,w.idx
,w.stat_val as [wls]
,o.stat_val as [ols]
FROM
#wls w
FULL null">OUTER JOIN
#ols o
ON
(w.stat_name = o.stat_name AND ISNULL(w.idx,0) = ISNULL(o.idx,0))
OR (w.stat_name = 'mss' AND o.stat_name = 'ss_reg')
This produces the following result.
Notice that LINEST does a pretty good job with the m, se, tstat, pval, sey, df, and ss_resid statistics. And LINEST does not calculate F_pval nor does it calculate the quartiles of the residuals. The real difference arises with the calculation of the sum of squares of regression (ss_reg in LINEST; mss in WLS) which is used in the calculation of rsq, F, rsqm and rsqa.
The ss_reg value in LINEST is calculated as sum of yhat (y ̂) squared, where yhat is simply the independent variables multiplied by the coefficients of regression. We can see that calculation in the following SQL.
SELECT SUM(SQUARE([m0]*n.Intercept+[m1]*x1+[m2]*x2)) as ss_reg
FROM (
SELECT
'm' + cast(idx as char(1)) as coef,
stat_val
FROM
#ols
WHERE
stat_name = 'm'
)d
PIVOT (max(stat_val) FOR coef in (m0,m1,m2))pvt
CROSS JOIN
(SELECT sqrt(w) as Intercept, x1*SQRT(w) as x1, x2*SQRT(w) as x2 FROM #t)n
This produces the following result.
For weighted least squares, this calculation needs to be adjusted for the weights. The following SQL shows how to make this adjustment.
DECLARE @wavg_y as float = (SELECT wct.WAVG(w,y) FROM #t)
DECLARE @mss as float = (
SELECT SUM(POWER([m0]*n.Intercept+[m1]*x1+[m2]*x2@wavg_y,2)*w) as mss
FROM (
SELECT
'm' + cast(idx as char(1)) as coef,
stat_val
FROM
#ols
WHERE
stat_name = 'm'
)d
PIVOT (max(stat_val) FOR coef in (m0,m1,m2))pvt
CROSS JOIN
(SELECT 1 as Intercept, x1, x2, w FROM #t)n
)
SELECT @mss as mss
This produces the following result.
Having gotten the regression modified sumofsquares value, we can then use the same formulas as in ordinary least squares to calculate the remaining statistics
DECLARE @ssresid as float = (SELECT stat_val from #wls WHERE stat_name = 'ss_resid')
DECLARE @df as float = (SELECT stat_val from #wls WHERE stat_name = 'df')
DECLARE @rsq as float = @mss/(@mss+@ssresid)
DECLARE @rsqm as float = SQRT(@rsq)
DECLARE @p as float = (SELECT COUNT(*)1 FROM #t)
DECLARE @rsqa as float = 1  (1  @rsq) * @p/ @df
DECLARE @Fobs as float = @mss / ((@p  @df) * @ssresid / @df)
DECLARE @Fdist as float = wct.F_DIST_RT(@Fobs, @p  @df, @df)
SELECT
stat_name,
NULL as idx,
stat_val
FROM (VALUES
('rsq',@rsq)
,('mss',@mss)
,('rsqm',@rsqm)
,('rsqa',@rsqa)
,('F',@Fobs)
,('F_pval',@Fdist)
)x(stat_name,stat_val)
This produces the following result.
Finally, the w_resid_quart values are simply the quartiles of the residuals.
SELECT
'w_resid_quart' as stat_name
,x.k as idx
,wct.QUARTILE(y  ([m0]*n.Intercept+[m1]*x1+[m2]*x2),x.k) as stat_val
FROM (
SELECT
'm' + cast(idx as char(1)) as coef,
stat_val
FROM
#ols
WHERE
stat_name = 'm'
)d
PIVOT (max(stat_val) FOR coef in (m0,m1,m2))pvt
CROSS JOIN
(SELECT y*sqrt(w) as y,sqrt(w) as Intercept, x1 * sqrt(w) as x1, x2 * sqrt(w) as x2 FROM #t)n
CROSS APPLY
(VALUES (0),(1),(2),(3),(4))x(k)
GROUP BY
x.k
This produces the following result.
See Also