Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server moving TTEST function


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
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service