Logo
Getting started

VAR_S

Updated 2024-02-13 19:58:24.507000

Syntax

SELECT [westclintech].[wct].[VAR_S] (
  <@Known_x, float,>)

Description

Use the aggregate function VAR_S to estimate variance based on a sample, or use VAR_S to estimate the population variance from a sample of N elements with an unknown mean (the mean being calculated from the sample itself). VAR_S is a numerically stable single-pass calculation of the sample variance which will produce more reliable results under certain conditions than the built-in SQL Server function VAR, without compromising performance.

The formula for the calculation of the sample variance is:

\frac{1}{N - 1}\sum_{j=0}^{N-1}(x_j-\bar{x})^2

Arguments

@Known_x

the values to be used in the calculation. @Known_x must be of a type float or of a type that implicitly converts to float.

Return Type

float

Remarks

If you want measure the variance for the entire population, then use the VAR_P function.

To calculate the population standard deviation use the STDEV_P function.

To calculate the sample standard deviation use the STDEV_S function.

To calculate a running sample variance use the RunningVAR function.

To calculate a moving sample variance use the MovingVAR function.

Examples

SELECT wct.VAR_S(x) as VAR_S

FROM

(

    SELECT 30000.0000216303

    UNION ALL

    SELECT 30000.0000565854

    UNION ALL

    SELECT 30000.000038137

    UNION ALL

    SELECT 30000.0000495983

    UNION ALL

    SELECT 30000.0000185861

    UNION ALL

    SELECT 30000.0000863479

    UNION ALL

    SELECT 30000.0000776366

    UNION ALL

    SELECT 30000.0000637985

    UNION ALL

    SELECT 30000.0000939786

    UNION ALL

    SELECT 30000.000031191

    UNION ALL

    SELECT 30000.0000550457

    UNION ALL

    SELECT 30000.0000207558

    UNION ALL

    SELECT 30000.0000805531

    UNION ALL

    SELECT 30000.0000241287

) n(x);

This produces the following result

{"columns":[{"field":"VAR_S","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"VAR_S":"6.92277192908644E-10"}]}

In this example, using the same data, we compare the XLeratorDB value to the value returned by the native SQL Server function.

SELECT wct.VAR_S(x) as XLeratorDB,

       VAR(x) as [SQL Server]

FROM

(

    SELECT 30000.0000216303

    UNION ALL

    SELECT 30000.0000565854

    UNION ALL

    SELECT 30000.000038137

    UNION ALL

    SELECT 30000.0000495983

    UNION ALL

    SELECT 30000.0000185861

    UNION ALL

    SELECT 30000.0000863479

    UNION ALL

    SELECT 30000.0000776366

    UNION ALL

    SELECT 30000.0000637985

    UNION ALL

    SELECT 30000.0000939786

    UNION ALL

    SELECT 30000.000031191

    UNION ALL

    SELECT 30000.0000550457

    UNION ALL

    SELECT 30000.0000207558

    UNION ALL

    SELECT 30000.0000805531

    UNION ALL

    SELECT 30000.0000241287

) n(x);

This produces the following result.

{"columns":[{"field":"XLeratorDB","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SQL Server","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XLeratorDB":"6.92277192908644E-10","SQL Server":"0"}]}