 # SQL Server Wilcoxon matched-pair signed rank test

WMPSR

Updated: 24 May 2013

Use the table-valued function WMPSR to calculate the Wilcoxon matched-pair signed-rank test.
Syntax
SELECT [wctStatistics].[wct].[WMPSR](
<@InputData_RangeQuery, nvarchar(max),>
,<@RV, nvarchar(4000),>)
Arguments
@InputData_RangeQuery
a T-SQL statement, as a string, that specifies the matched pairs passed into the function.
@RV
the value to be returned by the function. Use the following values:

 'W' the test statistic 'Z' the z test statistic (used in the normal approximation of the p-value) 'PL' the ‘less than’ p-value 'PG' the ‘greater than’ p-value 'P2' the 2-sided p-value

Return Type
float
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 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.stat
,wct.WMPSR('SELECT x,y FROM #w',p.stat) as stat_value
FROM (
SELECT 'W' UNION ALL
SELECT 'Z' UNION ALL
SELECT 'PL' UNION ALL
SELECT 'PG' UNION ALL
SELECT 'P2') p(stat)
This produces the following result. ### Support  Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service