STDEV_S

Updated 2024-02-13 19:43:21.250000

Syntax

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

Description

Use the aggregate function STDEV_S to return the estimated standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the mean. STDEV_S is a numerically stable single-pass calculation of the sample standard deviation which will produce more reliable results under certain conditions than the built-in SQL Server function STDEV, without compromising performance.

The formula for the calculation of the sample standard deviation is:

\sqrt{\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 standard deviation for the entire population, then use the STDEV_P function.

To calculate the sample variance use the VAR_S function.

To calculate the population variance use the VAR_P function.

To calculate a running sample standard deviation use the RunningSTDEV function.

To calculate a moving sample standard deviation use the MovingSTDEV function.

Examples

SELECT wct.STDEV_S(x) as STDEV_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":"STDEV_S","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"STDEV_S":"2.63111609950729E-05"}]}

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

SELECT wct.STDEV_S(x) as XLeratorDB,
       STDEV(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":"2.63111609950729E-05","SQL Server":"0"}]}