Home » SQL & PL/SQL » SQL & PL/SQL » reuse a computed value for each row in SQL
reuse a computed value for each row in SQL [message #388975] 
Thu, 26 February 2009 18:50 
ctvhtg
Messages: 6 Registered: February 2009 Location: Scotland

Junior Member 


I am attempting to reuse a previously computed value for each row of a result set in SQL.
The calculation is saying:
"Divide my current rate from the table by the last rate from the table then multiple by the previously computed value".
This calculation is the part of a real actuarial formula.
The PL/SQL anonymous block (below) produces the desired functionality.
There are some boundary conditions (i.e first row in the "partition" is initialised to 1).
The use of LAG allows the current and last rate to be available for each row.
The use of the PL/SQL variable allows the previous rate to be 'stored' and then retrieved for each row.
I would like to be able to perform the same functionality within SQL.
My current solution is to wrap this PL/SQL code within a function and select from it using a TABLE function.
This works fine, but I would like some advice on a pure SQL solution if it can be done.
Thanks in advance.
create table rates (year number(4), rate number(10,5));
begin
delete from rates;
insert into rates values (2000,0.99123);
insert into rates values (2001,0.98123);
insert into rates values (2002,0.97123);
insert into rates values (2003,0.86123);
insert into rates values (2004,0.85123);
insert into rates values (2005,0.84123);
insert into rates values (2006,0.73123);
insert into rates values (2007,0.72123);
insert into rates values (2008,0.71123);
insert into rates values (2009,0.50123);
end;
declare
ln_prev_rate rates.rate%type := 1;
ln_next_rate rates.rate%type := 1;
begin
for rec in (select year
, rate
, lag(rate) over (order by year) last_rate
, row_number() over (order by year) rnum
from rates
order by year)
loop
if rec.rnum = 1 then
ln_next_rate := 1;
ln_prev_rate := ln_next_rate;
else
ln_next_rate := ln_prev_rate * rec.rate / nvl(rec.last_rate,0);
end if;
dbms_output.put_line(to_char(rec.year)
'~'to_char(nvl(rec.rate,0),99999.99999)
'~'to_char(nvl(rec.last_rate,0),99999.99999)
'~'to_char(ln_prev_rate,99999.99999)
'~'to_char(ln_next_rate,99999.99999));
ln_prev_rate := ln_next_rate;
end loop;
end;
/




Re: reuse a computed value for each row in SQL [message #389090 is a reply to message #389083] 
Fri, 27 February 2009 04:28 
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...

Senior Member 


Actually, I was wrong  you can do it in pl/sql:select year,rate,last_rate,rnum
,cast (prod(rate/last_rate) over (order by year) as number(10,5))
from(
select year
,rate
,lag(rate) over (order by year) last_rate
,row_number() over (order by year) rnum
from rates)
order by year;
The trick is in the PROD function, which is a user defined aggregate that multiplies numbers in the same way that SUM adds them.
PROD:create or replace type num_prod_type as object
(
total number,
static function
ODCIAggregateInitialize(sctx IN OUT num_prod_type )
return number,
member function
ODCIAggregateIterate(self IN OUT num_prod_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN num_prod_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT num_prod_type,
ctx2 IN num_prod_type)
return number
);
/
create or replace type body num_prod_type
is
static function ODCIAggregateInitialize(sctx IN OUT num_prod_type)
return number
is
begin
sctx := num_prod_type( 1 );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT num_prod_type,
value IN varchar2 )
return number
is
begin
self.total := self.total * value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN num_prod_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT num_prod_type,
ctx2 IN num_prod_type)
return number
is
begin
self.total := greatest(self.total,ctx2.total);
return ODCIConst.Success;
end;
end;
/
CREATE or replace
FUNCTION prod(input varchar2 )RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING num_prod_type;
I'm quite proud of that.



Re: reuse a computed value for each row in SQL [message #389098 is a reply to message #389083] 
Fri, 27 February 2009 04:58 

Michel Cadot
Messages: 64444 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


I didn't see this topic.
Here's a MODEL solution:
SQL> select year, rate, cast(global_rate as number(10,5)) global_rate
2 from (select row_number() over (order by year) rn, year, rate from rates)
3 model
4 dimension by (rn)
5 measures (year, rate, 1 as global_rate)
6 rules (
7 global_rate[any] = nvl(global_rate[cv()1] * rate[cv()] / rate[cv()1], 1)
8 )
9 /
YEAR RATE GLOBAL_RATE
  
2000 .99123 1
2001 .98123 .98991
2002 .97123 .97982
2003 .86123 .86885
2004 .85123 .85876
2005 .84123 .84867
2006 .73123 .7377
2007 .72123 .72761
2008 .71123 .71752
2009 .50123 .50566
10 rows selected.
Nice solution, JRowbottom.
Regards
Michel
[Updated on: Fri, 27 February 2009 05:01] Report message to a moderator





Re: reuse a computed value for each row in SQL [message #389159 is a reply to message #389114] 
Fri, 27 February 2009 07:57 
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...

Senior Member 


The context switching will be a bit of a problem  I just ran the script below, which shows roughly a 5x slower performance by putting a function in SQL.
The real problem would be that the execution path that the optimizer decides to use would have a real impact whether your package actually got called in the right order.
I'd use either the wholy pl/sql solution, or the user defined aggregate



Re: reuse a computed value for each row in SQL [message #389260 is a reply to message #389159] 
Fri, 27 February 2009 18:48 

Barbara Boehmer
Messages: 8677 Registered: November 2002 Location: California, USA

Senior Member 


Here are a couple of pure SQL, no model, no PL/SQL, no function, no procedure solutions. They are two variations on the same theme.
SCOTT@orcl_11g> SELECT * FROM rates
2 /
YEAR RATE
 
2000 .99123
2001 .98123
2002 .97123
2003 .86123
2004 .85123
2005 .84123
2006 .73123
2007 .72123
2008 .71123
2009 .50123
10 rows selected.
SCOTT@orcl_11g> SELECT r1.year, r1.rate,
2 (SELECT CAST (NVL (EXP (SUM (LN (r2.rate_div_last_rate))), 1) AS NUMBER (10, 5))
3 FROM (SELECT year,
4 rate / LAG (rate) OVER (ORDER by year) rate_div_last_rate
5 FROM rates) r2
6 WHERE r2.year <= r1.year) global_rate
7 FROM rates r1
8 ORDER BY year
9 /
YEAR RATE GLOBAL_RATE
  
2000 .99123 1
2001 .98123 .98991
2002 .97123 .97982
2003 .86123 .86885
2004 .85123 .85876
2005 .84123 .84867
2006 .73123 .7377
2007 .72123 .72761
2008 .71123 .71752
2009 .50123 .50566
10 rows selected.
SCOTT@orcl_11g> SELECT r1.year, r1.rate,
2 CAST (NVL (EXP (SUM (LN (r2.rate_div_last_rate))), 1) AS NUMBER (10, 5)) global_rate
3 FROM rates r1,
4 (SELECT year,
5 rate / LAG (rate) OVER (ORDER by year) rate_div_last_rate
6 FROM rates) r2
7 WHERE r2.year <= r1.year
8 GROUP BY r1.year, r1.rate
9 ORDER BY year
10 /
YEAR RATE GLOBAL_RATE
  
2000 .99123 1
2001 .98123 .98991
2002 .97123 .97982
2003 .86123 .86885
2004 .85123 .85876
2005 .84123 .84867
2006 .73123 .7377
2007 .72123 .72761
2008 .71123 .71752
2009 .50123 .50566
10 rows selected.
SCOTT@orcl_11g>



Re: reuse a computed value for each row in SQL [message #389261 is a reply to message #389260] 
Fri, 27 February 2009 19:24 

Barbara Boehmer
Messages: 8677 Registered: November 2002 Location: California, USA

Senior Member 


Even better:
SCOTT@orcl_11g> SELECT year, rate,
2 CAST (NVL (EXP (SUM (ln_rate_div_lag_rate) OVER (ORDER BY year)), 1) AS NUMBER (10, 5)) global_rate
3 FROM (SELECT year, rate,
4 LN (rate / LAG (rate) OVER (ORDER by year)) ln_rate_div_lag_rate
5 FROM rates)
6 ORDER BY year
7 /
YEAR RATE GLOBAL_RATE
  
2000 .99123 1
2001 .98123 .98991
2002 .97123 .97982
2003 .86123 .86885
2004 .85123 .85876
2005 .84123 .84867
2006 .73123 .7377
2007 .72123 .72761
2008 .71123 .71752
2009 .50123 .50566
10 rows selected.
SCOTT@orcl_11g>
[Updated on: Fri, 27 February 2009 19:26] Report message to a moderator



Re: reuse a computed value for each row in SQL [message #389394 is a reply to message #388975] 
Sun, 01 March 2009 16:12 
ctvhtg
Messages: 6 Registered: February 2009 Location: Scotland

Junior Member 


I like the product trick.
I have extended the example to include a partition (age) within the result set.
SQL> create table agerates (age number(3), year number(4), rate number(10,5));
Table created.
SQL> insert into agerates
2 select a.age, b.year, b.rate
3 from (
4 select level age
5 from dual
6 connect by level<=3) a
7 cross join rates b;
30 rows created.
SQL>
SQL> SELECT age
2 ,year
3 ,rate
4 ,CAST (NVL (EXP (SUM (ln_rate_div_lag_rate) OVER (PARTITION BY age ORDER BY year)), 1) AS NUMBER (10, 5)) global_rate
5 FROM (SELECT age
6 ,year
7 ,rate
8 ,LN (rate / LAG (rate) OVER (PARTITION BY age ORDER by year)) ln_rate_div_lag_rate
9 FROM agerates)
10 ORDER BY age, year
11 ;
AGE YEAR RATE GLOBAL_RATE
   
1 2000 .99123 1
1 2001 .98123 .98991
1 2002 .97123 .97982
1 2003 .86123 .86885
1 2004 .85123 .85876
1 2005 .84123 .84867
1 2006 .73123 .7377
1 2007 .72123 .72761
1 2008 .71123 .71752
1 2009 .50123 .50566
2 2000 .99123 1
2 2001 .98123 .98991
2 2002 .97123 .97982
2 2003 .86123 .86885
2 2004 .85123 .85876
2 2005 .84123 .84867
2 2006 .73123 .7377
2 2007 .72123 .72761
2 2008 .71123 .71752
2 2009 .50123 .50566
3 2000 .99123 1
3 2001 .98123 .98991
3 2002 .97123 .97982
3 2003 .86123 .86885
3 2004 .85123 .85876
3 2005 .84123 .84867
3 2006 .73123 .7377
3 2007 .72123 .72761
3 2008 .71123 .71752
3 2009 .50123 .50566
30 rows selected.
SQL>




Goto Forum:
Current Time: Wed Feb 22 23:32:44 CST 2017
Total time taken to generate the page: 0.11054 seconds
