analytical functions

[seenumahesh22]

Hi,
I have a doubt regarding a calculation by using analytical functions.

((1+r1)+(1+r2)+(1+r3)+....(1+r n) )*1000

where r1,r2,r3 are the values of a column in my table.
I need to get the values like this

first row (1+r1)*1000
secondrow ((1+r1)+(1+r2))*1000
.
.
.
.
nth row ((1+r1)+(1+r2)+(1+r3)+....(1+r n) )*1000

suppose you take the table as below

FUNDCODE FUNDRETURNS TIMEKEY

F1 10 200
F1 20 300
F1 40 400
F1 20 500
F1 3 600
F1 60 700
F1 70 800
F1 85 900
F1 90 1000
F1 23 1001


after applying the query

 
select a.*,sum((fundreturns+1)*1000) over (partition by fundcode order by timekey) runningtotal from  fund_returns a
 
 

i got the output for the above calculation as
FUNDCODE FUNDRETURNS TIMEKEY RUNNINGTOTAL
F1 10 200 11000
F1 20 300 32000
F1 40 400 73000
F1 20 500 94000
F1 3 600 98000
F1 60 700 159000
F1 70 800 230000
F1 85 900 316000
F1 90 1000 407000
F1 23 1001 431000

Now I need to do a similar type but the calculation(multiplication instead of summation) is
(1+r1)*(1+r2)*(1+r3)*....(1+r n) *1000

Is it doable using analytical funtions ??? if so please throw some light on it.

Thanks in advance
Srinivasan M

[amitpatel66]

Hi Srinivasan,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES every time you post in this forum.

Thanks
MODERATOR

[amitpatel66]

I am not sure how you get the product using any analytical function but I have done using pipelined functions as shown below:

 
SQL> select * from xyz;
 
FC        FR        TK
-- --------- ---------
F1        10       100
F1        20       200
F1        30       300
F1        40       400
 
SQL> select text from user_source where name = 'RMUL';
 
TEXT
----------------------------------------------------------------------------------------------------
TYPE rmul IS TABLE OF INTEGER;
 
SQL> select text from user_source WHERE name = 'MULT';
 
TEXT
----------------------------------------------------------------------------------------------------
FUNCTION mult RETURN rmul PIPELINED IS
res NUMBER:= 1;
BEGIN
FOR I IN (SELECT fr+1 fn FROM xyz) LOOP
res:= res * I.fn * 1000;
PIPE ROW(res);
END LOOP;
return;
END;
 
9 rows selected.
 
SQL> select * FROM TABLE(mult);
 
COLUMN_VALUE
------------
       11000
   231000000
   7.161E+12
   2.936E+17
 
SQL> 
 
 

I hope this helps!!

[chritzuk]

Hi Srinivasn,

I think this will get you what you need (assumes fundreturns is always > 0):

SELECT   f.fundcode,
         f.fundreturns,
         f.timekey,
         EXP (SUM (LN (f.fundreturns + 1)) OVER (PARTITION BY fundcode ORDER BY timekey))
FROM     fund_returns f
ORDER BY fundcode,
         timekey

Chris