Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running TTEST function


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
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service