SWTEST_q
Updated: 27 June 2012
Use the scalar function SWTEST_q to compute Shapiro and Wilk’s W statistic and its p-value. The p-value is the probability of observing the given result by chance.
Syntax
SELECT [wctStatistics].[wct].[SWTEST_q](
<@X_RangeQuery, nvarchar(max),>
,<@Statistic, nvarchar(4000),>)
Arguments
@X_RangeQuery
a string which contains the SQL to select the sample values. The resultant table must be ofan expression of type float or of a type that can be implicitly converted to float.
@Statistic
identifies the return value as either the Shapiro-Wilk statistic (w) or the p-value (p).
Return Types
float
Remarks
· If any x-value returned by @X_RangeQuery IS NULL, it is not included in the calculation.
· @x must have at least 3 rows
Examples
In this example, we have two samples, which we have identified as sample 1 and sample 2 and put into a temporary table. We perform the Shapiro Wilk test on both in a single SELECT returning a row for each sample number.
/*Insert test data into a temp table*/
SELECT *
INTO #sw
FROM (VALUES
(1,0.11),(2,3.49),
(1,7.87),(2,1.36),
(1,4.61),(2,1.14),
(1,10.14),(2,2.92),
(1,7.95),(2,2.55),
(1,3.14),(2,1.46),
(1,0.46),(2,1.06),
(1,4.43),(2,5.27),
(1,0.21),(2,-1.11),
(1,4.75),(2,3.48),
(1,0.71),(2,1.10),
(1,1.52),(2,0.88),
(1,3.24),(2,-0.51),
(1,0.93),(2,1.46),
(1,0.42),(2,0.52),
(1,4.97),(2,6.20),
(1,9.53),(2,1.69),
(1,4.55),(2,0.08),
(1,0.47),(2,3.67),
(1,6.66),(2,2.81)
) n(s,x)
/*Perform the Shapiro Wilk Test on the temp table*/
SELECT a.s as sample_num
,wct.SWTEST_q('SELECT x from #sw where s = ' + cast(a.s as varchar), 'w') as w_observed
,wct.SWTEST_q('SELECT x from #sw where s = ' + cast(a.s as varchar), 'p') as p_value
FROM (SELECT DISTINCT s FROM #sw) a(s)
GROUP BY a.s
/*Clean up the temp table*/
DROP TABLE #sw
This produces the following result
sample_num w_observed p_value
----------- ---------------------- ----------------------
1 0.90047287949498 0.0420895755448937
2 0.959026946057681 0.524597930966876