9-59 各列の最初の非null値をまとめる

SQLパズル

Foobar
lnl  color  Len   width  hgt
---  -----  ----  -----  ----
  1  RED       8     10    12
  2  null   null   null    20
  3  null      9     25    25
  4  BLUE   null     67  null
  5  GRAY   null   null  null

lnlの降順に、各列の値を見ていって、
それぞれの最初の非null値をまとめて出力する。

出力結果
color  Len   width  hgt
-----  ----  -----  ----
GRAY      9     67    25

SQLパズル 第2版のパズル53 [テーブルを列ごとに折りたたむ] を参考にさせていただきました

データ作成スクリプト

create table Foobar(
lnl   number(1) primary key,
color varchar2(4),
Len   number(1),
width number(2),
hgt   number(2));

insert into Foobar values(1,'RED' ,   8,  10,  12);
insert into Foobar values(2,null  ,null,null,  20);
insert into Foobar values(3,null  ,   9,  25,  25);
insert into Foobar values(4,'BLUE',null,  67,null);
insert into Foobar values(5,'GRAY',null,null,null);
commit;

SQL

--■■■Keepを使う方法■■■
select
max(color) Keep (Dense_Rank Last order by nvl2(color,lnl,null) nulls first) as color,
max(Len)   Keep (Dense_Rank Last order by nvl2(Len  ,lnl,null) nulls first) as Len,
max(width) Keep (Dense_Rank Last order by nvl2(width,lnl,null) nulls first) as width,
max(hgt)   Keep (Dense_Rank Last order by nvl2(hgt  ,lnl,null) nulls first) as hgt
  from Foobar;

--■■■Last_Valueとignore nullsを使う方法(10g以降)■■■
select distinct
Last_Value(color ignore nulls)
over(order by lnl Rows between Unbounded Preceding
                           and Unbounded Following) as color,
Last_Value(Len ignore nulls)
over(order by lnl Rows between Unbounded Preceding
                           and Unbounded Following) as Len,
Last_Value(width ignore nulls)
over(order by lnl Rows between Unbounded Preceding
                           and Unbounded Following) as width,
Last_Value(hgt ignore nulls)
over(order by lnl Rows between Unbounded Preceding
                           and Unbounded Following) as hgt
  from Foobar;

--■■■Last_Valueとignore nullsを模倣する方法■■■
select
max(decode(lnl,TargetColor,Color)) as Color,
max(decode(lnl,TargetLen,Len))     as Len,
max(decode(lnl,TargetWidth,width)) as width,
max(decode(lnl,TargetHgt,hgt))     as hgt
from (select lnl,color,Len,width,hgt,
      max(nvl2(color,lnl,null)) over() as TargetColor,
      max(nvl2(Len  ,lnl,null)) over() as TargetLen,
      max(nvl2(width,lnl,null)) over() as TargetWidth,
      max(nvl2(hgt  ,lnl,null)) over() as TargetHgt
      from Foobar);

解説

10g以降なら、
Last_Valueとignore nullsを使う方法が分かりやすいと思います。

8-12 Last_Value関数を模倣(ignore nullsあり)

---