WLS

Updated 2024-02-13 20:16:54.467000

Syntax

SELECT * FROM [westclintech].[wct].[WLS] (
   <@TableName, nvarchar(max),>
  ,<@ColumnNames, nvarchar(max),>
  ,<@GroupedColumnName, nvarchar(max),>
  ,<@GroupedColumnValue, sql_variant,>
  ,<@LConst, bit,>
  ,<@y_Column, nvarchar(4000),>
  ,<@w_Column, nvarchar(4000),>)

Description

Use the 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.

Arguments

@GroupedColumnName

The name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results.

@w_Column

The column name or column number containing the weight (w) variable.

@ColumnNames

he 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.

@TableName

The name, as text, of the table or view that contains the values used in the calculation.

@GroupedColumnValue

The column value to do the grouping on.

@LConst

A bit value specifying the calculation of a y-intercept (@LConst =1) or regression through the origin (@LConst = 0).

@y_Column

The column name or column number containing the dependent (y) variable.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "60470735-9118-4d77-b6b9-03be34240dc2", "colName": "stat_name", "colDatatype": "nvarchar(4000)", "colDesc": "Identifies the statistic being returned: m \u2013 estimated coefficient se \u2013standard error of the estimated coefficient tstat \u2013 Student\u2019s T statistic pval \u2013 p-values of the tstat rsq \u2013 R2 rsqa \u2013 adjusted R2 rsqm \u2013 multiple R2 sey \u2013 standard error for the y estimate F \u2013 F statistic F_pval \u2013 p-value of F df \u2013 residual degrees of freedom ss_resid \u2013 weighted sum of squares mss \u2013 modified sum-of-squares w_resid_quart \u2013 weighted residual quartile"}, {"id": "9290d1a2-0f84-406f-9490-81f1b2fa9167", "colName": "idx", "colDatatype": "int", "colDesc": "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 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."}, {"id": "f5a0dc85-303c-4a95-9b2d-5c12d9c9c72a", "colName": "stat_val", "colDatatype": "float", "colDesc": "The return value."}, {"id": "e8cfde42-6aed-4e56-9e88-5f3e55fe7d93", "colName": "col_name", "colDatatype": "nvarchar(4000)", "colDesc": "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 left-most column in @ColumnNames .

I f @w_Column is NULL the @w_Column is the right-most column in @ColumnNames .

If @y_Column = @w_Column then no rows are returned.

I f @y_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.

Examples

This example explains the calculation of the regression coefficients and the summary statistics in WLS. 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;