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 Go to next message
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 #389083 is a reply to message #388975] Fri, 27 February 2009 04:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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. Wink

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 Go to previous messageGo to next message
ctvhtg
Messages: 6
Registered: February 2009
Location: Scotland
Junior Member
Cool
Thanks for both solutions. They offers some very interesting techniques.

This is an Oracle 9i database so MODEL is not available unfortunately Sad . 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
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>
Re: reuse a computed value for each row in SQL [message #389462 is a reply to message #389261] Mon, 02 March 2009 03:32 Go to previous message
JRowbottom
Messages: 5933
Registered: 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: Sat Dec 03 20:40:22 CST 2016

Total time taken to generate the page: 0.04524 seconds