ZTEST_q

Updated 2023-10-27 01:21:06.550000

Syntax

SELECT [westclintech].[wct].[ZTEST_q] (
  <@Values_RangeQuery, nvarchar(4000),>
 ,<@Test_value, float,>
 ,<@Sigma, float,>)

Description

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

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 Type

float

Remarks

No GROUP BY is required for this function even though it produces aggregated results.

For simpler queries or for queries on de-normalized data, use the ZTEST function.

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.

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

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.

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

In this example we see the underlying math behind the calculation.

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.

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