postgresql - Calculative cumulative returns using SQL -
i generate user's "monthly_return" between 2 months using code below. how turn "monthly_return" cumulative "linked" return similar stackoverflow question linked below?
similar question: running cumulative return in sql
i tried:
exp(sum(log(1 + cumulative_return) on (order date)) - 1)
but error:
pg::wrongobjecttype: error: on specified, log not window function nor aggregate function line 3: exp(sum(log(1 + cumulative_return) on (order date)) - 1... ^ : select portfolio_id, exp(sum(log(1 + cumulative_return) on (order date)) - 1) (select date, portfolio_id, (value_cents * 0.01 - cash_flow_cents * 0.01) / (lag(value_cents * 0.01, 1) on ( order portfolio_id, date)) - 1 cumulative_return portfolio_balances portfolio_id = 16 order portfolio_id, date) return_data;
the input data be:
1/1/2017: $100 value, $100 cash flow 1/2/2017: $100 value, $0 cash flow 1/3/2017: $100 value, $0 cash flow 1/4/2017: $200 value, $100 cash flow
the output be:
1/1/2017: 0% cumulative return 1/2/2017: 0% cumulative return 1/3/2017: 0% cumulative return 1/4/2017: 0% cumulative return
my current code shows monthly returns not linked (cumulative).
select date, portfolio_id, (value_cents * 0.01 - cash_flow_cents * 0.01) / (lag(value_cents * 0.01, 1) on ( order portfolio_id, date)) - 1 monthly_return portfolio_balances portfolio_id = 16 order portfolio_id, date;
if want cumulative sum:
select p.*, sum(monthly_return) on (partition portfolio_id order date) running_monthly_return (select date, portfolio_id, (value_cents * 0.01 - cash_flow_cents * 0.01) / (lag(value_cents * 0.01, 1) on ( order portfolio_id, date)) - 1 monthly_return portfolio_balances portfolio_id = 16 ) p order portfolio_id, date;
i don't see makes sense, because have cumulative sum of ratio, appears asking for.
Comments
Post a Comment