SQL Server WMPSR_TV Function

Updated 2023-11-06 18:57:49

Description

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

Syntax

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

Arguments

@x_y_Query

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

Return Type

table

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.