Search code examples
sqloraclewindow-functions

Oracle rolling LEAD calculation

Is it possible in Oracle to have a rolling lead calculation? I have a table where the first row is set to 100%. Every row after that equals the previous percentage multiplied by a factor, in this example o.99978. I can only get answer for first row without rolling all the way down. seq 3 should equal 0.99956 and so on.

SELECT SEQ, 
(CASE WHEN SEQ = 1 THEN PERC LAG(PERC) OVER (ORDER BY SEQ) * FACTOR END) 
PERC
FROM 
(SELECT 1 SEQ, 0.99978 FACTOR, 1 PERC FROM DUAL UNION ALL
SELECT 2 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 3 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 4 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 5 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 6 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 7 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 8 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 9 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL UNION ALL
SELECT 10 SEQ, 0.99978 FACTOR, NULL PERC FROM DUAL);
Solution

  • Just do this:

    select seq, factor, power(factor,seq-1) as perc
from my_table

    where your table is like the one in the picture with only first two columns

    here an example on dbfiddle