SQL Server MovingTTEST Function

Updated 2023-11-13 21:52:23.700000

Description

Use the scalar function 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 [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.

Return Type

float

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.

Examples

In this example, we have 20 rows of data and we want to calculate 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.

In this example, we have 20 rows of data and we want to calculate the t-Test for x and z as well as y and z starting from the first with a window size of 10. Notice 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.