aggregation over two hierarchies

mikharakiri_nospaum@yahoo · *nix forums beginner Joined: 05 Feb 2005 Posts: 46

navrsale wrote:

navrsale · *nix forums beginner Joined: 22 Feb 2005 Posts: 5

thanks for observation. Here is a more complex example of data in the
JOB table. Is this directed acyclic graph? Or cyclic as there is
closure between 12-10-5-9-11-12 nodes and 16-15-12-11-9-0-14-16?

http://img23.exs.cx/my.php?loc=img23&image=directedgraph11th.png

Each node must be visited and the value prorated in recursive fashion:
foreach node
-start with a node, for example 16
-visit each hierarchy on which 16 depends, in this case hierarchies for
14 and 15, SUM their values and the current value of node 16, and that
will be new, prorated value for node 16
-repeat this recursively for each sub-hierarchy
until all nodes are prorated

GreyBeard · *nix forums Guru Wannabe Joined: 10 Mar 2005 Posts: 182

On Wed, 23 Feb 2005 10:45:52 -0800, Rauf Sarwar wrote:

mikharakiri_nospaum@yahoo · *nix forums beginner Joined: 05 Feb 2005 Posts: 46

navrsale wrote:

Frank van Bortel · *nix forums Guru Joined: 19 Apr 2005 Posts: 804

Rauf Sarwar wrote:

Rauf Sarwar · *nix forums Guru Joined: 03 May 2005 Posts: 353

<snip>

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

navrsale wrote:

navrsale · *nix forums beginner Joined: 22 Feb 2005 Posts: 5

DA Morgan <damorgan@x.washington.edu> wrote in message news:<1109136771.990778@yasure>...

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

navrsale wrote:

Sybrand Bakker · *nix forums Guru Joined: 03 Apr 2005 Posts: 1766

On 22 Feb 2005 13:00:01 -0800, "navrsale" <navrsalemile@yahoo.ca>
wrote:

navrsale · *nix forums beginner Joined: 22 Feb 2005 Posts: 5

In my application there are two given tables:

JOB table:

job_id open closed splitfrom mergedto
=================================================
AAAA 1/1/90 1/1/00
BBBB 1/1/80 1/1/00
CCCC 1/1/00 1/1/01 AAAA EEEE
DDDD 1/1/00 1/1/01 BBBB EEEE
EEEE 1/1/01
FFFF 1/1/01 CCCC

create table job (
job_id char(4) NOT NULL,
open_dte date not null,
close_dte date,
split_from char(4),
merge_to char(4),
PRIMARY KEY (job_id),
FOREIGN KEY (split_from) REFERENCES job(job_id),
FOREIGN KEY (merge_to) REFERENCES job(job_id)
)
/
INSERT INTO job VALUES( 'AAAA', to_date('01/01/90', 'MM/DD/RR'),
to_date('01/01/00', 'MM/DD/YY'), null, null )
/
INSERT INTO job VALUES( 'BBBB', to_date('01/01/80', 'MM/DD/RR'),
to_date('01/01/00', 'MM/DD/YY'), null, null )
/
INSERT INTO job VALUES( 'CCCC', to_date('01/01/00', 'MM/DD/RR'),
to_date('01/01/01', 'MM/DD/YY'), 'AAAA', 'EEEE' )
/
INSERT INTO job VALUES( 'DDDD', to_date('01/01/00', 'MM/DD/RR'),
to_date('01/01/01', 'MM/DD/YY'), 'BBBB', 'EEEE' )
/
INSERT INTO job VALUES( 'EEEE', to_date('01/01/01', 'MM/DD/RR'), null,
null,
null )
/
INSERT INTO job VALUES( 'FFFF', to_date('01/01/01', 'MM/DD/RR'), null,
'CCCC',
null )
/
commit
/

and JOB_REPORT table:

job_id job_name YR1 YR2 YR3 YR4 YR5
======================================================
AAAA name1 6 5 4 3 2
BBBB name2 5 4 3 2 1
CCCC name3 3 2 1 2 3
DDDD name4 7 6 5 4 3
EEEE name5 10 9 8 7 6
FFFF name6 3 4 5 6 7

create table job_report (
job_id char(4) NOT NULL,
job_name char(5) not null,
yr1 integer,
yr2 integer,
yr3 integer,
yr4 integer,
yr5 integer
PRIMARY KEY (job_id)
)
/

INSERT INTO job_report VALUES( 'AAAA', 'name1', 6,5,4,3,2 )
/
INSERT INTO job_report VALUES( 'BBBB', 'name2', 5,4,3,2,1 )
/
INSERT INTO job_report VALUES( 'CCCC', 'name3', 3,2,1,2,3 )
/
INSERT INTO job_report VALUES( 'DDDD', 'name4', 7,6,5,4,3 )
/
INSERT INTO job_report VALUES( 'EEEE', 'name5', 10,9,8,7,6 )
/
INSERT INTO job_report VALUES( 'FFFF', 'name6', 3,4,5,6,7 )
/
commit
/

JOB table describes different jobs and their history (some jobs are
split into another job and some jobs are merged into a new job). For
example this hierarchical query describes history inside JOB table:

column change_history format a50
column participant format a12
set pagesize 66
accept p_job_id prompt 'enter job id, 0 for all: '

select distinct DEPTH,
CHANGE_HISTORY,
substr( change_history, instr( change_history, '/', -1,
1 ) + 1 ) PARTICIPANT,
decode( DEPTH - 1, 0, null, EVENT ) EVENT
from (
select level DEPTH,
sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
'SPLIT' EVENT
from job
start with DECODE( '&p_job_id', '0', '1', job_id ) = decode(
'&p_job_id', '0', '1', '&p_job_id')
connect by prior split_from = job_id
union all
select level DEPTH,
sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
'MERGE' EVENT
from job
start with DECODE( '&p_job_id', '0', '1', job_id ) = decode(
'&p_job_id', '0', '1', '&p_job_id' )
connect by prior job_id = merge_to
)
order by 2;

which gives this result:

DEPTH CHANGE_HISTORY PARTICIPANT EVENT
--------- ----------------------- ------------ -----
1 /AAAA AAAA
1 /BBBB BBBB
1 /CCCC CCCC
2 /CCCC/AAAA AAAA SPLIT
1 /DDDD DDDD
2 /DDDD/BBBB BBBB SPLIT
1 /EEEE EEEE
2 /EEEE/CCCC CCCC MERGE
2 /EEEE/DDDD DDDD MERGE
1 /FFFF FFFF
2 /FFFF/CCCC CCCC SPLIT
3 /FFFF/CCCC/AAAA AAAA SPLIT

So here is the problem: a query must be created which combines above
"history query" with table JOB_REPORT to produce following result:

job_id job_name YR1 ....
==========================================
AAAA name1 6
BBBB name2 5
CCCC name3 9 (*
DDDD name4 12 (**
EEEE name5 31 (***
FFFF name6 12 (****

if the query is for all JOB_IDs

or just

job_id job_name YR1 ....
==========================================
EEEE name5 31

if the query is for specific JOB_ID (EEEE in this example). Query must
show data for every YR (YR1, YR2, YR3, YR4, YR5)

remark (* : 9 is sum of 3 and 6 where 3 is the number of people who
worked on job CCCC, and where 6 is the number of people who worked on
job AAAA from which job CCCC was split. In this example I only showed
column YR1 but the same logic is applicable for columns YR2, YR3,YR4
and YR5 as well

remark (**: 12 is the sum of 7 and 5 where 7 is the number of people
who worked on job DDDD, and where 5 is the number of people who worked
on job BBBB from which job DDDD was split.

remark (***: 31 is the sum 10+9+12 where 10 is the number of people who
worked on job EEEE and 9 is explained in remark (* and 12 is explained
in remark (**. I.e. CCCC and DDDD were merged into EEEE

remark (****: 12 is the sum 3+9 where 3 is the number of people who
worked on job FFFF and 9 is explained in remark (*. I.e. FFFF was split
from job CCCC.

Basically, in this example data, job CCCC was created by splitting from
job AAAA, and job DDDD by splitting from job BBBB. CCCC and DDDD were
then merged into EEEE, while at the same time job FFFF was split from
CCCC. Query must process each row in JOB_REPORT table and aggregate the
number of employees when these numbers represent the "history" of
particular JOB_ID to correct existing value in JOB_REPORT table (to
"prorate" the number of employees in JOB_REPORT table based on the
history of given JOB_ID represented inside JOB table).

thanks,
mile

Google