Search code examples
sqlpostgresqlaggregate-functionspercentagemultiplication

Total percentage of a series of positive and negative percentages

Postgres 9.6.6, latest Ubuntu LTS.
I have a column with a daily grow (+-) percentages, like:

Trader_Id   Date    8_AM    8_PM    Growth%
    1       1/1     290     248     -14,48
    1       2/1     225     880     291,11
    1       3/1     732     512     -30,05
    1       4/1     621     602      -3,06
    1       5/1     314     314        0,0
    1       6/1       0       0        0,0
    1       7/1     294     95      -67,69

What is the correct query to sum and subtract a sequence of percentages to get the total percentage of growth(+-) of the selected trader?
In that case, select a Trader_Id, sort by Date ASC and calculate a total growth percentage from the first day available.
This is the sequence of manual calculations:

Growth%     Calculation                                     Result  
-14,48      1+(-14,48/100)                                  0,8552
291,11      0,8552+(291,11/100*0,8552)                      3,34477272
-30,05      3,34477272+(-30,05/100*3,34477272)              2,339668518
-3,06       2,33966851764+(-3,06/100*2,33966851764)         2,268074661
    0       2,26807466100022+(0/100*2,26807466100022)       2,268074661
    0       2,26807466100022+(0/100*2,26807466100022)       2,268074661
-67,69      2,26807466100022+(-67,69/100*2,26807466100022)  0,732814923
            (0,73281492296917-1)*100                        -26,7185077

The final expected result of SELECT SOMETHING(Growth% ORDER BY Date) is -26,72%

Figured out the correct formula to do that:

formula

Now the remaining problem is how to traduce in a correct SQL...

Solution

  • For lack of information assuming your column growth is type numeric and you want numeric precision for calculation as well.

    (Calculating with double precision is cheaper, but may increase rounding errors.)

    Create an aggregate function to generate a serial product once:

    CREATE AGGREGATE numeric_mul(numeric) (
   sfunc = numeric_mul,
   stype = numeric
);

    Then the query is as simple as:

    SELECT 100 * numeric_mul(growth *.01 + 1) - 100
FROM   tbl;

    db<>fiddle here

    The order of input rows has no bearing on the result.

    Related: