SQL Server RunningTTEST Function

Updated 2023-11-14 15:43:20.743000

Description

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

Return Type

float

Remarks

If @Id is NULL then @Id = 0.

To calculate moving t-Test, use the MovingTTEST function.

To calculate the t-Test for 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

Examples

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

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 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.