SQL Sequence Grand Aggregate Var and Dev Functions

The seq_var() and seq_dev() functions compute the variance and standard deviation for the input sequence, and return a double value. The variance is always a positive number; the smaller the value the closer the values of the sequence elements are to the mean. The standard deviation returned by seq_dev() is the square root of the variance.

Following is an example script demonstrating the seq_var() and seq_dev() functions:

 
 
    INSERT INTO SimpleSequence(testNumber,fVal1)
    VALUES(4,'{2,3,5,7}');
 
    SELECT fVal1, seq_var(fVal1) AS "var", seq_dev(fVal1) AS "dev"
    FROM SimpleSequence WHERE testNumber = 4;
 
    fVal1{}
    var
    dev
    ------------------------------------------------------------------------
    {2.000000, 3.000000, 5.000000, 7.000000}
    3.687500
    1.920286
     

The variance and standard deviation calculation for Grand_Agg, Group_Agg, Grid_Agg and Window_Agg functions implement the “population variance” method and can be verified using the excel VAR.P() and STDEV.P() functions (see http://www.excelfunctions.net/variance-in-excel.html for an explanation of these two statistical analysis methods).

Sample script

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    f 1