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)