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

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

SQLパズル

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

10-192 高さが1小さい要素の数を求めるその1
のアレンジ問題です。

変更点は、以下の2点です。

timeTable2からtimeを取得できない場合は、0として扱う。

order by DAY,AMPM,NO
の順序で階層展開する。(下の図のイメージ)

DAY      |AMPM|NO|  name
---------|----|--|--------
20071001 |  1 |1|    A
         |    |  |    |
20070101 |  1 |2|    B
         |    |  |  /  \
20070101 |  1 |3|  C  D
         |    |  |  \  /
20070101 |  1 |4|    E
         |    |  |  /  \
20070101 |  1 |5|  F  G
         |    |  |    |
20070101 |  1 |6|  H  I
         |    |  |  \  /
20070101 |  1 |7|    J
         |    |  |    |
20071001 |  2 |1|    A
         |    |  |    |
20070101 |  2 |2|    B
         |    |  |  /  \
20070101 |  2 |3|  C  D
         |    |  |  \  /
20070101 |  2 |4|    E
         |    |  |  /  \
20070101 |  2 |5|  F  G
         |    |  |    |
20070101 |  2 |6|  H  I
         |    |  |  \  /
20070101 |  2 |7|    J
         |    |  |    |
20071002 |  1 |1|    A
         |    |  |    |
20070102 |  1 |2|    B
         |    |  |  /  \
20070102 |  1 |3|  C  D
         |    |  |  \  /
20070102 |  1 |4|    K
         |    |  |  /  \
20070102 |  1 |5|  F  G
         |    |  |    |
20070102 |  1 |6|  H  I
         |    |  |  \  /
20070102 |  1 |7|    J

出力結果
DAY       AMPM  SEQ  NO  Code  name  time
--------  ----  ---  --  ----  ----  ----
07-10-01     1    1   1  A     A       0
07-10-01     1    2   2  B     B      10
07-10-01     1    3   3  C     C      20
07-10-01     1    4   3  D     D      30
07-10-01     1    5   4  E     E      90
07-10-01     1    6   5  F     F      60
07-10-01     1    7   5  G     G      70
07-10-01     1    8   6  H     H     100
07-10-01     1    9   6  I     I     110
07-10-01     1   10   7  J     J     250
07-10-01     2    1   1  A     A     140
07-10-01     2    2   2  B     B      10
07-10-01     2    3   3  C     C      20
07-10-01     2    4   3  D     D      30
07-10-01     2    5   4  E     E      90
07-10-01     2    6   5  F     F      60
07-10-01     2    7   5  G     G      70
07-10-01     2    8   6  H     H     100
07-10-01     2    9   6  I     I     110
07-10-01     2   10   7  J     J     250
07-10-02     1    1   1  A     A     140
07-10-02     1    2   2  B     B      10
07-10-02     1    3   3  C     C      20
07-10-02     1    4   3  D     D      30
07-10-02     1    5   4  K     K       0
07-10-02     1    6   5  F     F       0
07-10-02     1    7   5  G     G       0
07-10-02     1    8   6  H     H     100
07-10-02     1    9   6  I     I     110
07-10-02     1   10   7  J     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','A')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 2,2,'B','B')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 3,3,'C','C')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 4,3,'D','D')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 5,4,'E','E')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 6,5,'F','F')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 7,5,'G','G')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 8,6,'H','H')
into pathTable2 values(to_date('20071001','yyyymmdd'),1, 9,6,'I','I')
into pathTable2 values(to_date('20071001','yyyymmdd'),1,10,7,'J','J')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 1,1,'A','A')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 2,2,'B','B')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 3,3,'C','C')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 4,3,'D','D')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 5,4,'E','E')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 6,5,'F','F')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 7,5,'G','G')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 8,6,'H','H')
into pathTable2 values(to_date('20071001','yyyymmdd'),2, 9,6,'I','I')
into pathTable2 values(to_date('20071001','yyyymmdd'),2,10,7,'J','J')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 1,1,'A','A')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 2,2,'B','B')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 3,3,'C','C')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 4,3,'D','D')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 5,4,'K','K')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 6,5,'F','F')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 7,5,'G','G')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 8,6,'H','H')
into pathTable2 values(to_date('20071002','yyyymmdd'),1, 9,6,'I','I')
into pathTable2 values(to_date('20071002','yyyymmdd'),1,10,7,'J','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;


SQL

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関数で木の高さを求めてます。

脳内で木をイメージするには、
情報処理技術者試験の木の問題に慣れるのが早いと思います。
階層問い合わせと、木構造