SQL Server SWTEST_q Function

Updated 2023-10-26 14:29:51.933000

Description

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 [westclintech].[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 of an 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 Type

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

{"columns":[{"field":"sample_num","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"w_observed","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"p_value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"sample_num":"1","w_observed":"0.90047287949498","p_value":"0.0420895755448937"},{"sample_num":"2","w_observed":"0.959026946057681","p_value":"0.524597930966876"}]}