CODEWITHSUNDEEP
sqloracle-databasewindow-functions
Gerard
Gerard

Reputation: 27

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);

Upvotes: 0

Views: 68

Answers (2)

MT0
MT0

Reputation: 168281

There is not a PRODUCT() aggregate/analytic function in Oracle but you can get the same effect (with a few rounding errors) using EXP(SUM(LN(value))) and to get a cumulative product you can use the SUM( value ) OVER ( ORDER BY ... [RANGE|ROWS] BETWEEN ... ) windowed analytic function.

This demonstrates how to perform the calculation where the values are not the same in every row:

WITH test_data ( SEQ, FACTOR ) AS (
  SELECT  1, 0.99978 FROM DUAL UNION ALL
  SELECT  2, 0.99978 FROM DUAL UNION ALL
  SELECT  3, 0.99978 FROM DUAL UNION ALL
  SELECT  4, 0.99978 FROM DUAL UNION ALL
  SELECT  5, 0.99978 FROM DUAL UNION ALL
  SELECT  6, 0.99978 FROM DUAL UNION ALL
  SELECT  7, 0.99900 FROM DUAL UNION ALL
  SELECT  8, 0.99978 FROM DUAL UNION ALL
  SELECT  9, 0.99978 FROM DUAL UNION ALL
  SELECT 10, 0.99978 FROM DUAL
)
SELECT SEQ,
       FACTOR,
       EXP(
         COALESCE(
           SUM( LN( FACTOR ) ) OVER ( ORDER BY SEQ ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ),
           LN(1)
         )
       ) AS PERC
FROM   test_data;

Output:

SEQ | FACTOR |                                      PERC
--: | -----: | ----------------------------------------:
  1 | .99978 |                                         1
  2 | .99978 | .9997799999999999999999999999999999999907
  3 | .99978 | .9995600483999999999999999999999999999915
  4 | .99978 | .9993401451893519999999999999999999999783
  5 | .99978 | .9991202903574103425599999999999999999725
  6 | .99978 | .9989004838935317122846367999999999999711
  7 |   .999 | .9986807257870751353079341799039999999662
  8 | .99978 | .9976820450612880601726262457240959999696
  9 | .99978 | .9974625550113745767993882679500366988414
 10 | .99978 | .9972431132492720743924924025310876907504

db<>fiddle here

Upvotes: 1

DDS
DDS

Reputation: 2478

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

Upvotes: 2

Related Questions