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 Thu, 26 February 2009 18:50
 ctvhtg Messages: 6Registered: 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.

```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 #389083 is a reply to message #388975] Fri, 27 February 2009 04:08
 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member
To do truly iterative work like that, I think you need the Model clause.

Michel is the one here who best understands that - I've never got my head round it.

Over to you Michel
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: 5933Registered: 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: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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 #389114 is a reply to message #388975] Fri, 27 February 2009 05:47
 ctvhtg Messages: 6Registered: February 2009 Location: Scotland Junior Member

Thanks for both solutions. They offers some very interesting techniques.

This is an Oracle 9i database so MODEL is not available unfortunately . I will try the user aggregate function and run some benchmark tests. Out of interest, using a user defined function 'PROD' in this case, is there a significant overhead here. An alternative would be to call a package function and maintain the 'previous rate' in a package global variable as each row is fetched. This would mean context switching for every row. Is this in effect the same?

Regards.
Re: reuse a computed value for each row in SQL [message #389120 is a reply to message #389114] Fri, 27 February 2009 05:52
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Custom aggregate function is the most efficient way you can find (but model clause in 10g and up).

Regards
Michel
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: 5933Registered: 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: 8737Registered: 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: 8737Registered: 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: 6Registered: 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>
```
Re: reuse a computed value for each row in SQL [message #389462 is a reply to message #389261] Mon, 02 March 2009 03:32
 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member
Very elegant - and, given that it uses built in functions, almost certainly much better performing.
 Previous Topic: Connecting to scott schema Next Topic: Record type
Goto Forum:

Current Time: Fri Aug 18 05:58:36 CDT 2017

Total time taken to generate the page: 0.23138 seconds