トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-192 高さが1小さい要素の数を求めるその1

SQLパズル

pathTable                 timeTable
SEQ  NO  Code  name       Code1  Code2  time
---  --  ----  ----       -----  -----  ----
  1   1  A     A         A      B        10
  2   2  B     B         B      C        20
  3   3  C     C         B      D        30
  4   3  D     D         C      E        40
  5   4  E     E         D      E        50
  6   5  F     F         E      F        60
  7   5  G     G         E      G        70
  8   6  H     H         F      H        80
  9   6  I     I         F      I        90
 10   7  J     J         G      H       100
                          G      I       110
                          H      J       120
                          I      J       130

pathTableを階層問い合わせしたイメージは、こうなります。

NO    name
--  --------
 1     A
       |
 2     B
     /  \
 3   C  D
     \  /
 4     E
     /  \
 5   F  G
       |
 6   H  I
     \  /
 7     J

出力結果の求め方
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     A       0
  2   2  B     B      10
  3   3  C     C      20
  4   3  D     D      30
  5   4  E     E      90
  6   5  F     F      60
  7   5  G     G      70
  8   6  H     H     100
  9   6  I     I     110
 10   7  J     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','A')
into pathTable values( 2,2,'B','B')
into pathTable values( 3,3,'C','C')
into pathTable values( 4,3,'D','D')
into pathTable values( 5,4,'E','E')
into pathTable values( 6,5,'F','F')
into pathTable values( 7,5,'G','G')
into pathTable values( 8,6,'H','H')
into pathTable values( 9,6,'I','I')
into pathTable values(10,7,'J','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小さいデータも出力