CHITESTN
Updated 2023-10-30 14:19:07.113000
Syntax
SELECT [westclintech].[wct].[CHITESTN] (
<@expr1, nvarchar(4000),>
,<@expr2, nvarchar(4000),>
,<@obs, float,>)
Description
Use the aggregate function CHITESTN to calculate the Pearson chi-square test for independence on normalized tables. CHITESTN returns the value from the chi-square (χ2) distribution for the chi-square statistic and the appropriate degrees of freedom. Calculate the chi-square statistic (χ2) directly using the CHISQN or the CHISQN_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:
{
"columns": [
{
"field": "column 1",
"maxWidth": 100
},
{
"field": "column 2",
"maxWidth": 325
}
],
"rows": [
{
"column 1": "a",
"column 2": "is the degrees of freedom divided by 2"
},
{
"column 1": "?",
"column 2": "is the χ² statistic divided by 2"
} ]
}
CHITESTN automatically calculates the expected results and the degrees of freedom.
The value of the test statistic is
\chi^2=\sum_{i=1}^r\sum_{j=1}^c\frac{(O_{i,j} - E_{i,j})^2}{E_{i,j}}
Where
{
"columns": [
{
"field": "column 1",
"maxWidth": 100
},
{
"field": "column 2",
"maxWidth": 225
}
],
"rows": [
{
"column 1": "r",
"column 2": "is the number of columns"
},
{
"column 1": "c",
"column 2": "is the number of columns"
},
{
"column 1": "O",
"column 2": "is the Observed result"
},
{
"column 1": "E",
"column 2": "is the Expected result"
} ]
}
Arguments
@obs
the observed value. @Obs is an expression of type float or of a type that can be implicitly converted to float.
@expr2
the name or number of the columns.
@expr1
the name or number of the rows.
Return Type
float
Remarks
CHITESTN is designed for normalized tables. For de-normalized tables, use the scalar function CHITEST.
CHITESTN automatically calculates the expected values.
CHITESTN = CHIDIST(χ2, df), where df = (r-1)(c-1), r>1, c>1.
Use the CHISQN function to calculate the test statistic.
CHITESTN is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
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 CHITESTN function to perform the chi-square test.
SELECT wct.CHITESTN( sport, --@Expr1
locale, --@Expr2
result --@obs
) as CHITEST
FROM
(
VALUES
('Basketball', 'Rural', 28),
('Basketball', 'Suburban', 35),
('Basketball', 'Urban', 54),
('Baseball', 'Rural', 60),
('Baseball', 'Suburban', 43),
('Baseball', 'Urban', 35),
('Football', 'Rural', 52),
('Football', 'Suburban', 48),
('Football', 'Urban', 28)
) n (sport, locale, result);
This produces the following result.
{"columns":[{"field":"CHITEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CHITEST":"0.000162912223138266"}]}
What if the data had not been normalized? We could use that following SQL to take de-normalized data (the way it might be in a spreadsheet) and turn it into normalized data.
SELECT wct.CHITESTN(n.sport, x.locale, x.result) as CHITEST
FROM
(
VALUES
('Basketball', 28, 35, 54),
('Baseball', 60, 43, 35),
('Football', 52, 48, 28)
) n (sport, rural, suburban, urban)
CROSS APPLY
(
VALUES
('rural', rural),
('suburban', suburban),
('urban', urban)
) x (locale, result);
This produces the following result.
{"columns":[{"field":"CHITEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CHITEST":"0.000162912223138266"}]}
In this example, the de-normalized table contains another column, region. We will calculate the chi-square test for each region.
SELECT n.region,
wct.CHITESTN(n.sport, x.locale, x.result) as CHITEST
FROM
(
VALUES
('Midwest', 'Baseball', 27, 38, 36),
('Midwest', 'Basketball', 46, 33, 31),
('Midwest', 'Football', 43, 29, 36),
('Northeast', 'Baseball', 39, 46, 42),
('Northeast', 'Basketball', 42, 34, 36),
('Northeast', 'Football', 35, 30, 41),
('Southeast', 'Baseball', 31, 36, 33),
('Southeast', 'Basketball', 32, 39, 34),
('Southeast', 'Football', 31, 24, 40),
('Southwest', 'Baseball', 44, 19, 34),
('Southwest', 'Basketball', 37, 42, 24),
('Southwest', 'Football', 41, 29, 36),
('West', 'Baseball', 43, 33, 30),
('West', 'Basketball', 44, 38, 34),
('West', 'Football', 30, 36, 30)
) n (region, sport, urban, rural, suburban)
CROSS APPLY
(
VALUES
('rural', rural),
('suburban', suburban),
('urban', urban)
) x (locale, result)
GROUP BY region;
This produces the following result.
{"columns":[{"field":"region"},{"field":"CHITEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"region":"Midwest","CHITEST":"0.145577930808542"},{"region":"Northeast","CHITEST":"0.577611987659069"},{"region":"Southeast","CHITEST":"0.374156016716296"},{"region":"Southwest","CHITEST":"0.0186392153050728"},{"region":"West","CHITEST":"0.725711264565586"}]}