RunningTTEST
Updated: 30 June 2013
Use RunningTTEST to calculate the Student’s t-Test of column values in an ordered resultant table, without the need for a self-join. The t-Test is calculated over all the values from the first value to the last value in the ordered group or partition. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [westclintech].[wct].[RunningTTEST](
<@X, float,>
,<@Y, float,>
,<@TAILS, int,>
,<@TTYPE, int,>
,<@RowNum, int,>
,<@Id, tinyint,>)
Arguments
@X
the x-value passed into the function. @X is an expression of type float or of a type that can be implicitly converted to float.
@Y
the y-value passed into the function. @Y is an expression of type float or of a type that can be implicitly converted to float.
@TAILS
specifies the number of distribution tails. If @Tails = 1 the one-tailed distribution is returned. If @Tails = 2 the two-tailed distribution is returned. @TAILS is an expression of type int or of a type that can be implicitly converted to int.
@TTYPE
is the kind of t-Test to perform. If @TTYPE =1, perform the paired test. If @TTYPE = 2, perform the two-sample equal variance test. If @TTYPE = 3, perform the two-sample unequal variance test. @TTYPE is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the t-Testis being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningTTEST calculation. @Id allows you to specify multiple running t-Test calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
· If @Id is NULL then @Id = 0.
· To calculate moving t-Test, use the MovingTTEST function.
· To calculate the t-Testfor an entire data set, use the TTEST function.
· If @RowNum is equal to 1, RunningTTEST is equal to zero.
· @RowNum must be in ascending order.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem
Example
In this example, we have 20 rows of data and we want to caculate the t-Test for x and y starting from the first row.
SELECT rn
,x
,y
,wct.RunningTTEST(
x --@X
,y --@Y
,1 --@TAILS
,1 --@TTYPE
,ROW_NUMBER() OVER (ORDER by rn) --@RowNum
,NULL --@Id
) as [PAIRED T TEST]
FROM (VALUES
(1,102,118),
(2,142,89),
(3,110,72),
(4,110,101),
(5,96,133),
(6,101,97),
(7,99,110),
(8,96,112),
(9,96,101),
(10,126,97),
(11,98,102),
(12,105,107),
(13,108,52),
(14,126,152),
(15,72,148),
(16,114,60),
(17,111,70),
(18,100,69),
(19,118,100),
(20,84,66)
)n(rn,x,y)
This produces the following result.
rn x y PAIRED T TEST
----------- ----------- ----------- ----------------------
1 102 118 NULL
2 142 89 0.343324287673918
3 110 72 0.177573653110776
4 110 101 0.1323245043316
5 96 133 0.300831927745503
6 101 97 0.279567054776969
7 99 110 0.32295200882193
8 96 112 0.392573746764456
9 96 101 0.413799013100269
10 126 97 0.299580396651529
11 98 102 0.314163559135612
12 105 107 0.321165965172453
13 108 52 0.178190609695489
14 126 152 0.256869936459987
15 72 148 0.488326684115475
16 114 60 0.367100505513088
17 111 70 0.275167932640836
18 100 69 0.215261044421339
19 118 100 0.183464999011208
20 84 66 0.154928215657014
In this example, we have 20 rows of data and we want to caclulate the t-Test for x and z as well as y and z starting from the first row. Notice that each t-Test column requires a different @Id.
SELECT rn
,x
,y
,z
,wct.RunningTTEST(x, z,1,1,ROW_NUMBER() OVER (ORDER by rn),NULL) as [PAIRED T TEST xz]
,wct.RunningTTEST(y, z,1,1,ROW_NUMBER() OVER (ORDER by rn),1) as [PAIRED T TEST yz]
FROM (VALUES
(1,102,106,118),
(2,142,99,89),
(3,110,99,72),
(4,110,119,101),
(5,96,106,133),
(6,101,95,97),
(7,99,90,110),
(8,96,110,112),
(9,96,101,101),
(10,126,111,97),
(11,98,79,102),
(12,105,93,107),
(13,108,96,52),
(14,126,82,152),
(15,72,107,148),
(16,114,96,60),
(17,111,92,70),
(18,100,122,69),
(19,118,105,100),
(20,84,93,66)
)n(rn,x,y,z)
This produces the following result.
rn x y z PAIRED T TEST xz PAIRED T TEST yz
----------- ----------- ----------- ----------- ---------------------- ----------------------
1 102 106 118 NULL NULL
2 142 99 89 0.343324287673918 0.471142061623696
3 110 99 72 0.177573653110776 0.268637501554641
4 110 119 101 0.1323245043316 0.143912633939745
5 96 106 133 0.300831927745503 0.381776828090856
6 101 95 97 0.279567054776969 0.39318719664846
7 99 90 110 0.32295200882193 0.456931716246789
8 96 110 112 0.392573746764456 0.441763479139861
9 96 101 101 0.413799013100269 0.441053495066801
10 126 111 97 0.299580396651529 0.456999645393713
11 98 79 102 0.314163559135612 0.389020734261484
12 105 93 107 0.321165965172453 0.307142004911739
13 108 96 52 0.178190609695489 0.433182651770177
14 126 82 152 0.256869936459987 0.295689725079382
15 72 107 148 0.488326684115475 0.193384809174959
16 114 96 60 0.367100505513088 0.302618971415815
17 111 92 70 0.275167932640836 0.371627955172775
18 100 122 69 0.215261044421339 0.46134448377815
19 118 105 100 0.183464999011208 0.446462433674427
20 84 93 66 0.154928215657014 0.370545416684929