Search code examples
sqloraclewindow-functions

Oracle SQL Cumulative Product

I have a somewhat similar question to: MySQL cumulative product group by but I'm unable to get the proposed solution to work for my situation.

I have 5 month over month percentage change values that I want to apply to a static value. I want the resulting value to be cumulative for all months.

Report_Month PCT_Change Initial_Value
8/1/2021 0.1202 0.88
9/1/2021 -.0426
10/1/2021 -0.0794
11/1/2021 0.0726
12/1/2021 0.1182

If I were calculating this in Excel the out put would look like:

Report_Month PCT_Change Value
8/1/2021 0.1202 0.985776
9/1/2021 -.0426 0.943781942
10/1/2021 -0.0794 0.868845656
11/1/2021 0.0726 0.931923851
12/1/2021 0.1182 1.04207725

However, when I use the exp(sum(ln(x))) method that's not what I'm getting. This is the output using exp(sum(ln(0.88*(1+pct_change))) over (order by report_month)).

Report_Month PCT_Change Value
8/1/2021 0.1202 0.9857
9/1/2021 -.0426 0.8305
10/1/2021 -0.0794 0.6728
11/1/2021 0.0726 0.6351
12/1/2021 0.1182 0.6249

Any assistance would be greatly appreciated.

Thanks, Chris

Solution

  • The value of Initial_Value must be multiplied to the result of the SUM() window function, so the correct expression is:

    0.88 * EXP(SUM(LN((1 + pct_change))) OVER (ORDER BY report_month))

    See the demo.