トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-192 高さが1小さい要素の数を求めるその1
SQLパズル
pathTable timeTable
SEQ NO Code name Code1 Code2 time
--- -- ---- ---- ----- ----- ----
1 1 A Ａ A B 10
2 2 B Ｂ B C 20
3 3 C Ｃ B D 30
4 3 D Ｄ C E 40
5 4 E Ｅ D E 50
6 5 F Ｆ E F 60
7 5 G Ｇ E G 70
8 6 H Ｈ F H 80
9 6 I Ｉ F I 90
10 7 J Ｊ G H 100
G I 110
H J 120
I J 130
pathTableを階層問い合わせしたイメージは、こうなります。
NO name
-- --------
1 Ａ
｜
2 Ｂ
／ ＼
3 Ｃ Ｄ
＼ ／
4 Ｅ
／ ＼
5 Ｆ Ｇ
｜
6 Ｈ Ｉ
＼ ／
7 Ｊ
出力結果の求め方
pathTable の NO順にCode(name)を階層展開します。
NO=1 を持った Aは、 NO=2を持った Bへ
NO=2 を持った Bは、 NO=3を持った CとDへ
NO=3 を持った CとDは、NO=4を持った Eへ
NO=4 を持った Eは、 NO=5を持った FとGへ
NO=5 を持った FとGは、NO=6を持った HとIへ
NO=6 を持った HとIは、NO=7を持った Jへ
という展開方法になります。
また同時に、timeTableからtimeを取得したい。
取得時は、Bに変化するにあたり、
Aからかかる時間として取得するという取得方法になります。
また、timeを取得するにあたり、以下のような条件があります。
A のようなパターンの時、 timeTableにデータが無いので 0 とする。
B のようなパターンの時、 A->Bとして 10 を取得 (補足1)
C のようなパターンの時、 B->Cとして 20 を取得 (補足1)
D のようなパターンの時、 B->Dとして 30 を取得 (補足1)
E のようなパターンの時、 C->E + D->E として 40+50=90 を取得 (補足2)
F のようなパターンの時、 E->Fとして 60 を取得 (補足1)
G のようなパターンの時、 E->Gとして 70 を取得 (補足1)
H のようなパターンの時、 MAX(F->H,G->H) として 100 を取得 (補足3)
I のようなパターンの時、 MAX(F->I,G->I) として 110 を取得 (補足3)
J のようなパターンの時、 H->J + I->J として 120+130=250 を取得 (補足2)
(補足1) 1->1 は、そのまま取得
(補足2) 2->1 は、合計で取得。
(補足3) 2->2 は、パターンの最大で取得
ただし、左辺や、右辺の2は2とは限らない(最大数はn)
他の注意
NO=1の時に前のデータが無くても必要
C->D や、 F->G,H->I等の同じレベルでの横データは不要
出力結果
SEQ NO Code name time
--- -- ---- ---- ----
1 1 A Ａ 0
2 2 B Ｂ 10
3 3 C Ｃ 20
4 3 D Ｄ 30
5 4 E Ｅ 90
6 5 F Ｆ 60
7 5 G Ｇ 70
8 6 H Ｈ 100
9 6 I Ｉ 110
10 7 J Ｊ 250
データ作成スクリプト
create table pathTable(
SEQ number(2) not null,
NO number(2) not null,
Code char(1) not null,
name char(2) not null,
primary key(SEQ));
create table timeTable(
Code1 char(1) not null,
Code2 char(1) not null,
time number(3) not null,
primary key(Code1,Code2));
insert all
into pathTable values( 1,1,'A','Ａ')
into pathTable values( 2,2,'B','Ｂ')
into pathTable values( 3,3,'C','Ｃ')
into pathTable values( 4,3,'D','Ｄ')
into pathTable values( 5,4,'E','Ｅ')
into pathTable values( 6,5,'F','Ｆ')
into pathTable values( 7,5,'G','Ｇ')
into pathTable values( 8,6,'H','Ｈ')
into pathTable values( 9,6,'I','Ｉ')
into pathTable values(10,7,'J','Ｊ')
into timeTable values('A','B', 10)
into timeTable values('B','C', 20)
into timeTable values('B','D', 30)
into timeTable values('C','E', 40)
into timeTable values('D','E', 50)
into timeTable values('E','F', 60)
into timeTable values('E','G', 70)
into timeTable values('F','H', 80)
into timeTable values('F','I', 90)
into timeTable values('G','H',100)
into timeTable values('G','I',110)
into timeTable values('H','J',120)
into timeTable values('I','J',130)
select 1 from dual;
commit;
SQL
--■■■Row_Number関数の結果で行間アクセスする方法■■■
select distinct 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 SEQ,NO,Code,name,LV,PreCode,LVCount,Row_ID,
Lag(LVCount,SeekCount) over(order by LV,Row_ID,PreCode) as PreLVCount,
(select b.time
from timeTable b
where b.Code1 = a.PreCode
and b.Code2 = a.Code) as time
from (select SEQ,NO,Code,name,LV,PreCode,Row_ID,
count(distinct Row_ID) over(partition by LV) as LVCount,
Row_Number() over(partition by LV order by Row_ID,PreCode) as SeekCount
from (select distinct SEQ,NO,Code,name,Level as LV,
prior Code as PreCode,RowID as Row_ID
from pathTable a
Start With NO = 1
connect by prior No+1 = No)) a)
order by SEQ;
--■■■rangeで行間アクセスする方法■■■
select distinct SEQ,NO,Code,name,
case when LV = 1
then 0
when PreLVCount =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 SEQ,NO,Code,name,LV,PreCode,LVCount,Row_ID,
max(LVCount)
over(order by LV range between 1 preceding
and 1 preceding) as PreLVCount,
(select b.time
from timeTable b
where b.Code1 = a.PreCode
and b.Code2 = a.Code) as time
from (select SEQ,NO,Code,name,LV,PreCode,Row_ID,
count(distinct Row_ID) over(partition by LV) as LVCount
from (select distinct SEQ,NO,Code,name,Level as LV,
prior Code as PreCode,RowID as Row_ID
from pathTable a
Start With NO = 1
connect by prior No+1 = No)) a)
order by SEQ;
解説
Row_Number関数の結果で行間アクセスする方法での、
高さが1小さい要素の数を求める方法は、SQLクックブックのレシピ8.7を応用してます。
8-7 次の入社日を求める
そして、最後にselect句で、case式による場合分けを使ってます。
10-132 大小比較で場合分け
10-164 場合の数を求めて、場合分け
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
rangeで行間アクセスする方法のように、
高さが1小さい要素の数を求める方法は、rangeを使ってもいいです。
8-14 Seqが1小さいデータも出力