Function to Calculate Formula Without Using a For Loop in PL/SQL

Question

Still kinda new to PL/SQL but basically I'm trying to create a function that will calculate a person's score depending on how much money they've paid in the last 8 years. It uses the sums for each year for calculations. The formula is (Year-1)/Year-2) * (Year-2/Year-3) * (Year3/Year4) and so on. What makes it extra tricky is that I need to skip years where they gave 0.

For example:

Sample Formula Calculations

Here's the code I have so far:

CREATE OR REPLACE FUNCTION formula
(idnum IN NUMBER)
RETURN NUMBER IS score NUMBER; 

-- Declare Variables
currentyear NUMBER := EXTRACT (YEAR FROM SYSDATE);
previousyear NUMBER := currentyear - 1;
yeareight NUMBER := currentyear - 8;
previoussum NUMBER := 0;
currentsum NUMBER := 0;
placeholder NUMBER := 0;
score NUMBER := 1;

BEGIN
-- Set Score to 0 if no history of payments in the last 8 years
SELECT NVL(SUM(amount), 0)
   INTO currentsum
   FROM moneytable g
   WHERE g.id_number = idnum
         AND g.fiscal_year BETWEEN yeareight AND previousyear;    

IF currentsum = 0 THEN score := 0;
  ELSE 
-- Loop to calculate Score
-- Score formula is (Year-1/Year -2) * (Year-2/Year-3) and so on for the last 8 years
-- Zeroes ignored for above calculations  
-- Score defaults to 1 if only one year has any gifts
FOR counter IN 1..8
  LOOP

  currentyear := currentyear - 1;
  placeholder := 0;

  SELECT NVL(SUM(amount), 0)
  INTO currentsum
  FROM moneytable g
  WHERE g.id_number = idnum
        AND g.fiscal_year = currentyear;

 IF currentsum = 0 THEN CONTINUE;
 ELSE placeholder := previoussum / currentsum; END IF;

 previoussum := currentsum;

 IF currentsum > 0 AND placeholder > 0 THEN score := score * placeholder; END IF;

END LOOP; 
END IF;
RETURN score;

END;

It works and it gives the correct score but it runs super slow if I try running it more for than a few people at a time. Is there a more efficient, optimized way to create this function?

score 1 · Answer 1 · edited Dec 17 '19 at 08:59

First UNPIVOT to get rid of the empty years

select * from tab
  UNPIVOT (  value                             
            FOR year  IN                             
            (YEAR1, YEAR2, YEAR3, YEAR4, YEAR5, YEAR6, YEAR7, YEAR8) 
          )
where value != 0
order by 1,2;

NAME YEAR       VALUE
---- ----- ----------
Jane YEAR1         10
Jane YEAR3         20
Jane YEAR4         50
Jane YEAR7         30
Jane YEAR8         20
Rob  YEAR2         10
Rob  YEAR4         20
...

Then calculate the coefficient using LEAD aggregate function (use as default the same VALUE from the row to ignore the last coefficient - set it to one).

with formula as (select * from tab
  UNPIVOT (  value                             
            FOR year  IN                             
            (YEAR1, YEAR2, YEAR3, YEAR4, YEAR5, YEAR6, YEAR7, YEAR8) 
          )
where value != 0)
select NAME, YEAR, VALUE,
VALUE / lead(value,1,VALUE) over (partition by NAME order by YEAR) as koeff
from formula
order by 1,2;

NAME YEAR       VALUE      KOEFF
---- ----- ---------- ----------
Jane YEAR1         10 ,5        
Jane YEAR3         20 ,4        
Jane YEAR4         50 1,66666667
Jane YEAR7         30 1,5       
Jane YEAR8         20          1
Rob  YEAR2         10 ,5        
Rob  YEAR4         20 ,2 
...

In the last step calculate the aggregated multiplication of the coefficients using this trick

with formula as (select * from tab
  UNPIVOT (  value                             
            FOR year  IN                             
            (YEAR1, YEAR2, YEAR3, YEAR4, YEAR5, YEAR6, YEAR7, YEAR8) 
          )
where value != 0),
formula2 as (
select NAME, YEAR, VALUE,
VALUE / lead(value,1,VALUE) over (partition by NAME order by YEAR) as koeff
from formula)
select name, 
round(EXP(SUM(LN(koeff))),6) score
from formula2
group by name
order by 1 ;


NAME      SCORE
---- ----------
Jane ,5        
Rob  ,1        
Tom  ,2

Test Data

create table tab as
select 'Tom' name, 0 year1, 0 year2, 0 year3, 10 year4, 20 year5, 30 year6, 40 year7, 50 year8 
from dual union all
select 'Jane' name, 10 year1, 0 year2, 20 year3, 50 year4, 0 year5, 0 year6, 30 year7, 20 year8 
from dual union all
select 'Rob' name, 0 year1, 10 year2, 0 year3, 20 year4, 0 year5, 0 year6, 0 year7, 100 year8 
from dual;

score 0 · Answer 2 · answered Dec 16 '19 at 17:19

You want the first non-zero value divided by the last non-zero value. That would be:

select (case when year1 <> 0 then year1
             when year2 <> 0 then year2
             when year3 <> 0 then year3
             when year4 <> 0 then year4
             when year5 <> 0 then year5
             when year6 <> 0 then year6
             when year7 <> 0 then year7
             when year8 <> 0 then year8
        end) /
       (case when year8 <> 0 then year8
             when year7 <> 0 then year7
             when year6 <> 0 then year6
             when year5 <> 0 then year5
             when year4 <> 0 then year4
             when year3 <> 0 then year3
             when year2 <> 0 then year2
             when year1 <> 0 then year1
        end)

score 0 · Accepted Answer · answered Dec 16 '19 at 17:31

0

Perhaps is better to call one single query with the last 8 years and put the result on an array and so loop on it, without execute 8 query:

DECLARE
  TYPE arrayofnumbers IS TABLE OF NUMBER(11);
  sums arrayofnumbers;
BEGIN
  SELECT NVL(SUM(amount), 0)
  INTO sums
  FROM moneytable g
    WHERE g.id_number = idnum AND g.fiscal_year between currentyear and currentyear+7;

 FOR i IN 1 .. sums.count
  LOOP
    -- other code
    dbms_output.put_line(sums(i));

  END LOOP;
END;

answered Dec 16 '19 at 17:31

Max

1,020
7
13

I want to thank everyone for their input but I'm marking this one as the correct answer because it was the closest to what I needed. Thanks Max! I was getting a "collection types not allowed error" so I replaced "IS TABLE OF" with "IS ARRAY(8) OF" and a cursor and it worked perfectly! And it runs unbelievably faster than my original solution. – StarsTurnCold Dec 16 '19 at 20:36

score 0 · Answer 4 · answered Dec 16 '19 at 17:35

Query table only once and group result by years. Number them descending and loop through eight rows. It can be done in SQL or you can go through grouped rows in loop in your function. SQL example:

^dbfiddle

with y(rn, amt) as (
    select row_number() over (order by fiscal_year desc), sum(amount)
      from moneytable g  
      where id_number = 1
        and fiscal_year between extract (year from sysdate) - 8 
                            and extract(year from sysdate) - 1
      group by fiscal_year),
  c(rn, amt, prev, ret) as (
    select rn, amt, amt, 1 from y where rn = 1
    union all
    select y.rn, y.amt, c.amt, (c.amt/y.amt)*ret from c join y on y.rn = c.rn + 1)
select ret from c where rn = (select max(rn) from c)

Function to Calculate Formula Without Using a For Loop in PL/SQL

4 Answers4