CHITEST2
Updated: 6 August 2010
Use CHITEST2 to calculate the Pearson chi-square test for independence. CHITEST2 returns the value from the chi-square (χ2) distribution for the statistic and the appropriate degrees of freedom. Calculate the chi-square statistic (χ2) directly using the CHISQ2 or the CHISQ2_q function.
The chi-square statistic is calculated by finding the difference between each observed and theoretical frequency for each possible outcome, squaring them, dividing each by the theoretical frequency, and taking the sum of the results. A second important part of determining the test statistic is to define the degrees of freedom of the test: this is essentially the number of squares errors involving the observed frequencies adjusted for the effect of using some of those observations to define the expected frequencies.
Given the test statistic and the degrees of freedom, the test value is returned by the regularized gamma function Q(a, x) where:
a is the degrees of freedom divided by 2
x is χ2 statistic divided by 2
CHITEST2 requires the expected results as input to the function but automatically calculates the degrees of freedom
The value of the test statistic is
Where
r is the number of rows
c is the number of columns
O is the Observed result
E is the Expected result
Syntax
SELECT [wctStatistics].[wct].[CHITEST2] (
<@Actual_range_TableName, nvarchar(4000),>
,<@AR_ColumnNames, nvarchar(4000),>
,<@AR_GroupedColumnName, nvarchar(4000),>
,<@AR_GroupedColumnValue, sql_variant,>
,<@Expected_range_TableName, nvarchar(4000),>
,<@ER_ColumnNames, nvarchar(4000),>
,<@ER_GroupedColumnName, nvarchar(4000),>
,<@ER_GroupedColumnValue, sql_variant,>)
Arguments
@Actual_range_TableName
the name, as text, of the table or view that contains the actual, or observed, results to be used in the CHITEST calculation.
@AR_ColumnNames
the names, as text, of the columns in the de-normalized table or view specified by @Actual_range_TableName that contains the values to be used in the CHITEST calculation.
@AR_GroupedColumnName
the name, as text, of the column in the table or view specified by @Actual_range_TableName which will be used for grouping the results.
@AR_GroupedColumnValue
the column value to do the grouping on.
@Expected_range_TableName
the name, as text, of the table or view that contains the expected results to be used in the CHITEST calculation.
@ER_ColumnNames
the names, as text, of the columns in the de-normalized table or view specified by @Expected_range_TableName that contains the expected results to be used in the CHITEST calculation.
@ER_GroupedColumnName
the name, as text, of the column in the table or view specified by @Expected_range_TableName which will be used for grouping the results.
@ER_GroupedColumnValue
the column value to do the grouping on.
Return Types
float
Remarks
· CHITEST2 is designed for de-normalized tables. For normalized tables, use the CHITESTN2 function.
· CHITEST2 automatically calculates the expected values.
· For queries that are more complex, consider using the CHITEST2_q function.
· CHITEST2 = CHIDIST(χ2, df), where df = (r-1)(c-1), r>1, c>1
· To calculate the test statistic, use the CHISQ2 function
· CHITEST2 requires the expected values as input to the function. If you want to perform the chi-square test without providing the expected values, use the CHITEST function.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
In this hypothetical situation, we want to determine if there is an association between population density and the preference for a sport from among baseball, football, and basketball. We will use the CHITEST2 function to perform the chi-squared test.
CREATE TABLE #O(
[Sport] [varchar] (20) NOT NULL,
[Rural] [float] NOT NULL,
[Suburban] [float] NOT NULL,
[Urban] [float] NOT NULL
)
INSERT INTO #O VALUES ('Basketball',28,35,54)
INSERT INTO #O VALUES ('Baseball',60,43,35)
INSERT INTO #O VALUES ('Football',52,48,28)
CREATE TABLE #E(
[Sport] [varchar] (20) NOT NULL,
[Rural] [float] NOT NULL,
[Suburban] [float] NOT NULL,
[Urban] [float] NOT NULL
)
INSERT INTO #E VALUES ('Basketball',42.77,38.49,35.74)
INSERT INTO #E VALUES ('Baseball',50.44,45.4,42.16)
INSERT INTO #E VALUES ('Football',46.79,42.11,39.1)
SELECT wct.CHITEST2(
'#O'
,'Rural, Suburban, Urban'
,''
,NULL
,'#E'
,'Rural, Suburban, Urban'
,''
,NULL)
This produces the following result
----------------------
0.000162575660939674
(1 row(s) affected)