TTEST_PAIRED
Updated 2023-10-26 18:38:10.363000
Syntax
SELECT [westclintech].[wct].[TTEST_PAIRED] (
<@X1, float,>
,<@X2, float,>
,<@Statistic, nvarchar(4000),>)
Description
Use the aggregate function TTEST_PAIRED to calculate a two-sample, paired t -test. Use a paired t -test when you have two related observations (i.e., two observations per subject). The TTEST_PAIRED can return any of the following values.
{
"columns": [
{
"field": "Statistic",
"width": 100
},
{
"field": "Description",
"width": 225
}
],
"rows": [
{
"Statistic": "P1",
"Description": "one-tailed probability"
},
{
"Statistic": "P2",
"Description": "two-tailed probability"
},
{
"Statistic": "T",
"Description": "t-statistic"
},
{
"Statistic": "DF",
"Description": "degrees of freedom"
},
{
"Statistic": "N1",
"Description": "number of observations (in the x group)"
},
{
"Statistic": "N2",
"Description": "number of observations (in the y group)"
},
{
"Statistic": "ES",
"Description": "effect size"
},
{
"Statistic": "SD",
"Description": "pooled variance"
},
{
"Statistic": "MD",
"Description": "mean difference"
},
{
"Statistic": "SE",
"Description": "standard error"
},
{
"Statistic": "LCL",
"Description": "lower confidence level"
},
{
"Statistic": "UCL",
"Description": "upper confidence level"
}
]
}
Arguments
@Statistic
identifies the statistic to be returned.
@X2
the x-values for sample number 2. @X2 is an expression of type float or of a type that can be implicitly converted to float.
@X1
the x-values for sample number 1. @X1 is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If there is only one row of paired data a NULL will be returned.
@Statistic must be invariant within a GROUP.
If either @X1 IS NULL or @X2 IS NULL, the row is not included in the aggregate.
TTEST_PAIRED is an aggregate function and follows the same conventions as all other aggregate function in SQL Server.
For independent samples with equal variances use TTEST_INDEP.
For independent samples with unequal variances use TTEST_INDEPU.
Effect Size (ES) is calculated as Cohen’s d.
N is the number of rows where @X1 IS NOT NULL and @X2 IS NOT NULL.
LCL and UCL are calculated with alpha = .05.
Examples
In this example we calculate the t -statistic for 25 pairs of observations about body weight before and after a diet.
SELECT wct.TTEST_PAIRED(before, after, 'T') as t_observed
FROM
(
VALUES
(1, 'M', 218, 196),
(2, 'F', 126, 139),
(3, 'M', 209, 188),
(4, 'F', 140, 140),
(5, 'M', 192, 173),
(6, 'F', 152, 137),
(7, 'F', 112, 123),
(8, 'M', 226, 203),
(9, 'M', 201, 181),
(10, 'M', 193, 174),
(11, 'F', 124, 136),
(12, 'F', 133, 141),
(13, 'M', 204, 184),
(14, 'M', 212, 191),
(15, 'F', 144, 134),
(16, 'M', 182, 164),
(17, 'M', 200, 180),
(18, 'M', 189, 170),
(19, 'F', 156, 140),
(20, 'M', 203, 183),
(21, 'M', 207, 186),
(22, 'M', 211, 190),
(23, 'F', 122, 124),
(24, 'M', 202, 182),
(25, 'F', 134, 147)
) n (id, s, before, after);
This produces the following result.
{"columns":[{"field":"t_observed","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"t_observed":"4.34248118673448"}]}
If we wanted to calculate the one-sided p-value, we would just P1 instead of T.
SELECT wct.TTEST_PAIRED(before, after, 'P1') as one_tailed_p_value
FROM
(
VALUES
(1, 'M', 218, 196),
(2, 'F', 126, 139),
(3, 'M', 209, 188),
(4, 'F', 140, 140),
(5, 'M', 192, 173),
(6, 'F', 152, 137),
(7, 'F', 112, 123),
(8, 'M', 226, 203),
(9, 'M', 201, 181),
(10, 'M', 193, 174),
(11, 'F', 124, 136),
(12, 'F', 133, 141),
(13, 'M', 204, 184),
(14, 'M', 212, 191),
(15, 'F', 144, 134),
(16, 'M', 182, 164),
(17, 'M', 200, 180),
(18, 'M', 189, 170),
(19, 'F', 156, 140),
(20, 'M', 203, 183),
(21, 'M', 207, 186),
(22, 'M', 211, 190),
(23, 'F', 122, 124),
(24, 'M', 202, 182),
(25, 'F', 134, 147)
) n (id, s, before, after);
This produces the following result.
{"columns":[{"field":"one_tailed_p_value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"one_tailed_p_value":"0.000110546467918379"}]}
Here we show all the values the can be returned by TTEST_PAIRED.
SELECT t.s,
t.descr,
wct.TTEST_PAIRED(before, after, t.s) as [Value]
FROM
(
VALUES
(1, 'M', 218, 196),
(2, 'F', 126, 139),
(3, 'M', 209, 188),
(4, 'F', 140, 140),
(5, 'M', 192, 173),
(6, 'F', 152, 137),
(7, 'F', 112, 123),
(8, 'M', 226, 203),
(9, 'M', 201, 181),
(10, 'M', 193, 174),
(11, 'F', 124, 136),
(12, 'F', 133, 141),
(13, 'M', 204, 184),
(14, 'M', 212, 191),
(15, 'F', 144, 134),
(16, 'M', 182, 164),
(17, 'M', 200, 180),
(18, 'M', 189, 170),
(19, 'F', 156, 140),
(20, 'M', 203, 183),
(21, 'M', 207, 186),
(22, 'M', 211, 190),
(23, 'F', 122, 124),
(24, 'M', 202, 182),
(25, 'F', 134, 147)
) n (id, s, before, after)
CROSS APPLY
(
VALUES
('P1', 'one_tailed_p_value'),
('P2', 'two_tailed_p_value'),
('T', 't_observed'),
('ES', 'effect_size'),
('N', 'num_observed'),
('DF', 'deg_freedom'),
('MD', 'mean_difference'),
('SDD', 'std_dev_diff'),
('SE', 'std_err_mean'),
('LCL', 'lower_ci'),
('UCL', 'upper_ci'),
('R', 'correlation')
) t (s, descr)
GROUP BY t.s,
t.descr;
This produces the following result.
{"columns":[{"field":"s"},{"field":"descr"},{"field":"Value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"s":"P1","descr":"one_tailed_p_value","Value":"0.000110546467918379"},{"s":"P2","descr":"two_tailed_p_value","Value":"0.000221092935836758"},{"s":"T","descr":"t_observed","Value":"4.34248118673448"},{"s":"ES","descr":"effect_size","Value":"0.868496237346895"},{"s":"N","descr":"num_observed","Value":"25"},{"s":"DF","descr":"deg_freedom","Value":"24"},{"s":"MD","descr":"mean_difference","Value":"11.44"},{"s":"SDD","descr":"std_dev_diff","Value":"13.1721929330946"},{"s":"SE","descr":"std_err_mean","Value":"2.63443858661892"},{"s":"LCL","descr":"lower_ci","Value":"6.00278599037985"},{"s":"UCL","descr":"upper_ci","Value":"16.8772140096201"},{"s":"R","descr":"correlation","Value":"0.97692313682967"}]}
Since TTEST_PAIRED is an aggregate function, we could have modified our SQL to produce a result for each value of s in the derived table, n.
SELECT *
FROM
(
SELECT n.s as gender,
t.s as statistic,
t.descr,
wct.TTEST_PAIRED(before, after, t.s) as [Value]
FROM
(
VALUES
(1, 'M', 218, 196),
(2, 'F', 126, 139),
(3, 'M', 209, 188),
(4, 'F', 140, 140),
(5, 'M', 192, 173),
(6, 'F', 152, 137),
(7, 'F', 112, 123),
(8, 'M', 226, 203),
(9, 'M', 201, 181),
(10, 'M', 193, 174),
(11, 'F', 124, 136),
(12, 'F', 133, 141),
(13, 'M', 204, 184),
(14, 'M', 212, 191),
(15, 'F', 144, 134),
(16, 'M', 182, 164),
(17, 'M', 200, 180),
(18, 'M', 189, 170),
(19, 'F', 156, 140),
(20, 'M', 203, 183),
(21, 'M', 207, 186),
(22, 'M', 211, 190),
(23, 'F', 122, 124),
(24, 'M', 202, 182),
(25, 'F', 134, 147)
) n (id, s, before, after)
CROSS APPLY
(
VALUES
('P1', 'one_tailed_p_value'),
('P2', 'two_tailed_p_value'),
('T', 't_observed'),
('ES', 'effect_size'),
('N', 'num_observed'),
('DF', 'deg_freedom'),
('MD', 'mean_difference'),
('SDD', 'std_dev_diff'),
('SE', 'std_err_mean'),
('LCL', 'lower_ci'),
('UCL', 'upper_ci'),
('R', 'correlation')
) t (s, descr)
GROUP BY n.s,
t.s,
t.descr
) p
PIVOT
(
MIN([Value])
FOR gender in ([M], [F])
) as d
ORDER BY 1;
This produces the following result.
{"columns":[{"field":"statistic"},{"field":"descr"},{"field":"M","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"F","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"statistic":"DF","descr":"deg_freedom","M":"14","F":"9"},{"statistic":"ES","descr":"effect_size","M":"15.8348061330513","F":"-0.154626901015141"},{"statistic":"LCL","descr":"lower_ci","M":"19.5578922229037","F":"-10.1274153610638"},{"statistic":"MD","descr":"mean_difference","M":"20.2666666666667","F":"-1.79999999999998"},{"statistic":"N","descr":"num_observed","M":"15","F":"10"},{"statistic":"P1","descr":"one_tailed_p_value","M":"1.01201526614611E-18","F":"0.318277706057421"},{"statistic":"P2","descr":"two_tailed_p_value","M":"2.02403053229221E-18","F":"0.636555412114841"},{"statistic":"R","descr":"correlation","M":"0.999804279842337","F":"0.545476196975635"},{"statistic":"SDD","descr":"std_dev_diff","M":"1.27988094684437","F":"11.6409239801277"},{"statistic":"SE","descr":"std_err_mean","M":"0.330463839483762","F":"3.68118338460761"},{"statistic":"T","descr":"t_observed","M":"61.3279404437097","F":"-0.488973194741248"},{"statistic":"UCL","descr":"upper_ci","M":"20.9754411104297","F":"6.52741536106385"}]}