pathTable2 timeTable2
DAY AMPM SEQ NO Code name Code1 Code2 time
-------- ---- --- -- ---- ---- ----- ----- ----
20071001 1 1 1 A Ａ A B 10
20071001 1 2 2 B Ｂ B C 20
20071001 1 3 3 C Ｃ B D 30
20071001 1 4 3 D Ｄ C E 40
20071001 1 5 4 E Ｅ D E 50
20071001 1 6 5 F Ｆ E F 60
20071001 1 7 5 G Ｇ E G 70
20071001 1 8 6 H Ｈ F H 80
20071001 1 9 6 I Ｉ F I 90
20071001 1 10 7 J Ｊ G H 100
20071001 2 1 1 A Ａ G I 110
20071001 2 2 2 B Ｂ H J 120
20071001 2 3 3 C Ｃ I J 130
20071001 2 4 3 D Ｄ J A 140
20071001 2 5 4 E Ｅ
20071001 2 6 5 F Ｆ
20071001 2 7 5 G Ｇ
20071001 2 8 6 H Ｈ
20071001 2 9 6 I Ｉ
20071001 2 10 7 J Ｊ
20071002 1 1 1 A Ａ
20071002 1 2 2 B Ｂ
20071002 1 3 3 C Ｃ
20071002 1 4 3 D Ｄ
20071002 1 5 4 K Ｋ
20071002 1 6 5 F Ｆ
20071002 1 7 5 G Ｇ
20071002 1 8 6 H Ｈ
20071002 1 9 6 I Ｉ
20071002 1 10 7 J Ｊ
10-192 高さが1小さい要素の数を求めるその1
のアレンジ問題です。
変更点は、以下の2点です。
timeTable2からtimeを取得できない場合は、0として扱う。
order by DAY,AMPM,NO
の順序で階層展開する。(下の図のイメージ)
DAY |AMPM|NO| name
---------|----|--|--------
20071001 | 1 |１| Ａ
| | | ｜
20070101 | 1 |２| Ｂ
| | | ／ ＼
20070101 | 1 |３| Ｃ Ｄ
| | | ＼ ／
20070101 | 1 |４| Ｅ
| | | ／ ＼
20070101 | 1 |５| Ｆ Ｇ
| | | ｜
20070101 | 1 |６| Ｈ Ｉ
| | | ＼ ／
20070101 | 1 |７| Ｊ
| | | ｜
20071001 | 2 |１| Ａ
| | | ｜
20070101 | 2 |２| Ｂ
| | | ／ ＼
20070101 | 2 |３| Ｃ Ｄ
| | | ＼ ／
20070101 | 2 |４| Ｅ
| | | ／ ＼
20070101 | 2 |５| Ｆ Ｇ
| | | ｜
20070101 | 2 |６| Ｈ Ｉ
| | | ＼ ／
20070101 | 2 |７| Ｊ
| | | ｜
20071002 | 1 |１| Ａ
| | | ｜
20070102 | 1 |２| Ｂ
| | | ／ ＼
20070102 | 1 |３| Ｃ Ｄ
| | | ＼ ／
20070102 | 1 |４| Ｋ
| | | ／ ＼
20070102 | 1 |５| Ｆ Ｇ
| | | ｜
20070102 | 1 |６| Ｈ Ｉ
| | | ＼ ／
20070102 | 1 |７| Ｊ
出力結果
DAY AMPM SEQ NO Code name time
-------- ---- --- -- ---- ---- ----
07-10-01 1 1 1 A Ａ 0
07-10-01 1 2 2 B Ｂ 10
07-10-01 1 3 3 C Ｃ 20
07-10-01 1 4 3 D Ｄ 30
07-10-01 1 5 4 E Ｅ 90
07-10-01 1 6 5 F Ｆ 60
07-10-01 1 7 5 G Ｇ 70
07-10-01 1 8 6 H Ｈ 100
07-10-01 1 9 6 I Ｉ 110
07-10-01 1 10 7 J Ｊ 250
07-10-01 2 1 1 A Ａ 140
07-10-01 2 2 2 B Ｂ 10
07-10-01 2 3 3 C Ｃ 20
07-10-01 2 4 3 D Ｄ 30
07-10-01 2 5 4 E Ｅ 90
07-10-01 2 6 5 F Ｆ 60
07-10-01 2 7 5 G Ｇ 70
07-10-01 2 8 6 H Ｈ 100
07-10-01 2 9 6 I Ｉ 110
07-10-01 2 10 7 J Ｊ 250
07-10-02 1 1 1 A Ａ 140
07-10-02 1 2 2 B Ｂ 10
07-10-02 1 3 3 C Ｃ 20
07-10-02 1 4 3 D Ｄ 30
07-10-02 1 5 4 K Ｋ 0
07-10-02 1 6 5 F Ｆ 0
07-10-02 1 7 5 G Ｇ 0
07-10-02 1 8 6 H Ｈ 100
07-10-02 1 9 6 I Ｉ 110
07-10-02 1 10 7 J Ｊ 250
create table pathTable2(
DAY DATE not null,
AMPM number(1) not null, -- 1=AM,2=PM
SEQ number(2) not null,
NO number(2) not null,
Code char(1) not null,
name char(2) not null,
primary key(DAY,AMPM,SEQ));
create table timeTable2(
Code1 char(1) not null,
Code2 char(1) not null,
time number(3) not null,
primary key(Code1,Code2));
insert all
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 1,1,'A','Ａ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 2,2,'B','Ｂ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 3,3,'C','Ｃ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 4,3,'D','Ｄ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 5,4,'E','Ｅ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 6,5,'F','Ｆ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 7,5,'G','Ｇ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 8,6,'H','Ｈ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 9,6,'I','Ｉ')
into pathTable2 values(to_date('20071001','yyyymmdd'),1,10,7,'J','Ｊ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 1,1,'A','Ａ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 2,2,'B','Ｂ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 3,3,'C','Ｃ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 4,3,'D','Ｄ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 5,4,'E','Ｅ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 6,5,'F','Ｆ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 7,5,'G','Ｇ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 8,6,'H','Ｈ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 9,6,'I','Ｉ')
into pathTable2 values(to_date('20071001','yyyymmdd'),2,10,7,'J','Ｊ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 1,1,'A','Ａ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 2,2,'B','Ｂ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 3,3,'C','Ｃ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 4,3,'D','Ｄ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 5,4,'K','Ｋ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 6,5,'F','Ｆ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 7,5,'G','Ｇ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 8,6,'H','Ｈ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 9,6,'I','Ｉ')
into pathTable2 values(to_date('20071002','yyyymmdd'),1,10,7,'J','Ｊ')
into timeTable2 values('A','B', 10)
into timeTable2 values('B','C', 20)
into timeTable2 values('B','D', 30)
into timeTable2 values('C','E', 40)
into timeTable2 values('D','E', 50)
into timeTable2 values('E','F', 60)
into timeTable2 values('E','G', 70)
into timeTable2 values('F','H', 80)
into timeTable2 values('F','I', 90)
into timeTable2 values('G','H',100)
into timeTable2 values('G','I',110)
into timeTable2 values('H','J',120)
into timeTable2 values('I','J',130)
into timeTable2 values('J','A',140)
select 1 from dual;
commit;
select distinct DAY,AMPM,SEQ,NO,Code,name,
case when LV = 1
then 0
when PreLVCount =1 and LVCount =1
then time
when PreLVCount =1 and LVCount >1
then time
when PreLVCount >1 and LVCount =1
then sum(time) over(partition by Row_ID)
when PreLVCount >1 and LVCount >1
then max(time) over(partition by Row_ID) end as time
from (select DAY,AMPM,SEQ,NO,Code,name,LV,LVCount,Row_ID,
max(LVCount)
over(order by LV range between 1 preceding
and 1 preceding) as PreLVCount,
nvl((select b.time
from timeTable2 b
where b.Code1 = a.PreCode
and b.Code2 = a.Code),0) as time
from (select DAY,AMPM,SEQ,NO,Code,name,LV,PreCode,Row_ID,
count(distinct Row_ID) over(partition by LV) as LVCount
from (select distinct DAY,AMPM,SEQ,NO,Code,name,Level as LV,
prior Code as PreCode,RowID as Row_ID
from (select DAY,AMPM,SEQ,NO,Code,name,
dense_Rank() over(order by DAY,AMPM,NO) as Rank
from pathTable2)
Start With Rank = 1
connect by prior Rank+1 = Rank)) a)
order by DAY,AMPM,SEQ;
最初に、dense_Rank関数で木の高さを求めてます。 脳内で木をイメージするには、 情報処理技術者試験の木の問題に慣れるのが早いと思います。 階層問い合わせと、木構造