APEX

How about a custom aggregate function?

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

Hi Jens,

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

Regards,
Rob.

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.

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))

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

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.
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.

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.

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.

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.

Hi Jens,

This is also an amazing solution, although as mentioned a bit cheated.

I started this thread thinking: "I know a bit of SQL and since I cannot come up with a non model clause solution I truly wonder if it is possible at all". And here I have two other solutions! You've got to love this forum ...

Thanks!

Regards,
Rob.

I feel a XML solution was still missing ;)
Let's keep this forum rock:

SQL> SELECT VALUE(t).EXTRACT('ROW/CUSTOMER/text()').getnumberval() customer,
  2         VALUE(t).EXTRACT('ROW/AMOUNT/text()').getnumberval() amount,
  3         TO_DATE(VALUE(t).EXTRACT('ROW/STARTDATE/text()').getstringval(),'DD-MON-RR') startdate,
  4         VALUE(t).EXTRACT('ROW/PERCENTAGE/text()').getnumberval() percentage,
  5         ROUND(VALUE(t).EXTRACT('ROW/BALANCE_AT_END_OF_YEAR/text()').getnumberval(),2) balance_at_end_of_year
  6    FROM XMLTABLE ('declare function local:baeoy($cust)
  7                    {
  8                      if($cust[1]) then
  9                        $cust[count($cust)]/PERCENTAGE * ($cust[count($cust)]/AMOUNT + local:baeoy($cust[position() <= count($cust) - 1]))
 10                      else (0)
 11                    }; (: end of baeoy :)
 12                    for $c in distinct-values(/ROWSET/ROW/CUSTOMER)
 13                    let $cust := /ROWSET/ROW[CUSTOMER = $c]
 14                    for $i at $row in $cust
 15                    return <ROW>
 16                           {$i/CUSTOMER}
 17                           {$i/AMOUNT}
 18                           {$i/STARTDATE}
 19                           {$i/PERCENTAGE}
 20                           <BALANCE_AT_END_OF_YEAR> {local:baeoy($cust[position() <= $row])} </BALANCE_AT_END_OF_YEAR>
 21                            </ROW>' PASSING XMLTYPE(CURSOR(SELECT customer, amount, startdate, (1 + percentage / 100) percentage, 0 balance_at_end_of_year
 22                                                            FROM deposits, interest_rates
 23                                                           WHERE startdate = deposit_date
 24                                                           ORDER BY customer, startdate))) t
 25  /

  CUSTOMER     AMOUNT STARTDATE PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- --------- ---------- ----------------------
         1       1000 01-JAN-03       1.05                   1050
         1        200 01-JAN-04      1.032                   1290
         1        500 01-JAN-05      1.041                1863.39
         1        100 01-JAN-06      1.058                2077.27
         1        800 01-JAN-07      1.049                3018.25
         2         20 01-JAN-03       1.05                     21
         2        150 01-JAN-04      1.032                 176.47
         2         60 01-JAN-05      1.041                 246.17
         2        100 01-JAN-06      1.058                 366.25
         2        100 01-JAN-07      1.049                 489.09

10 rows selected.

Regards,
Michael

Let's keep this forum rock:

It sure does this way: a third amazing solution!

Although I don't understand it (yet) and I get a ORA-00904 on my local 10.2.0.1.0 database:

SQL> SELECT VALUE(t).EXTRACT('ROW/CUSTOMER/text()').getnumberval() customer,
  2         VALUE(t).EXTRACT('ROW/AMOUNT/text()').getnumberval() amount,
  3         TO_DATE(VALUE(t).EXTRACT('ROW/STARTDATE/text()').getstringval(),'DD-MON-RR') startdate,
  4         VALUE(t).EXTRACT('ROW/PERCENTAGE/text()').getnumberval() percentage,
  5         ROUND(VALUE(t).EXTRACT('ROW/BALANCE_AT_END_OF_YEAR/text()').getnumberval(),2) balance_at_end_of_year
  6    FROM XMLTABLE ('declare function local:baeoy($cust)
  7                    {
  8                      if($cust[1]) then
  9                        $cust[count($cust)]/PERCENTAGE * ($cust[count($cust)]/AMOUNT + local:baeoy($cust[position() <= count($cust)
- 1]))
 10                      else (0)
 11                    }; (: end of baeoy :)
 12                    for $c in distinct-values(/ROWSET/ROW/CUSTOMER)
 13                    let $cust := /ROWSET/ROW[CUSTOMER = $c]
 14                    for $i at $row in $cust
 15                    return <ROW>
 16                           {$i/CUSTOMER}
 17                           {$i/AMOUNT}
 18                           {$i/STARTDATE}
 19                           {$i/PERCENTAGE}
 20                           <BALANCE_AT_END_OF_YEAR> {local:baeoy($cust[position() <= $row])} </BALANCE_AT_END_OF_YEAR>
 21                            </ROW>' PASSING XMLTYPE(CURSOR(SELECT customer, amount, startdate, (1 + percentage / 100) percentage, 0
balance_at_end_of_year
 22                                                            FROM deposits, interest_rates
 23                                                           WHERE startdate = deposit_date
 24                                                           ORDER BY customer, startdate))) t
 25  /
       ROUND(VALUE(t).EXTRACT('ROW/BALANCE_AT_END_OF_YEAR/text()').getnumberval(),2) balance_at_end_of_year
                   *
FOUT in regel 5:
.ORA-00904: "T": ongeldige ID

Do you know why?

As cd already suggested in another thread, I am also looking forward to some "Introduction on XML in SQL" written by you ... :-)

Regards,
Rob.

Pitty, hope I did not run in any bug here. My tested version is

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

I've rewritten some part, since there was some issue with nls_numeric_characters as well. If you want, give this one a try too:

michaels>  ALTER SESSION SET nls_numeric_characters='.,'
Session altered.

michaels>  SELECT EXTRACT (COLUMN_VALUE,'ROW/CUSTOMER/text()').getnumberval() customer,
       EXTRACT (COLUMN_VALUE,'ROW/AMOUNT/text()').getnumberval() amount,
       TO_DATE(EXTRACT (COLUMN_VALUE, 'ROW/STARTDATE/text()').getstringval (),'DD-MON-RR') startdate,
       EXTRACT (COLUMN_VALUE, 'ROW/PERCENTAGE/text()').getnumberval() percentage,
       ROUND(EXTRACT (COLUMN_VALUE, 'ROW/BALANCE_AT_END_OF_YEAR/text()').getnumberval(),2) balance_at_end_of_year
  FROM XMLTABLE ('declare function local:baeoy($cust)  
                  {
                    if($cust[1]) then  
                      (0.01 * $cust[count($cust)]/PERCENTAGE + 1) * ($cust[count($cust)]/AMOUNT + local:baeoy($cust[position() <= count($cust) - 1])) 
                    else (0) 
                  }; (:-:)
                  for $c in distinct-values(/ROWSET/ROW/CUSTOMER)
                  let $cust := /ROWSET/ROW[CUSTOMER = $c]
                  for $i at $row in $cust
                   return <ROW> 
                          {$i/CUSTOMER}
                          {$i/AMOUNT}
                          {$i/STARTDATE}
                          {$i/PERCENTAGE}
                          <BALANCE_AT_END_OF_YEAR> {local:baeoy($cust[position() <= $row])} </BALANCE_AT_END_OF_YEAR>
                          </ROW>' PASSING XMLTYPE(CURSOR(SELECT customer, amount, startdate, percentage, 0 balance_at_end_of_year 
                                                           FROM deposits, interest_rates
                                                          WHERE startdate = deposit_date 
                                                       ORDER BY customer, startdate)))
/
  CUSTOMER     AMOUNT STARTDATE PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- --------- ---------- ----------------------
         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,25
         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,09


10 rows selected.

Regards,
Michael

Michaels, I tested again.

First a:

FOUT in regel 3:
.ORA-01843: Geen geldige maand.

And after setting my nls_date_format to DD-MON-RR a:

FOUT in regel 1:
.ORA-19112: fout bij evaluatie: oracle.xquery.XQException: XP0021: Dit is een dynamische fout die optreedt als een waarde in een
CAST-uitdrukking niet naar het vereiste type kan worden geconverteerd.
Detail: FORG0001: Ongeldige waarde voor conversie/constructor.

And most of all, this is s l o w . . . ., unbelievable for just 10 records.

Regards,
Rob.

Is this problem really different from "Hierarchical weighted total" -- hierarchy in this case collapsed to a linearly ordered sequence of dates? We have "part assembly"

grandchild(qty=1000) ----> child(qty=200) -----> parent(qty=500) ---->...

The links are weighed with interest rates, so that amended picture is:

grandchild(qty=1000) --1.05--> child(qty=200) ---1.032--> parent(qty=500) ---->...

The "Hierarchical weighted total" problem is to calculate aggregated qty at the root, and the "connect by" really shines for that kind of queries. Yet, the recursive with gives even more elegant answer.

================= Extract from "SQL Design patterns" ===============

Hierarchical Weighted Total

Before exploring aggregation on graphs, let’s have a quick look to aggregation on trees. Aggregation on trees is much simpler and has a designated name: hierarchical total. A typical query is
"Find combined salary of all the employees under (direct and indirect) supervision of King."
This query, however, has no inherent complexity, and effectively reduces to familiar task of finding a set all node’s descendants.

In graph context, the hierarchical total query is commonly referred to as bill of materials (BOM). Consider bicycle parts assembly

Part SubPart Quantity
Bicycle Wheel 2
Wheel Reflector 2
Bicycle Frame 1
Frame Reflector 2

Parts with the same name are assumed to be identical. They are modeled as nodes in an (acyclic directed) graph. The edges specify the construction flow:
1. Assemble each wheel from the required parts (including reflectors).
2. Assemble frame from the required components (including reflectors).
3. Assemble bicycle from the required parts (including frame and wheels).

Suppose we want to order the total list of parts for bike assembly. How do we calculate each part quantity? Unlike hierarchical total for a tree we can’t just add the quantities, as they multiply along each path.

Figure 6.11: Bicycle assembly graph. The total number of reflector parts should be calculated as the sum of parts aggregated along each path. The path /Bicycle/Wheel/Reflector contributes to 2*2=4 parts: bicycle has 2 wheels, each wheel has 2 reflectors. Likewise, the path /Bicycle/Frame/Reflector contribute to 1*2=2 more parts.

Therefore, there are two levels of aggregation here, multiplication of the quantities along each path, and summation along each alternative path.

----------------- Aggregation on Graphs (soapbox) -----------------
Two levels of aggregation fit naturally into in graphs queries. Consider finding a shortest path between two nodes. First, we add the distances along each path, and then we choose a path with minimal length.

Double aggregation is not something unique to graph queries, however. Consider
"Find the sum of the salaries grouped by department. Select maximum of them."
When expressing such a query in SQL, we accommodate the first sentence as an inner subquery inside the outer query corresponding to the second sentence

select max(salaryExpences) from (
select deptno, sum(sal) salaryExpenses
from Emp
group by dept
)
------------------------(end of soapbox)-------------------------

Hierarchical weighted total query has the same structure. The first level of aggregation where we join edges into paths is analogous to the group by subquery from the salary expense query. Formally, it is a transitive closure, which is enhanced with additional aggregates, or generalized transitive closure.

Figure 6.12: Generalized transitive closure. There are several aggregates naturally associated with each path: the first edge, the last edge, the path length, or any aggregate on the edge weights.

Let’s suggest some hypothetical SQL syntax for generalized transitive closure
select distinct first(Part), last(SubPart), product (Quantity)
from AssemblyEdges
connect by prior Part = later SubPart
Unfamiliar syntax requires clarification:
§ The product is a non-standard aggregate from chapter 4.
§ The first and last refer to the first and last edges in the path, correspondingly. These aggregates are unique to ordered structures such as (directed) graphs.
§ Although we didn’t use it in the example, concatenating edge labels into a string is one more natural aggregation function, which is unique to graphs. The list aggregate function is a standard way to accommodates it.
§ The later keyword is just a syntactic sugar fixing apparent asymmetry caused by the prior keyword.
§ There is no start with clause, which is, in fact, redundant. It is an outer query where we’ll restrict paths to those originated in the ‘Bicycle’ node.

The generalized transitive closure query is enveloped with second level of aggregation, which is accomplished by standard means
select leaf, sum(factoredQuantity) from (
select product(Quantity) factoredQuantity,
first(Part) root, last(SubPart) leaf
from AssemblyEdges
connect by prior Part = later SubPart
) where root = ‘Bicycle’
group by leaf

Enough theory, what do we do in real world to implement an aggregated weighted total query? Let’s start with Oracle, because the proposed syntax for generalized transitive closure resembles Oracle connect by.

First, we have to be able to refer to the first and the last edges in the path

select connect_by_root(Part) root, SubPart leaf
from AssemblyEdges
connect by prior Part = SubPart

Unlike our fictional syntax, Oracle treats edges in the path asymmetrically. Any column from the AssemblyEdges table is assumed to (implicitly) refer to the last edge. This design dates back to version 7. The connect_by_root function referring to the first edge has been added in version 10. It is remarkable how this, essentially ad-hock design proved to be successful in practice.

Next, Oracle syntax has several more path aggregate functions:
1. level – the length of the path.
2. sys_connect_by_path – which is essentially the list aggregate in our fictitious syntax.
3. connect_by_is_leaf – an indicator if there is no paths which contain the current path as a prefix.
4. connect_by_is_cycle – an indicator if the first edge is adjacent to the last.

Unfortunately, we need an aggregate which is a product of edge weights, and not a string which is a concatenation of weights produced by sys_connect_by_path. You might be tempted to hack a function which accepts a string of the edge weights, parses it, and returns the required aggregate value.

Nah, too easy! Can we solve the problem without coding a function (even a simple one)? Yes we can, although this solution would hardly be more efficient. The critical idea is representing any path in the graph as a concatenation of three paths:

§ a prefix path from the start node to some intermediate node i
§ a path consisting of a single weighted edge (i,j)
§ a postfix path from the node i to the end node

Figure 6.13: A path from node x to node y is a composition of the path from node x to node i, the edge (i, j), and the path from j to y. The edge (i, j) can be positioned anywhere along the path from x to y.
Then, we freeze the path from x to y, while interpreting the edge (i,j) as a variable. All we need to do is aggregating the weights of all those edges.

Let’s assemble the solution piece by piece. First, all the paths in the graphs are expressed as
with TClosure as (
select distinct connect_by_root(Part) x, SubPart y,
sys_connect_by_path('['||Part||','||SubPart||'>',' ') path
from AssemblyEdges
connect by nocycle Part=prior SubPart
union
select Part, Part, '' from AssemblyEdges
union
select SubPart, SubPart, '' from AssemblyEdges
) …
This is essentially reflexive transitive closure relation enhanced with the path column. Paths are strings of concatenated edges; each edge is sugarcoated into '['||Part||','||SubPart||'>', which helps visual perception, but is inessential for the solution.

Next, we join two paths and intermediate edge together, and group by paths
… , PathQuantities as (
select t1.x, t2.y,
t1.p||' ['||e.Part||','||e.SubPart||'>'||t2.p,
product(Quantity) Quantity
from TClosure t1, AssemblyEdges e, TClosure t2
where t1.y = e.Part and e.SubPart = t2.x
group by t1.x, t2.y, t1.p||' ['||e.Part||','||e.SubPart||'>'||t2.p
) …
There we have all the paths with quantities aggregated along them. Let’s group the paths by the first and last node in the path while adding the quantities
select x, y, sum(Quantity)
from PathQuantities
group by x, y
This query is almost final, as it needs only a minor touch: restricting the node x to ‘Bicycle’ and interpreting the y column as a Part in the assembly
select y Part, sum(Quantity)
from PathQuantities
where x = ‘Bicycle’
group by x, y

Let’s move on to recursive SQL solution. It turns out to be quite satisfactory
with TCAssembly as (
select Part, SubPart, Quantity AS factoredQuantity
from AssemblyEdges
where Part = ‘Bicycle’
union all
select te.Part, e.SubPart, e.Quantity * te.factoredQuantity
from TCAssembly te, AssemblyEdges e
where te.SubPart = e.Part
) select SubPart, sum(Quantity) from TCAssembly
group by SubPart

The most important, it accommodated the inner aggregation with non-standard aggregate effortlessly! Second, the cycle detection issue that plagued recursive SQL in the section on transitive closure is not a problem for directed acyclic graphs.

It could be done without any cheating.

create or replace type two as object (x number, y number);
/

create or replace type mul_type as object
(
  total number,

  static function ODCIAggregateInitialize(sctx IN OUT mul_type)
    return number,

  member function ODCIAggregateIterate(self IN OUT mul_type, value IN two)
    return number,

  member function ODCIAggregateTerminate(self        IN mul_type,
                                         returnValue OUT varchar2,
                                         flags       IN number)
    return number,

  member function ODCIAggregateMerge(self IN OUT mul_type,
                                     ctx2 IN mul_type) return number
);
/

create or replace type body mul_type is

  static function ODCIAggregateInitialize(sctx IN OUT mul_type) return number is
  begin
    sctx := mul_type(0);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT mul_type, value IN two)
    return number is
  begin
    self.total := round((self.total + value.x) * value.y, 2);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self        IN mul_type,
                                         returnValue OUT varchar2,
                                         flags       IN number) return number is
  begin
    returnValue := self.total;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT mul_type,
                                     ctx2 IN mul_type) return number is
  begin
    return ODCIConst.Success;
  end;

end;
/

CREATE OR REPLACE FUNCTION mulagg(input two) RETURN number
  AGGREGATE USING mul_type;
/

SQL> select d.*,
  2         i.*,
  3         mulagg(two(d.amount, 1 + i.percentage / 100)) over(partition by d.customer order by d.deposit_date) balance_at_end_of_year
  4    from deposits d, interest_rates i
  5   where d.deposit_date = i.startdate
  6  /
 
  CUSTOMER     AMOUNT DEPOSIT_DATE STARTDATE   PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- ------------ ----------- ---------- ----------------------
         1       1000 01.01.2003   01.01.2003           5                   1050
         1        200 01.01.2004   01.01.2004         3,2                   1290
         1        500 01.01.2005   01.01.2005         4,1                1863,39
         1        100 01.01.2006   01.01.2006         5,8                2077,27
         1        800 01.01.2007   01.01.2007         4,9                3018,26
         2         20 01.01.2003   01.01.2003           5                     21
         2        150 01.01.2004   01.01.2004         3,2                 176,47
         2         60 01.01.2005   01.01.2005         4,1                 246,17
         2        100 01.01.2006   01.01.2006         5,8                 366,25
         2        100 01.01.2007   01.01.2007         4,9                  489,1
 
10 rows selected

Another one solution. Just for fun. (self join makes this solution unattractive)

create or replace type xyz as object (x number, y number, z number)
/
create or replace type xyz_tbl as table of xyz
/
create or replace function mul(p in xyz_tbl) return number is
  Result number := 0;
begin
  for i in (select x, y from table(p) order by z) loop
    result := round((result + i.x) * i.y, 2);
  end loop;
  return result;
end mul;
/

SQL> select d.*,
  2         i.*,
  3         mul(cast(multiset(select xyz(d1.amount,
  4                              1 + i1.percentage / 100,
  5                              row_number() over(order by i1.startdate))
  6                     from deposits d1, interest_rates i1
  7                    where i1.startdate = d1.deposit_date
  8                      and d1.deposit_date <= d.deposit_date
  9                      and d1.customer = d.customer) as xyz_tbl)) balance_at_end_of_year
 10    from deposits d, interest_rates i
 11   where d.deposit_date = i.startdate
 12  /
 
  CUSTOMER     AMOUNT DEPOSIT_DATE STARTDATE   PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- ------------ ----------- ---------- ----------------------
         1       1000 01.01.2003   01.01.2003           5                   1050
         1        200 01.01.2004   01.01.2004         3,2                   1290
         1        500 01.01.2005   01.01.2005         4,1                1863,39
         1        100 01.01.2006   01.01.2006         5,8                2077,27
         1        800 01.01.2007   01.01.2007         4,9                3018,26
         2         20 01.01.2003   01.01.2003           5                     21
         2        150 01.01.2004   01.01.2004         3,2                 176,47
         2         60 01.01.2005   01.01.2005         4,1                 246,17
         2        100 01.01.2006   01.01.2006         5,8                 366,25
         2        100 01.01.2007   01.01.2007         4,9                  489,1
 
10 rows selected
SQL> select customer,
  2         amount,
  3         deposit_date,
  4         startdate,
  5         percentage,
  6         mul(cast(collect(tmp) as xyz_tbl)) balance_at_end_of_year
  7    from (select d.*,
  8                 i.*,
  9                 xyz(d1.amount,
 10                     1 + i1.percentage / 100,
 11                     row_number() over(order by i1.startdate)) tmp
 12            from deposits d, interest_rates i, deposits d1, interest_rates i1
 13           where d.deposit_date = i.startdate
 14             and i1.startdate = d1.deposit_date
 15             and d1.deposit_date <= d.deposit_date
 16             and d1.customer = d.customer)
 17   group by customer, amount, deposit_date, startdate, percentage
 18   order by customer, startdate
 19  /
 
  CUSTOMER     AMOUNT DEPOSIT_DATE STARTDATE   PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- ------------ ----------- ---------- ----------------------
         1       1000 01.01.2003   01.01.2003           5                   1050
         1        200 01.01.2004   01.01.2004         3,2                   1290
         1        500 01.01.2005   01.01.2005         4,1                1863,39
         1        100 01.01.2006   01.01.2006         5,8                2077,27
         1        800 01.01.2007   01.01.2007         4,9                3018,26
         2         20 01.01.2003   01.01.2003           5                     21
         2        150 01.01.2004   01.01.2004         3,2                 176,47
         2         60 01.01.2005   01.01.2005         4,1                 246,17
         2        100 01.01.2006   01.01.2006         5,8                 366,25
         2        100 01.01.2007   01.01.2007         4,9                  489,1
 
10 rows selected

Hi,4 years old members :-)
Hehehe,I like recursive with clause B-)

with tmp as(
select a.customer,a.amount,a.deposit_date,b.percentage,
Row_Number() over(partition by customer order by a.deposit_date) as rn
  from deposits a,interest_rates b
 where a.deposit_date=b.startdate),
rec(customer,amount,deposit_date,PERCENTAGE,rn,BALANCE) as(
select customer,amount,deposit_date,percentage,rn,amount*(1+percentage/100)
  from tmp a
 where rn=1
union all
select b.customer,b.amount,b.deposit_date,b.percentage,b.rn,
(a.BALANCE+b.amount)*(1+b.percentage/100)
  from rec a,tmp b
 where a.rn+1=b.rn
   and a.customer = b.customer)
select*from rec order by customer,deposit_date;

customer  amount  deposit_  percentage  rn    balance
--------  ------  --------  ----------  --  ---------
       1    1000  03-01-01           5   1       1050
       1     200  04-01-01         3.2   2       1290
       1     500  05-01-01         4.1   3    1863.39
       1     100  06-01-01         5.8   4  2077.2666
       1     800  07-01-01         4.9   5  3018.2527
       2      20  03-01-01           5   1         21
       2     150  04-01-01         3.2   2    176.472
       2      60  05-01-01         4.1   3  246.16735
       2     100  06-01-01         5.8   4  366.24506
       2     100  07-01-01         4.9   5  489.09107