Clash Royale CLAN TAG#URR8PPP

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%

SELECT SOMETHING(Growth% ORDER BY Date)

Figured out the correct formula to do that:

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

CREATE TABLE

2 Answers
2

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

growth

numeric

numeric

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

double precision

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:

I believe you have a column referring to the day and you would like to know the sum of percentages for the current week.

You could do

SELECT SUM(day_percent)
FROM t
WHERE calendar_day BETWEEN
date_trunc('week',calendar_day) AND
date_trunc('week',calendar_day) + INTERVAL '7 DAYS';


Demo

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.