10-144 case式とLag関数とSum関数の組み合わせ

SQLパズル

TreeTable2
gcode  lvl  name
-----  ---  ----
   10    1    A
   21    2    B
   32    3    C
   40    4    D
   55    4    E
   60    3    F
   75    4    G
   80    3    H
   90    4    I
  101    3    J
  110    1    K
  122    2    L
  130    3    M
  144    4    N
  155    4    O
  166    3    P
  170    4    Q
  500    1    R
  600    3    S

lvlが、2以上インクリメントされることは、
ありうるとして

gcodeの昇順で、以下の出力を行う

出力結果
TreeInfo
--------
/A/B/C/D
/-/-/-/E
/-/-/F/G
/-/-/H/I
/-/-/J
/K/L/M/N
/-/-/-/O
/-/-/P/Q
/R/-/S

データ作成スクリプト

create table TreeTable2 as
select 10 as gcode,1 as lvl,'A' as coa_name from dual
union all select  21,2,'B' from dual
union all select  32,3,'C' from dual
union all select  40,4,'D' from dual
union all select  55,4,'E' from dual
union all select  60,3,'F' from dual
union all select  75,4,'G' from dual
union all select  80,3,'H' from dual
union all select  90,4,'I' from dual
union all select 101,3,'J' from dual
union all select 110,1,'K' from dual
union all select 122,2,'L' from dual
union all select 130,3,'M' from dual
union all select 144,4,'N' from dual
union all select 155,4,'O' from dual
union all select 166,3,'P' from dual
union all select 170,4,'Q' from dual
union all select 500,1,'R' from dual
union all select 600,3,'S' from dual;

SQL

select RTrim(
   '/' || max(decode(lvl,1,coa_name,'-'))
|| '/' || max(decode(lvl,2,coa_name,'-'))
|| '/' || max(decode(lvl,3,coa_name,'-'))
|| '/' || max(decode(lvl,4,coa_name)),'/-') as TreeInfo
from (select lvl,coa_name,sum(willSum) over(order by gcode) as groupNum
        from (select gcode,lvl,coa_name,
              case when Lag(lvl) over(order by gcode) < lvl
                   then 0 else 1 end as willSum
                from TreeTable2))
group by groupNum
order by groupNum;

解説

case式とLag関数とSum関数を組み合わせて、groupNumを求めて
groupNumでグループ化してます

---