10-281 集合関数的なLast_Value関数を代用

SQLパズル

TreeTbl
Key    Val2  Val3  Val4
-----  ----  ----  ----
1      null  null  null
12        1  null  null
123    null  null  null
13     null     2  null
134    null     3     3
1345   null  null  null
13456  null  null  null
19     null     4     4
191    null  null  null
1912      1  null  null
19123  null     9  null
20     null     1     9
25        1     2  null
251    null  null  null
2513   null  null  null

(枝別れのない)木の経路上で、木の高さをソートキーとしての、
Last_Value(値 ignore nulls)
を求める。

出力結果
Key    Val2  Val3  Val4
-----  ----  ----  ----
123    1     null  null
13456  null  3     3
19123  1     9     4
20     null  1     9
2513   1     2     null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table TreeTbl(
Key  varchar2(5),
Val2 number(1),
Val3 number(1),
Val4 number(1));

insert into TreeTbl values('1'    ,null,null,null);
insert into TreeTbl values('12'   ,   1,null,null);
insert into TreeTbl values('123'  ,null,null,null);
insert into TreeTbl values('13'   ,null,   2,null);
insert into TreeTbl values('134'  ,null,   3,   3);
insert into TreeTbl values('1345' ,null,null,null);
insert into TreeTbl values('13456',null,null,null);
insert into TreeTbl values('19'   ,null,   4,   4);
insert into TreeTbl values('191'  ,null,null,null);
insert into TreeTbl values('1912' ,   1,null,null);
insert into TreeTbl values('19123',null,   9,null);
insert into TreeTbl values('20'   ,null,   1,   9);
insert into TreeTbl values('25'   ,   1,   2,null);
insert into TreeTbl values('251'  ,null,null,null);
insert into TreeTbl values('2513' ,null,null,null);
commit;

SQL

col Key  for a10

select Key,
max(Val2) Keep(Dense_Rank First order by nvl2(Val2,0,1),LV) as C2,
max(Val3) Keep(Dense_Rank First order by nvl2(Val3,0,1),LV) as C3,
max(Val4) Keep(Dense_Rank First order by nvl2(Val4,0,1),LV) as C4
from (select connect_by_root Key as Key,Val2,Val3, Val4,Level as LV
        from TreeTbl a
      Start With not exists(select 1 from TreeTbl b
                             where a.Key = substr(b.Key,1,length(b.Key)-1))
      connect by prior substr(Key,1,length(Key)-1) = Key)
group by Key;

解説

集合関数的な、Last_Value(取得値 ignore nulls)
over(order by SortKey Rows between Unbounded Preceding
                                         and Unbounded Following)
は、
max(取得値) Keep(Dense_Rank First order by nvl2(取得値,0,1),SortKey desc)
で代用できます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
create table LastTes(GID,SortKey,Val) as
select 'AAA',10, 200 from dual union all
select 'AAA',20,null from dual union all
select 'AAA',30, 300 from dual union all
select 'AAA',40,null from dual union all
select 'AAA',50, 100 from dual union all
select 'AAA',60,null from dual union all
select 'BBB',10,null from dual union all
select 'BBB',20,null from dual union all
select 'BBB',30,null from dual union all
select 'CCC',10, 500 from dual union all
select 'CCC',20,null from dual union all
select 'CCC',30,null from dual;

select distinct GID,
Last_Value(Val ignore nulls) over(partition by GID order by SortKey
                                  Rows between Unbounded Preceding
                                           and Unbounded Following) as LastVal
from LastTes
order by GID;

select GID,
max(Val) Keep(Dense_Rank First order by nvl2(Val,0,1),SortKey desc) as LastVal
  from LastTes
group by GID
order by GID;

GID  LastVal
---  -------
AAA      100
BBB     null
CCC      500

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
集合関数的でない、行ごとのLast_Value
8-12 Last_Value関数を模倣(ignore nullsあり)

---