WMPSR_TV

Updated 2023-11-06 18:57:49

Syntax

SELECT * FROM [westclintech].[wct].[WMPSR_TV](
   <@x_y_Query, nvarchar(max),>)

Description

Use the table-valued function WMPSR_TV to calculate the Wilcoxon matched-pair signed-rank test.

Arguments

@x_y_Query

a T-SQL statement, as a string, that specifies the matched pairs passed into the function.

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": "55204384-ab23-4f33-97d1-11fa7fb85dfc", "colName": "W", "colDatatype": "float", "colDesc": "the test statistic"}, {"id": "cef554f1-b2e9-4df8-bfc1-d599bc6b64b9", "colName": "Z", "colDatatype": "float", "colDesc": "the z test statistic (used in the normal approximation of the p-value)"}, {"id": "1a4b142e-e3ab-433b-8036-63a0e4e13785", "colName": "PL", "colDatatype": "float", "colDesc": "the 'less than' p-value"}, {"id": "79afd786-cdb7-4212-bd82-ea2c118b2a1b", "colName": "PG", "colDatatype": "float", "colDesc": "the 'greater than' p-value"}, {"id": "4411d00b-7ce9-41cf-b9df-3f015f0c09b6", "colName": "P2", "colDatatype": "float", "colDesc": "the 2-sided p-value"}]}

Remarks

The function is insensitive to order; it does not matter what order the pairs are passed in.

Ranks are calculated based on the absolute value in the difference of a pair. If we call the first value in the pair x and the second y, then the rank is calculated based on ABS(x-y)

Pairs where x = y are not included in the rank calculation.

Ranks ties are calculated as the average of the rank in the interval occupied by the tie.

Once the ranks are calculated, the rank is multiplied by SIGN(x-y). The W-statistic is the sum of absolute values where SIGN(x-y) = 1.

If the number of ranked pairs is less than 50, then the exact p-value is calculated using the PSIGNRANK function, otherwise the NORMSDIST function is used.

Examples

SELECT x,

       y

INTO #w

FROM

(

    SELECT 5260,

           3910

    UNION ALL

    SELECT 5470,

           4220

    UNION ALL

    SELECT 5640,

           3885

    UNION ALL

    SELECT 6180,

           5160

    UNION ALL

    SELECT 6390,

           5645

    UNION ALL

    SELECT 6515,

           4680

    UNION ALL

    SELECT 6805,

           5265

    UNION ALL

    SELECT 7515,

           5975

    UNION ALL

    SELECT 7515,

           6790

    UNION ALL

    SELECT 8230,

           6900

    UNION ALL

    SELECT 8770,

           7335

) w(x, y);

SELECT p.*

FROM wct.WMPSR_TV('SELECT x,y FROM #w')

    --This CROSS APPLY UNPIVOTS the tvf columns for formatting

    CROSS APPLY

(

    SELECT 'W',

           W

    UNION ALL

    SELECT 'Z',

           Z

    UNION ALL

    SELECT 'PL',

           PL

    UNION ALL

    SELECT 'PG',

           PG

    UNION ALL

    SELECT 'P2',

           P2

) p(stat, value_stat);

DROP TABLE #w;

This produces the following result.

{"columns":[{"field":"stat"},{"field":"value_stat","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"stat":"W","value_stat":"66"},{"stat":"Z","value_stat":"2.93550859009007"},{"stat":"PL","value_stat":"0.00166500695587299"},{"stat":"PG","value_stat":"0.998334993044127"},{"stat":"P2","value_stat":"0.00333001391174598"}]}