KSTEST2_q

Updated 2023-10-26 14:15:15.190000

Syntax

SELECT [westclintech].[wct].[KSTEST2_q](
  <@X_RangeQuery, nvarchar(max),>
 ,<@Statistic, nvarchar(4000),>)

Description

Use the scalar function KSTEST2_q to perform the two-sample Kolmogorov-Smirnov test to compare the distributions of the values in two samples. KSTEST2 produces the test statistic (k) and the p-value (p) which can be used to ascertain if the two samples are from the same continuous distribution.

Arguments

@Statistic

identifies the return value as either the Kolmogorov-Smirnov statistic (k) or the p-value (p).

@X_RangeQuery

a string which contains the SQL to select the sample values and the cumulative distiribution function. The resultant table must contain two columns containing the following values.

Return Type

float

Remarks

If @x is null then @x is not included in the calculation.

There must exactly 2 @sid in the resultant table from @X_RangeQuery.

To evaluate one sample against a continuous distribution function, use KSTEST or KSTEST_q.

Examples

In this example, we compare a sample of test scores for men and women.

/*Put data into a temp table*/

SELECT *

INTO #n

FROM

(

    SELECT 'Men',

           117

    UNION ALL

    SELECT 'Men',

           106

    UNION ALL

    SELECT 'Men',

           105

    UNION ALL

    SELECT 'Men',

           112

    UNION ALL

    SELECT 'Men',

           108

    UNION ALL

    SELECT 'Men',

           115

    UNION ALL

    SELECT 'Men',

           97

    UNION ALL

    SELECT 'Men',

           112

    UNION ALL

    SELECT 'Men',

           110

    UNION ALL

    SELECT 'Women',

           131

    UNION ALL

    SELECT 'Women',

           107

    UNION ALL

    SELECT 'Women',

           110

    UNION ALL

    SELECT 'Women',

           108

    UNION ALL

    SELECT 'Women',

           100

    UNION ALL

    SELECT 'Women',

           74

    UNION ALL

    SELECT 'Women',

           89

    UNION ALL

    SELECT 'Women',

           115

    UNION ALL

    SELECT 'Women',

           85

    UNION ALL

    SELECT 'Women',

           94

    UNION ALL

    SELECT 'Women',

           110

    UNION ALL

    SELECT 'Women',

           80

    UNION ALL

    SELECT 'Women',

           103

    UNION ALL

    SELECT 'Women',

           122

) n(s, x);

/*Calculate the test statistics and the p-value*/

SELECT wct.KSTEST2_q('SELECT s,x from #n', 'p') as p_value,

       wct.KSTEST2_q('SELECT s,x from #n', 'k') as k_observed;

/*Clean up the temp table*/

DROP TABLE #n;

This produces the following result.

{"columns":[{"field":"p_value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"k_observed","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"p_value":"0.297578744560361","k_observed":"0.388888888888889"}]}