MovingTTEST
Updated: 30 Jun 2013
Use MovingTTEST 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 for each value from the first value in the window to the last value in the window. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [westclintech].[wct].[MovingTTEST](
<@X, float,>
,<@Y, float,>
,<@TAILS, int,>
,<@TTYPE, int,>
,<@Offset, int,>
,<@RowNum, int,>
,<@Id, tinyint,>
,<@Exact, bit,>)
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.
@Offset
specifies the window size. @Offset 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 MovingTTEST calculation. @Id allows you to specify multiple moving 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.
@Exact
a bit value which tells the function whether or not to return a NULL value if the number of rows in the window is smaller the @Offset value. If @Exact is 'True' and the number of rows in the window is less the @Offset then a NULL is returned. @Exact is an expression of type bit or of a type that can be implicitly converted to bit.
Remarks
· If @Id is NULL then @Id = 0.
· To calculate the t-Test from the beginning of a dataset or a partition, use the RunningTTEST function.
· To calculate the t-Test for an entire data set or for an entire group within a data set use the TTEST function.
· If @RowNum is equal to 1, MovingTTEST is equal to zero
· @RowNum must be in ascending order.
· If @Exact IS NULL then @Exact = 'True'
· 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 over a window of 10 rows.
SELECT rn
,x
,y
,wct.MovingTTEST(
x --@X
,y --@Y
,1 --@TAILS
,1 --@TTYPE
,10 --@Offset
,ROW_NUMBER() OVER (ORDER by rn) --@RowNum
,NULL --@Id
,'True' --@Exact
) 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 NULL
3 110 72 NULL
4 110 101 NULL
5 96 133 NULL
6 101 97 NULL
7 99 110 NULL
8 96 112 NULL
9 96 101 NULL
10 126 97 0.299580396651529
11 98 102 0.250900153326152
12 105 107 0.471526926437087
13 108 52 0.390598624967712
14 126 152 0.445181389200508
15 72 148 0.324842150299173
16 114 60 0.496862608496906
17 111 70 0.351317444959803
18 100 69 0.23411642890432
19 118 100 0.185528301296675
20 84 66 0.20513375110675
In this example, we have 20 rows of data and we want to calculat the t-Test for x and z as well as y and z starting from the first with a window size of 10. Notive that each t-Test columns requires a different @Id. Also note that we have set @Exact to 'False'.
SELECT rn
,x
,y
,z
,wct.MovingTTEST(x, z,1,1,10,ROW_NUMBER() OVER (ORDER by rn),NULL,'False') as [PAIRED T TEST xz]
,wct.MovingTTEST(y, z,1,1,10,ROW_NUMBER() OVER (ORDER by rn),1,'False') 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.250900153326152 0.466511297089124
12 105 93 107 0.471526926437087 0.314516089286348
13 108 96 52 0.390598624967712 0.43303571802974
14 126 82 152 0.445181389200508 0.156716066016777
15 72 107 148 0.324842150299173 0.136051578624049
16 114 96 60 0.496862608496906 0.250331068591402
17 111 92 70 0.351317444959803 0.383258436236536
18 100 122 69 0.23411642890432 0.434966545194386
19 118 105 100 0.185528301296675 0.419710117770743
20 84 93 66 0.20513375110675 0.382556543757788