5-38 Last_Value関数

SQLパズル

Aテーブル      Bテーブル
Code  Name    Code  Name2      EndDate
----  ----    ----  -----   ----------
0001  AAAA    0001   GGGG   2004/01/01
0002  BBBB    0001   HHHH   2004/02/02
0003  CCCC    0001   IIII   2004/03/03
0004  DDDD    0002   JJJJ   2004/01/01
0005  EEEE    0002   KKKK   2004/03/03
              0005   LLLL   2004/02/02

AテーブルのCodeに紐づくデータを
Bテーブルから取得する(Dateが最大のデータと外部結合する)

出力結果
Code  Name  Name2
----  ----  -----
0001  AAAA   IIII
0002  BBBB   KKKK
0003  CCCC   null
0004  DDDD   null
0005  EEEE   LLLL

データ作成スクリプト

create Table Aテーブル(
Code char(4),
Name char(4),
primary key(Code));

create Table Bテーブル(
Code    char(4),
Name2   char(4),
EndDate date,
primary key(Code,EndDate));

insert into Aテーブル(Code,Name) values('0001','AAAA');
insert into Aテーブル(Code,Name) values('0002','BBBB');
insert into Aテーブル(Code,Name) values('0003','CCCC');
insert into Aテーブル(Code,Name) values('0004','DDDD');
insert into Aテーブル(Code,Name) values('0005','EEEE');
insert into Bテーブル(Code,Name2,EndDate) values('0001','GGGG',to_date('20040101','yyyymmdd'));
insert into Bテーブル(Code,Name2,EndDate) values('0001','HHHH',to_date('20040202','yyyymmdd'));
insert into Bテーブル(Code,Name2,EndDate) values('0001','IIII',to_date('20040303','yyyymmdd'));
insert into Bテーブル(Code,Name2,EndDate) values('0002','JJJJ',to_date('20040101','yyyymmdd'));
insert into Bテーブル(Code,Name2,EndDate) values('0002','KKKK',to_date('20040303','yyyymmdd'));
insert into Bテーブル(Code,Name2,EndDate) values('0005','LLLL',to_date('20040202','yyyymmdd'));
commit;

SQL

--■■■Last_Value関数を使う方法■■■
select Code,Name,
(select distinct
 Last_Value(b.Name2)
 over(order by EndDate Rows between Unbounded Preceding and Unbounded Following) as Name2
from Bテーブル b
where b.Code=a.Code) as Name2
from Aテーブル a
order by Code;

--■■■Row_Number関数を使う方法■■■
select a.Code,a.Name,c.Name2
from Aテーブル a,
(select b.Code,b.Name2,
 Row_Number() over(partition by b.Code order by b.EndDate desc) as Rank
 from Bテーブル b) c
where a.Code=c.Code(+)
  and c.Rank(+) = 1
order by a.Code;

--■■■相関サブクエリを使う方法■■■
select Code,Name,
(select b.Name2 from Bテーブル b
  where b.Code=a.Code
    and b.EndDate = (select max(c.EndDate) from Bテーブル c
                      where c.Code=b.Code)) as Name2
from Aテーブル a
order by Code;

解説

Last_Value関数を使うと、ソートした最後の行の値を取得できます。