FTEST

Updated 2023-10-26 14:01:56.627000

Syntax

SELECT [westclintech].[wct].[FTEST] (
  <@expr1, nvarchar(4000),> 
 ,<@expr2, float,>)

Description

Use the aggregate function FTEST to return the result of an F-test. An F-test returns the two-tailed probability that the variance in dataset 1 and dataset 2 are not significantly different. Use this function to determine whether two samples have different variances.

F = \frac{\text{Explained Variance}}{\text{Unexplained variance}}

Arguments

@expr2

The value to be used in the F-Test calculation.

@expr1

The label associated with expr2.

Return Type

float

Remarks

If the number of rows in dataset 1 or dataset 2 is less than 2, FTEST will return an error

If the variance of dataset 1 or the variance of dataset 2 is zero, FTEST will return a divide by zero error.

FTEST is designed for normalized data.

If there are more than the 2 datasets in the group, FTEST will return a NULL.

FTEST is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

Examples

SELECT wct.FTEST(   label, --@GroupName

                    x      --@Value

                ) as FTEST

FROM

(

    VALUES

        ('d1', 6),

        ('d1', 7),

        ('d1', 9),

        ('d1', 15),

        ('d1', 21),

        ('d2', 20),

        ('d2', 28),

        ('d2', 31),

        ('d2', 38),

        ('d2', 40)

) n (label, x);

This produces the following result.

{"columns":[{"field":"FTEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"FTEST":"0.648317846786174"}]}

If the values are not normalized, we could try something like this.

SELECT wct.FTEST(label, x) as FTEST

FROM

(

    VALUES

        (6, 20),

        (7, 28),

        (9, 31),

        (15, 38),

        (21, 40)

) n (d1, d2)

    CROSS APPLY

(

    VALUES

        ('d1', d1),

        ('d2', d2)

) f (label, x);

This produces the following result

{"columns":[{"field":"FTEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"FTEST":"0.648317846786174"}]}

Here’s an example, where the 2 arrays are passed as in 2 rows in a spreadsheet format.

SELECT wct.FTEST(n.label, f.x) as FTEST

FROM

(

    VALUES

        ('d1', 6, 7, 9, 15, 21),

        ('d2', 20, 28, 31, 38, 40)

) n (label, x1, x2, x3, x4, x5)

    CROSS APPLY

(

    VALUES

        ('x1', x1),

        ('x2', x2),

        ('x3', x3),

        ('x4', x4),

        ('x5', x5)

) f (v, x);

This produces the following result.

{"columns":[{"field":"FTEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"FTEST":"0.648317846786174"}]}

In this example, we have two trials with 2 datasets each. We can use the GROUP BY syntax to do the F-test calculation on both trials simultaneously.

SELECT trial,

       wct.FTEST(dataset, val) as FTEST

FROM

(

    VALUES

        ('Trial 1', 'd1', 93.2923),

        ('Trial 1', 'd1', 101.7884),

        ('Trial 1', 'd1', 105.3499),

        ('Trial 1', 'd1', 101.7043),

        ('Trial 1', 'd1', 91.0345),

        ('Trial 1', 'd1', 74.2144),

        ('Trial 1', 'd1', 102.1458),

        ('Trial 1', 'd1', 88.6409),

        ('Trial 1', 'd1', 93.2016),

        ('Trial 1', 'd1', 115.4339),

        ('Trial 1', 'd2', 107.6562),

        ('Trial 1', 'd2', 74.2587),

        ('Trial 1', 'd2', 100.7526),

        ('Trial 1', 'd2', 91.3729),

        ('Trial 1', 'd2', 104.4295),

        ('Trial 1', 'd2', 91.3057),

        ('Trial 1', 'd2', 104.412),

        ('Trial 1', 'd2', 92.2344),

        ('Trial 1', 'd2', 108.6575),

        ('Trial 1', 'd2', 88.7054),

        ('Trial 2', 'd3', 109.5777),

        ('Trial 2', 'd3', 110.0512),

        ('Trial 2', 'd3', 82.1003),

        ('Trial 2', 'd3', 94.4187),

        ('Trial 2', 'd3', 96.8142),

        ('Trial 2', 'd3', 100.3303),

        ('Trial 2', 'd3', 100.7579),

        ('Trial 2', 'd3', 70.4202),

        ('Trial 2', 'd3', 99.1351),

        ('Trial 2', 'd3', 82.6996),

        ('Trial 2', 'd4', 89.3049),

        ('Trial 2', 'd4', 121.954),

        ('Trial 2', 'd4', 91.2342),

        ('Trial 2', 'd4', 77.2809),

        ('Trial 2', 'd4', 95.6451),

        ('Trial 2', 'd4', 85.728),

        ('Trial 2', 'd4', 87.8381),

        ('Trial 2', 'd4', 71.1317),

        ('Trial 2', 'd4', 121.6113),

        ('Trial 2', 'd4', 126.1566)

) n (trial, dataset, val)

GROUP BY Trial;

This produces the following result.

{"columns":[{"field":"trial","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"FTEST","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"trial":"Trial 1","FTEST":"0.903558917831079"},{"trial":"Trial 2","FTEST":"0.211079812120368"}]}