Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server Z.TEST function


 
Updated: 6 August 2010

Use ZTEST_q to calculate the one-tailed probability of a Z-test. The Z-test is a statistical test used in inference which determines if the difference between a sample mean and the population mean is large enough to be statistically significant, that is, if it is unlikely to have occurred by chance. The Z-test is used primarily with standardized testing to determine if the test scores of a particular sample of test takers are within or outside of the standard performance of test takers.
Syntax
SELECT [wctStatistics].[wct].[ZTEST_q] (
   <@Values_RangeQuery, nvarchar(4000),>
 ,<@Test_value, float,>
 ,<@Sigma, float,>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the values to be used in the ZTEST_q calculation.
@Test_value
the value at which to evaluate the function. @Test_value is an expression of type float or of a type that can be implicitly converted to float.
@Sigma
is the population (known) standard deviation. If zero, the sample standard deviation is used. @Sigma is an expression of type float or of a type that can be implicitly converted to float.
 
Return Types
float
Remarks
·         For simpler queries or for queries on de-normalized data, use the ZTEST function.
·         No GROUP BY is required for this function even though it produces aggregated results.
ZTEST_q = 1 - wct.NORMSDIST(
(wct.AVERAGE(@Values_RangeQuery) - @Testvalue)/
(wct.STDEV(@Values_RangeQuery)/
SQRT(wct.COUNT(@Values_RangeQuery))))

Examples
CREATE TABLE #z1(
      [num] [float] NOT NULL
)
INSERT INTO #z1 VALUES (3)
INSERT INTO #z1 VALUES (6)
INSERT INTO #z1 VALUES (7)
INSERT INTO #z1 VALUES (8)
INSERT INTO #z1 VALUES (6)
INSERT INTO #z1 VALUES (5)
INSERT INTO #z1 VALUES (4)
INSERT INTO #z1 VALUES (2)
INSERT INTO #z1 VALUES (1)
INSERT INTO #z1 VALUES (9)
 

To calculate the one-tailed probability-value of a z-test for the dataset above, at the hypothesized population mean of 4:

select wct.ZTEST_q('Select num from #z1',4,0)
 

This produces the following result

----------------------
0.0905741968513638
 
(1 row(s) affected)

To calculate the two-tailed probability-value of a z-test for the dataset above, at the hypothesized population mean of 6:

SELECT
CASE
WHEN wct.ZTEST_q('Select num from #z1',6,0) > .5 THEN 1-wct.ZTEST_q('Select num from #z1',6,0)
      ELSE wct.ZTEST_q('Select num from #z1',6,0)
END * 2
 

This produces the following result

----------------------
0.27391322174094
 
(1 row(s) affected)
 
SELECT 1 - wct.NORMSDIST(
      (wct.AVERAGE_q('Select num from #z1') - 4)/
      (wct.STDEV_q('Select num from #z1')/
      SQRT(wct.COUNT_q('Select num from #z1'))))

This produces the following result

----------------------
0.0905741968513638
 
(1 row(s) affected)

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service