SQL Sequence Weighted Sum and Weighted Average Functions

The seq_wsum(), seq_wavg() functions take two sequence arguments of type double and return a scalar result of type double. The weighted sum function seq_wsum() calculates the sum of the products of the two sequence elements. In fact the seq_wsum() is equivalent to: seq_sum( seq_mul()).

The weighted average function seq_wavg() calculates the average as the sum of the products of the two sequence elements divided by the sum of sequence 1. This is equivalent to the sequence returned by seq_wsum() divided by seq_sum() of sequence 1.

Following is an example script demonstrating the seq_wsum() and seq_wavg() functions:

 
    INSERT INTO SimpleSequence(testNumber,dVal1,dVal2)
    VALUES(5,'{2,3,4}','{1,2,4}');
     
    SELECT dVal1,dVal2,seq_wsum(dVal1,dVal2) AS "wsum" FROM SimpleSequence WHERE testNumber=5;
    SELECT dVal1,dVal2,seq_wavg(dVal1,dVal2) AS "wavg" FROM SimpleSequence WHERE testNumber=5;
     
    dVal1{}
    dVal2{}
    wsum
    -------------------------------------------------------------------------
    {2.000000, 3.000000, 4.000000}
    {1.000000, 2.000000, 4.000000}
    24.000000
            
     
    dVal1{}
    dVal2{}
    wavg
    -------------------------------------------------------------------------
    {2.000000, 3.000000, 4.000000}
    {1.000000, 2.000000, 4.000000}
    2.666667
            
         

The weighted sum for the dataset above is obtained as follows:

value	weight	product
2.00	1.00	2.00
3.00	2.00	6.00
4.00	4.00	16.00
wsum		24.00

The weighted average for the dataset above is obtained as follows:

value	weight	product
2.00	1.00	2.00
3.00	2.00	6.00
4.00	4.00	16.00
wsum	(= 2+6+16)	24.00
sum1	(= 2+3+4)	9.00
wavg	(= 24/9)	2.666667

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 3