10-182 コードごとの、Top2の行の値を出力

SQLパズル

deriveTop2
Code  DateX       Value
----  ----------  -----
 ABD  2007/09/15     23
 ABD  2007/09/16     54
 ABD  2007/09/17     22
 KLO  2007/01/01     72
 KLO  2007/04/20     89
 KLO  2007/04/21     91
 XYZ  2007/01/11     30
 XYZ  2007/01/22     20
 XYZ  2007/01/30     10
 YYY  2007/12/12     11
 ZZZ  2007/11/21     66
 ZZZ  2007/11/23     22
 ZZZ  2007/11/25     33

コードごとの、Top2の行の値を出力する。

出力結果
Code  latestData  secondLatestData
----  ----------  ----------------
 ABD          22                54
 KLO          91                89
 XYZ          10                20
 YYY          11              null
 ZZZ          33                22

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

データ作成スクリプト

create table deriveTop2 as
select 'ABD' as Code,to_date('2007/09/15','yyyy/mm/dd') as DateX,23 as Value from dual
union select 'ABD',to_date('2007/09/16','yyyy/mm/dd'),54 from dual
union select 'ABD',to_date('2007/09/17','yyyy/mm/dd'),22 from dual
union select 'KLO',to_date('2007/01/01','yyyy/mm/dd'),72 from dual
union select 'KLO',to_date('2007/04/20','yyyy/mm/dd'),89 from dual
union select 'KLO',to_date('2007/04/21','yyyy/mm/dd'),91 from dual
union select 'XYZ',to_date('2007/01/11','yyyy/mm/dd'),30 from dual
union select 'XYZ',to_date('2007/01/22','yyyy/mm/dd'),20 from dual
union select 'XYZ',to_date('2007/01/30','yyyy/mm/dd'),10 from dual
union select 'YYY',to_date('2007/12/12','yyyy/mm/dd'),11 from dual
union select 'ZZZ',to_date('2007/11/21','yyyy/mm/dd'),66 from dual
union select 'ZZZ',to_date('2007/11/23','yyyy/mm/dd'),22 from dual
union select 'ZZZ',to_date('2007/11/25','yyyy/mm/dd'),33 from dual;

SQL

--■■■分析関数を使う方法1■■■
select Code,
max(decode(Rank,1,Value)) as "latestData",
max(decode(Rank,2,Value)) as "secondLatestData"
from (select Code,Value,
      Row_Number() over(partition by Code order by DateX desc) as Rank
        from deriveTop2)
where Rank <= 2
group by Code
order by Code;

--■■■分析関数を使う方法2■■■
select distinct Code,
Last_Value(Value) over(partition by Code order by DateX
Rows between Unbounded Preceding and Unbounded Following) as "latestData",
Last_Value(LagValue) over(partition by Code order by DateX
Rows between Unbounded Preceding and Unbounded Following) as "secondLatestData"
from (select Code,DateX,Value,
      Lag(Value) over(partition by Code order by DateX) as LagValue
        from deriveTop2)
order by Code;

--■■■分析関数を使う方法3■■■
select Code,
Value as "latestData",
LagValue as "secondLatestData"
from (select Code,DateX,Value,
      Lag(Value) over(partition by Code order by DateX) as LagValue,
      max(DateX) over(partition by Code) as MaxDateX
        from deriveTop2)
where DateX=MaxDateX
order by Code;

--■■■分析関数を使わない方法■■■
select Code,
Value as "latestData",
(select b.Value
   from deriveTop2 b
  where b.Code = a.Code
    and b.DateX = (select max(c.DateX)
                     from deriveTop2 c
                    where c.Code = b.Code
                      and c.DateX < a.DateX)) as "secondLatestData"
  from deriveTop2 a
 where DateX = (select max(b.DateX)
                  from deriveTop2 b
                 where b.Code = a.Code)
 order by Code;

--■■■model句を使う方法(10g以降)■■■
select Code,latestData,secondLatestData
  from deriveTop2
 model RETURN UPDATED ROWS
 partition by (Code)
 dimension by (Row_Number() over(partition by Code order by DateX desc) as rn)
 measures(Value as latestData,Value as secondLatestData)
 rules(secondLatestData[1] = secondLatestData[2])
order by Code;

解説

分析関数を使う方法1が、一番分かりやすいでしょう。

---