SQL & PL/SQL

Good morning everybody,

I have a small question and I wonder what is the best approach to handle thees scenarios.

If we have a structure like this one.

drop table boxes;
CREATE TABLE boxes (
   id number, 
   parent_id number, 
   capacity number
);

insert into boxes(id, parent_id) values(1, NULL);
insert into boxes(id, parent_id) values(2, 1);
insert into boxes(id, parent_id) values(3, 2);
insert into boxes(id, parent_id, capacity) values(4, 3, 10);

insert into boxes(id, parent_id) values(18, 1);
insert into boxes(id, parent_id, capacity) values(19, 18, 5);


insert into boxes(id, parent_id) values(10, NULL);
insert into boxes(id, parent_id) values(11, 10);
insert into boxes(id, parent_id, capacity) values(12, 11, 3);

It's a parent/child relation. I simplied to keep only what is relevant. We have an id and a parent_id pointing to parent node.

And at the leaves level we have a capacity. Now I would like to compute at EACH level of the tree the sum capacity of all descending nodes….

If we take this example, I want something like this:

        ID  PARENT_ID   CAPACITY      LEVEL TOTAL_CAPACITY
---------- ---------- ---------- ---------- --------------
         1                                1             15    - total of all leaves below this node (10 + 5)
         2          1                     2             10    - total of all the leaves below this branch (only one leaf)
         3          2                     3             10
         4          3         10          4             10
        18          1                     2              5   -- 
        19         18          5          3              5
        10                                1              3
        11         10                     2              3
        12         11          3          3              3

9 rows selected. 

When I have that, I want to assign some objects to these boxes. Imagine, I add the following code:

CREATE TABLE objects(id number, box_id number, dimensions number);
 
insert into objects(id, box_id, dimensions) values(1, 4, 1);
insert into objects(id, box_id, dimensions) values(2, 19, 2);

We can add objects only at leaves. Is it possible to easily compute the space used at each level?

        ID  PARENT_ID   CAPACITY      LEVEL TOTAL_CAPACITY  USED_CAPACITY USED_CAPACITY_IN_PERCENTAGE
---------- ---------- ---------- ---------- --------------  ------------- ---------------------------
         1                                1             15            3                  (3/15)    2%
         2          1                     2             10            1                  (1/15) 0.67%  
         3          2                     3             10            1                  (1/15) 0.67%  
         4          3         10          4             10            1                  (1/15) 0.67%  
        18          1                     2              5            1                  (1/15) 0.67%   
        19         18          5          3              5            2                  (2/5)   0.4% 
        10                                1              3            0                  (0/3)     0%  
        11         10                     2              3            0                  (0/3)     0%  
        12         11          3          3              3            0                  (0/3)     0%  

As you can see, I would like to have from the leaves to the roots sums of all available space. And when we use them (putting boxes), we should check their dimensions to see how much left (size and in percentage). At some point, in my case i will have hundreads of thousands of rows in my objects table.

Any suggestions?