Hi all,
I would like to ask your opinion on the best way to approach this kind of problem. Imagine I have a recursive structure like this one:
create table boxes
(
id number,
parent_id number,
s number, -- size
name varchar2(1000)
);
/* root level boxes don't have a parent id and they don't have sizes */
insert into boxes(id, parent_id, s, name) values(1, null, null, 'Top level box');
/* we can have any number of boxes in a parent box */
insert into boxes(id, parent_id, s, name) values(2, 1, null, 'Box 1');
insert into boxes(id, parent_id, s, name) values(3, 1, null, 'Box 2');
/* and we can have as many levels in the hierarchy */
insert into boxes(id, parent_id, s, name) values(4, 2, null, 'Box 1.1');
insert into boxes(id, parent_id, s, name) values(5, 3, null, 'Box 2.1');
/* terminal level boxes have a size */
insert into boxes(id, parent_id, s, name) values(6, 4, 33, 'Box 1.1.1');
insert into boxes(id, parent_id, s, name) values(7, 5, 22, 'Box 2.1.1');
COMMIT;
I have another table with box contents. Each terminal level can have some content in it. A table that may look like this:
create table box_contents
(
id number,
box_id number,
content_id number,
s number --size
);
insert into box_contents(id, box_id, content_id, s) VALUES(1, 6, 1, 1);
insert into box_contents(id, box_id, content_id, s) VALUES(2, 6, 2, 1);
insert into box_contents(id, box_id, content_id, s) VALUES(3, 6, 3, 10);
insert into box_contents(id, box_id, content_id, s) VALUES(4, 7, 1, 10);
insert into box_contents(id, box_id, content_id, s) VALUES(5, 7, 2, 5);
insert into box_contents(id, box_id, content_id, s) VALUES(6, 7, 3, 10);
I would like to know what is the best way to approach this? I want to generate a report like this
ID PARENT ID LEVEL Name State TOTAL TOTAL_USED TOTAL_EXTRA TOTAL_LEFT CNT_DIRECT_CHILD CNT_CONTENT
--- --------- ----- --------------- ---------- ------ ---------- ---------- ---------- ---------------- ------------
1 NULL 1 Top level box NOT FULL 55 34 3 21 2 6
2 1 2 Box 1 NOT FULL 33 12 0 21 1 3
3 1 2 Box 2 FULL 22 22 3 0 1 3
4 2 3 Box 1.1 NOT FULL 33 12 0 21 1 3
5 3 3 Box 2.1 FULL 22 22 3 0 1 3
6 4 4 Box 1.1.1 NOT FULL 33 12 0 21 0 3
7 5 4 Box 2.1.1 FULL 22 22 3 0 0 3
I would like to get a view of the full structure and a lot of statistics around.
- The state should check ANY terminal level. And if any one they is NOT NULL, that state should be propagated up to the roots. Any intermediate levels having a terminal level that is NOT FULL should also be marked as NOT FULL;
- The total used should be the SUM of the contents in terminal levels. But, if content is bigger that the size of the terminal box, we take terminal size and we calculate the extra.
- Total left is the remaining size and propagated to parent levels
- Count direct child is the number of directly attached boxes (not all descending). I guess calculating also all descending would be too costly?
- Count contents will be the number of contents attached to terminal level boxes (and summed up to the roots).
This exemple is quite simple and in reality I have a structure with a lot more data with many branches. So, I was wondering what would be the best approach to generate such report?