ZTEST

Updated 2023-10-26 20:05:20.020000

Syntax

SELECT [westclintech].[wct].[ZTEST] (
  <@known_x, float,>
 ,<@mu, nvarchar(4000),>
 ,<@sigma, float,>)

Description

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

Arguments

@mu

the value at which to evaluate the function. @mu is an expression of type float or of a type that can be implicitly converted to float.

@known_x

the x-values supplied to the function. @Known_x 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 Type

float

Remarks

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

Examples

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

SELECT wct.ZTEST(x, 4, 0) as ZTEST

FROM

(

    SELECT 3

    UNION ALL

    SELECT 6

    UNION ALL

    SELECT 7

    UNION ALL

    SELECT 8

    UNION ALL

    SELECT 6

    UNION ALL

    SELECT 5

    UNION ALL

    SELECT 4

    UNION ALL

    SELECT 2

    UNION ALL

    SELECT 1

    UNION ALL

    SELECT 9

) n(x);

This produces the following result.

{"columns":[{"field":"ZTEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ZTEST":"0.0905741968513638"}]}

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

SELECT CASE

           WHEN wct.ZTEST(x, 6, 0) > .5 THEN

               1 - wct.ZTEST(x, 6, 0)

           ELSE

               wct.ZTEST(x, 6, 0)

       END * 2 as ZTEST

FROM

(

    SELECT 3

    UNION ALL

    SELECT 6

    UNION ALL

    SELECT 7

    UNION ALL

    SELECT 8

    UNION ALL

    SELECT 6

    UNION ALL

    SELECT 5

    UNION ALL

    SELECT 4

    UNION ALL

    SELECT 2

    UNION ALL

    SELECT 1

    UNION ALL

    SELECT 9

) n(x);

This produces the following result

{"columns":[{"field":"ZTEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ZTEST":"0.27391322174094"}]}