Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server Weighted Least Squares function


WLS
Updated: 01 Oct 2017

Use the SQL Server table-valued function WLS to calculate the Ordinary Least Squares (OLS) solution for a series of x- and y-values and an associated column of weights; sometimes referred to as Weighted Least-Squares (WLS).

WLS returns the coefficients of regression, standard errors, Student’s T and associated p-value for each of the independent variables. It also returns summary statistics about the regression including the standard error of y, R2, adjusted R2, the F-statistic and its p-value, 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 p-values 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 NameDefinition
@TableNameThe name, as text, of the table or view that contains the values used in the calculation.
@ColumnNamesThe 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.
@GroupedColumnNameThe name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results.
@GroupedColumnValueThe column value to do the grouping on.
@LConstA bit value specifying the calculation of a y-intercept (@LConst =1) or regression through the origin (@LConst = 0).
@y_ColumnThe column name or column number containing the dependent (y) variable.
@w_ColumnThe 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
ColumnDefinition
stat_nameIdentifies the statistic being returned:
  • m – estimated coefficient
  • se –standard error of the estimated coefficient
  • tstat – Student’s T statistic
  • pval – p-values of the tstat
  • rsq – R2
  • rsqa – adjusted R2
  • rsqm – multiple R2
  • sey – standard error for the y estimate
  • F – F statistic
  • F_pval – p-value of F
  • df – residual degrees of freedom
  • ss_resid – weighted sum of squares
  • mss – modified sum-of-squares
  • w_resid_quart – weighted residual quartile
idxUniquely 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 y-intercept (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_valThe return value.
col_nameThe 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 left-most column in @ColumnNames.
  • If @w_Column is NULL the @w_Column is the right-most 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.

SELECT
    stat_name
    ,idx-1 as idx
    ,stat_val
    ,col_name
INTO
    #ols
FROM
    wct.LINEST('#t','y*SQRT(w) as y, sqrt(w) as Intercept, x1*SQRT(w) as x1, x2*SQRT(w) as x2','',NULL,1,0)
WHERE
    idx > 0 OR idx IS NULL
 
SELECT
    *
FROM
    #ols

This produces the following result.

The following SQL produces a side-by-side 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 sum-of-squares 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


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service