Forum Stats

  • 3,827,706 Users
  • 2,260,808 Discussions
  • 7,897,355 Comments

Discussions

Query challenge

Rob van Wijk
Rob van Wijk Member Posts: 5,856 Bronze Trophy
edited Jan 26, 2011 3:54AM in SQL & PL/SQL
Dear forum members,

I'm working on a piece about the model clause in which I'm comparing solutions for problems written with the model clause and solutions written without it.

For the next query I have a performant model clause solution, but I could not find a functionally equivalent solution without using the model clause and without using PL/SQL.

Maybe it is just not possible, but maybe some bright mind here has a solution.

Here are the tables:
create table deposits
as
select 1 customer, 1000 amount, date '2003-01-01' deposit_date from dual union all
select 1, 200, date '2004-01-01' from dual union all
select 1, 500, date '2005-01-01' from dual union all
select 1, 100, date '2006-01-01' from dual union all
select 1, 800, date '2007-01-01' from dual union all
select 2, 20, date '2003-01-01' from dual union all
select 2, 150, date '2004-01-01' from dual union all
select 2, 60, date '2005-01-01' from dual union all
select 2, 100, date '2006-01-01' from dual union all
select 2, 100, date '2007-01-01' from dual
/

create table interest_rates
as
select date '2003-01-01' startdate, 5 percentage from dual union all
select date '2004-01-01', 3.2 from dual union all
select date '2005-01-01', 4.1 from dual union all
select date '2006-01-01', 5.8 from dual union all
select date '2007-01-01', 4.9 from dual
/
The expected result is:
CUSTOMER AMOUNT STARTDATE  PERCENTAGE BALANCE_AT_END_OF_YEAR
-------- ------ ---------- ---------- ----------------------
1 1000 01-01-2003 5 1050
1 200 01-01-2004 3,2 1290
1 500 01-01-2005 4,1 1863,39
1 100 01-01-2006 5,8 2077,27
1 800 01-01-2007 4,9 3018,26
2 20 01-01-2003 5 21
2 150 01-01-2004 3,2 176,47
2 60 01-01-2005 4,1 246,17
2 100 01-01-2006 5,8 366,25
2 100 01-01-2007 4,9 489,1
The calculation for the balance at the end of the year is done like this:

1050: 1000 * 1.05
1290: ((1000*1.05) + 200) * 1.032
1863.39: (((1000*1.05) + 200) * 1.032) + 500) * 1.041
et cetera.

Good luck!

Regards,
Rob.

Message was edited by:
Rob van Wijk

Changed a customer number from 3 to 2 in the create table statement.
«1

Comments

  • 572471
    572471 Member Posts: 984 Green Ribbon
    edited May 5, 2007 10:47AM
    Rob, this query is only for your data.
    If you have interest start_dates or dates of deposit replenishments in the middle of the year,
    then you should make some changes to the query.
    SQL> select customer, amount, deposit_date, percentage,
    2 round(prod * sum(temp_amount) over(partition by customer order by
    3 deposit_date Rows unbounded preceding),
    4 2) balance_at_end_of_year
    5 from (select t.*,
    6 amount / lag(prod, 1, 1) over(partition by customer order by deposit_date) temp_amount
    7 from (select d.*,
    8 i.*,
    9 exp(sum(ln(1 + i.percentage / 100))
    10 over(partition by customer order by d.deposit_date)) prod
    11 from deposits d, interest_rates i
    12 where d.deposit_date = i.startdate) t)
    13 /

    CUSTOMER AMOUNT DEPOSIT_DATE PERCENTAGE BALANCE_AT_END_OF_YEAR
    ---------- ---------- ------------ ---------- ----------------------
    1 1000 01.01.2003 5 1050
    1 200 01.01.2004 3,2 1290
    1 500 01.01.2005 4,1 1863,39
    1 100 01.01.2006 5,8 2077,27
    1 800 01.01.2007 4,9 3018,25
    2 20 01.01.2003 5 21
    2 150 01.01.2004 3,2 176,47
    2 60 01.01.2005 4,1 246,17
    2 100 01.01.2006 5,8 366,25
    2 100 01.01.2007 4,9 489,09

    10 rows selected
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Hi Jens,

    I know about custom aggregate functions, but I don't see how these can be applied in this case.

    Regards,
    Rob.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Volder,

    Thanks! This is unbelievably inventive! Why couldn't I think of it ;-)

    It took me some minutes before I knew what was going on...

    There is only a very tiny problem, that you've probably seen yourself: the rounding. The intended behaviour of this was that every balance every year was rounded, but your solution doesn't round the number until the very end.

    But apart from that, it is just plain excellent.

    Regards,
    Rob.
  • 572471
    572471 Member Posts: 984 Green Ribbon
    edited May 5, 2007 1:36PM
    There is only a very tiny problem, that you've
    probably seen yourself: the rounding. The intended
    behaviour of this was that every balance every year
    was rounded, but your solution doesn't round the
    number until the very end.
    Yep, I think such kind of rounding is impossible in SQL without using model.

    PS. Can you provide how did you resolve it with model clause?

    My variant is:
    select c,a,dd,b from deposits 
    model
    reference r
    on (select * from interest_rates)
    dimension by (startdate)
    measures (percentage p)
    main m
    partition by (customer c)
    dimension by(row_number() over (partition by customer order by deposit_date) rn)
    measures(amount a, deposit_date dd, 0 b)
    rules (b[any] order by rn = round((nvl(b[CV()-1],0)+a[CV()])*(1+r.p[dd[CV()]]/100),2))
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited May 5, 2007 1:52PM
    Mine was almost the same. I just used a regular join instead of a reference model:
    SQL> select customer
    2 , amount
    3 , startdate
    4 , percentage
    5 , balance balance_at_end_of_year
    6 from deposits s
    7 , interest_rates r
    8 where s.deposit_date = r.startdate
    9 model
    10 partition by (s.customer)
    11 dimension by (r.startdate)
    12 measures (s.amount, r.percentage, 0 balance)
    13 rules
    14 ( balance[any] order by startdate
    15 = round
    16 ( (nvl(balance[add_months(cv(),-12)],0) + amount[cv()])
    17 * (1 + percentage[cv()]/100)
    18 , 2
    19 )
    20 )
    21 order by customer
    22 , startdate
    23 /

    CUSTOMER AMOUNT STARTDATE PERCENTAGE BALANCE_AT_END_OF_YEAR
    ---------- ---------- ------------------- ---------- ----------------------
    1 1000 01-01-2003 00:00:00 5 1050
    1 200 01-01-2004 00:00:00 3,2 1290
    1 500 01-01-2005 00:00:00 4,1 1863,39
    1 100 01-01-2006 00:00:00 5,8 2077,27
    1 800 01-01-2007 00:00:00 4,9 3018,26
    2 20 01-01-2003 00:00:00 5 21
    2 150 01-01-2004 00:00:00 3,2 176,47
    2 60 01-01-2005 00:00:00 4,1 246,17
    2 100 01-01-2006 00:00:00 5,8 366,25
    2 100 01-01-2007 00:00:00 4,9 489,1

    10 rijen zijn geselecteerd.
    Regards,
    Rob.

    Message was edited by:
    Rob van Wijk

    ... and I skipped the row_number() dimension and just used the startdate
  • 572471
    572471 Member Posts: 984 Green Ribbon
    edited May 5, 2007 4:22PM
    Mine was almost the same. I just used a regular join
    instead of a reference model:
    yeah, it's more appropriate here to use join at the root level, because model clause have a resrtiction:
    -The query block on which the reference model is defined cannot be correlated to an outer query.
    So if you need to get any data into the resultset - you can't get it out of the reference model data. And "perecentage" is that kind of data.
    d I skipped the row_number() dimension and just used
    the startdate
    That's better in that query, because you know that all the dates are the first dates of the years and they are following consequently.

    PS
    And have you already compared two possible versions (analytic and model) in perfomance?
    Just interesting.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    PS
    And have you already compared two possible versions
    (analytic and model) in perfomance?
    Just interesting.
    Not yet, but there won't be much difference in I/O, because both are using the tables only once and do a join. My guess is that the non-model clause solution will be more cpu intensive. The biggest difference here in my opinion though is readability, maintainability in favour of the model clause solution.

    I'll let you, and the other readers of this thread of course, know when I finish the performance comparison.

    Regards,
    Rob.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Here is the result of the performance comparison. Quite a huge difference in cpu time, more than I expected:
    ********************************************************************************

    select customer
    , amount
    , deposit_date
    , percentage
    , balance balance_at_end_of_year
    from deposits s
    , interest_rates r
    where s.deposit_date = r.startdate
    model
    partition by (s.customer)
    dimension by (s.deposit_date)
    measures (s.amount, r.percentage, 0 balance)
    rules
    ( balance[any] order by deposit_date
    = round
    ( (nvl(balance[add_months(cv(),-12)],0) + amount[cv()])
    * (1 + percentage[cv()]/100)
    , 2
    )
    )
    order by customer
    , deposit_date

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.01 0.12 0 48 192 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 6668 1.87 1.85 0 313 0 100000
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 6670 1.89 1.98 0 361 192 100000

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 61

    Rows Row Source Operation
    ------- ---------------------------------------------------
    100000 SORT ORDER BY (cr=313 pr=0 pw=0 time=1938195 us)
    100000 SQL MODEL ORDERED (cr=313 pr=0 pw=0 time=734355 us)
    100000 HASH JOIN (cr=313 pr=0 pw=0 time=1600739 us)
    5 TABLE ACCESS FULL INTEREST_RATES (cr=3 pr=0 pw=0 time=79 us)
    100000 TABLE ACCESS FULL DEPOSITS (cr=310 pr=0 pw=0 time=400026 us)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 6668 0.00 0.01
    SQL*Net message from client 6668 0.00 1.66
    ********************************************************************************

    select customer
    , amount
    , deposit_date
    , percentage
    , round
    ( prod * sum(temp_amount) over (partition by customer order by deposit_date)
    , 2
    ) balance_at_end_of_year
    from ( select t.*
    , amount / lag(prod, 1, 1) over(partition by customer order by deposit_date) temp_amount
    from ( select d.*
    , i.*
    , exp(sum(ln(1 + i.percentage / 100)) over (partition by customer order by d.deposit_date)) prod
    from deposits d
    , interest_rates i
    where d.deposit_date = i.startdate
    ) t
    )

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 6668 7.78 7.77 0 313 0 100000
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 6670 7.78 7.77 0 313 0 100000

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 61

    Rows Row Source Operation
    ------- ---------------------------------------------------
    100000 WINDOW BUFFER (cr=313 pr=0 pw=0 time=7834909 us)
    100000 VIEW (cr=313 pr=0 pw=0 time=8014180 us)
    100000 WINDOW BUFFER (cr=313 pr=0 pw=0 time=7314167 us)
    100000 VIEW (cr=313 pr=0 pw=0 time=7639359 us)
    100000 WINDOW SORT (cr=313 pr=0 pw=0 time=3939355 us)
    100000 HASH JOIN (cr=313 pr=0 pw=0 time=1600501 us)
    5 TABLE ACCESS FULL INTEREST_RATES (cr=3 pr=0 pw=0 time=74 us)
    100000 TABLE ACCESS FULL DEPOSITS (cr=310 pr=0 pw=0 time=300025 us)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 6668 0.00 0.01
    SQL*Net message from client 6668 0.00 1.67



    ********************************************************************************
    Regards,
    Rob.
  • Jens Petersen
    Jens Petersen Member Posts: 4,730 Bronze Badge
    I know it's cheated (it was a bad idea suggesting custom aggregates anyway) but just for fun
    CREATE OR REPLACE TYPE CompoundInterest
    AS OBJECT (
    
    runningSum NUMBER,
    
    STATIC FUNCTION ODCIAggregateInitialize
      ( actx IN OUT CompoundInterest
      ) RETURN NUMBER,
    
    MEMBER FUNCTION ODCIAggregateIterate
      ( self  IN OUT CompoundInterest,
        val   IN     varchar2
      ) RETURN NUMBER,
    
    MEMBER FUNCTION ODCIAggregateTerminate
      ( self         IN  CompoundInterest,
        returnValue  OUT number,
        flags        IN  NUMBER
      ) RETURN NUMBER,
    
    MEMBER FUNCTION ODCIAggregateMerge
      (self IN OUT CompoundInterest,
       ctx2 IN     CompoundInterest
      ) RETURN NUMBER
    
    );
    /
    CREATE OR REPLACE TYPE BODY CompoundInterest AS
    
    STATIC FUNCTION ODCIAggregateInitialize
      ( actx IN OUT CompoundInterest
      ) RETURN NUMBER IS 
      BEGIN
        IF actx IS NULL THEN
          dbms_output.put_line('NULL INIT');
          actx := CompoundInterest (0);
        ELSE
          dbms_output.put_line('NON-NULL INIT');
          actx.runningSum := 0;
        END IF;
        RETURN ODCIConst.Success;
      END;
    
    MEMBER FUNCTION ODCIAggregateIterate
      ( self  IN OUT CompoundInterest,
        val   IN     varchar2
      ) RETURN NUMBER IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Iterate ' || TO_CHAR(val));
        IF val IS NULL THEN 
            /* Will never happen */
            DBMS_OUTPUT.PUT_LINE('Null on iterate');
        END IF;
        self.runningSum := round ((self.runningSum + nvl(substr(val, 1, instr(val, '!')-1),0) ) * (1+nvl(substr(val, instr(val, '!')+1),0)/100), 2);
        RETURN ODCIConst.Success;
      END;
    
    MEMBER FUNCTION ODCIAggregateTerminate
      ( self        IN  CompoundInterest,
        ReturnValue OUT number,
        flags       IN  NUMBER
      ) RETURN NUMBER IS
      BEGIN
        dbms_output.put_line('Terminate ' || to_char(flags) || to_char(self.runningsum));
        returnValue := self.runningSum;
        RETURN ODCIConst.Success;
      END;
    
    MEMBER FUNCTION ODCIAggregateMerge
      (self IN OUT CompoundInterest,
       ctx2 IN     CompoundInterest
      ) RETURN NUMBER IS
      BEGIN
        self.runningSum := self.runningSum + ctx2.runningSum;
        RETURN ODCIConst.Success;
      END;
    
    END;
    /
    CREATE OR REPLACE FUNCTION Compound_Interest
      ( x varchar2
      ) RETURN number
        AGGREGATE USING CompoundInterest;
    /
    SQL> select customer, amount, startdate, percentage,
      2         (select Compound_Interest(d1.amount||'!'||r1.percentage)
      3          from deposits d1, interest_rates r1
      4          where r1.startdate=d1.deposit_date
      5          and d1.deposit_date <= d2.deposit_date
      6          and d1.customer = d2.customer) compound_interest
      7  from deposits d2, interest_rates
      8  where startdate=deposit_date
      9  order by customer, startdate
     10  ;
    
      CUSTOMER     AMOUNT STARTDATE PERCENTAGE COMPOUND_INTEREST
    ---------- ---------- --------- ---------- -----------------
             1       1000 01-JAN-03          5              1050
             1        200 01-JAN-04        3.2              1290
             1        500 01-JAN-05        4.1           1863.39
             1        100 01-JAN-06        5.8           2077.27
             1        800 01-JAN-07        4.9           3018.26
             2         20 01-JAN-03          5                21
             2        150 01-JAN-04        3.2            176.47
             2         60 01-JAN-05        4.1            246.17
             2        100 01-JAN-06        5.8            366.25
             2        100 01-JAN-07        4.9             489.1
    
    10 rows selected.
This discussion has been closed.