Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server ks.test function


KSTEST2_q

Updated: 27 June 2012


Use the scalra 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.
Syntax
SELECT [wctStatistics].[wct].[KSTEST2_q](
  <@X_RangeQuery, nvarchar(max),>
 ,<@Statistic, nvarchar(4000),>)
Arguments
@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.
@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 Types
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
·         For simpler queries, consider using KSTEST2
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
               p_value             k_observed
---------------------- ----------------------
     0.297578744560361      0.388888888888889
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service