KSTEST2

Updated 2023-10-26 14:13:12.697000

Syntax

SELECT [westclintech].[wct].[KSTEST2] (
  <@sid, nvarchar(4000),> 
 ,<@x, float,> 
 ,<@statistic, nvarchar(4000),>)

Description

Use the aggregate function KSTEST2 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

@sid

the sample identifier. @sid indentifies which of the two samples @ x belongs to.

@x

the sample value. @x is an expression of type float or of a type that can be implicitly converted to float.

@statistic

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

Return Type

float

Remarks

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

There must exactly 2 @sid within a GROUP.

@statistic must be invariant for the GROUP.

KSTEST2 is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

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

Examples

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

SELECT wct.KSTEST2(s, x, 'p') as p_value,

       wct.KSTEST2(s, x, 'k') as k_observed

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);

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"}]}