5-57 前月が2位以内のデータを出力

SQLパズル

ValTable
Code  yyyymm  Val
----  ------  ---
AAAA  200704  300
BBBB  200704  200
CCCC  200704  100
AAAA  200705  400
BBBB  200705  500
CCCC  200705  600
AAAA  200706  900
BBBB  200706  800
CCCC  200706  700
DDDD  200712  100
EEEE  200712  200
FFFF  200712  300
DDDD  200801  400
EEEE  200801  500
FFFF  200801  600
GGGG  200810  900
GGGG  200812  800

まず、yyyymmごとに、上位2位までを抽出します。

Code  yyyymm  Val  Rank
----  ------  ---  ----
AAAA  200704  300     1
BBBB  200704  200     2
BBBB  200705  500     2
CCCC  200705  600     1
AAAA  200706  900     1
BBBB  200706  800     2
EEEE  200712  200     2
FFFF  200712  300     1
GGGG  200810  900     1
GGGG  200812  800     1

抽出した結果の、Codeが等しい1ヶ月後のデータを出力する。

出力結果
Code  yyyymm  Val
----  ------  ---
AAAA  200705  400
BBBB  200705  500
BBBB  200706  800
CCCC  200706  700
EEEE  200801  500
FFFF  200801  600

データ作成スクリプト

create table ValTable as
select 'AAAA' as Code,200704 as yyyymm,300 as Val from dual
union select 'BBBB',200704,200 from dual
union select 'CCCC',200704,100 from dual
union select 'AAAA',200705,400 from dual
union select 'BBBB',200705,500 from dual
union select 'CCCC',200705,600 from dual
union select 'AAAA',200706,900 from dual
union select 'BBBB',200706,800 from dual
union select 'CCCC',200706,700 from dual
union select 'DDDD',200712,100 from dual
union select 'EEEE',200712,200 from dual
union select 'FFFF',200712,300 from dual
union select 'DDDD',200801,400 from dual
union select 'EEEE',200801,500 from dual
union select 'FFFF',200801,600 from dual
union select 'GGGG',200810,900 from dual
union select 'GGGG',200812,800 from dual;

SQL

--■■■add_months関数を使う方法■■■
select Code,yyyymm,Val
from (select Code,yyyymm,Val,
      case when to_number(to_char(add_months(to_date(to_char(yyyymm),'yyyymm'),-1),'yyyymm'))
              = Lag(yyyymm) over(partition by Code order by yyyymm)
            and Lag(Rank) over(partition by Code order by yyyymm) <= 2
           then 1 else 0 end as WillOut
        from (select Code,yyyymm,Val,
              Rank() over(partition by yyyymm order by Val desc) as Rank
              from ValTable))
where WillOut = 1;

--■■■12進数変換を使う方法■■■
select Code,yyyymm,Val
from (select Code,yyyymm,Val,
      max(rn) over(partition by Code
      order by trunc(yyyymm/100)*12+mod(yyyymm,100)
      range between 1 preceding
                and 1 preceding) as PreRn
      from (select Code,yyyymm,Val,
            Rank() over(partition by yyyymm order by Val desc) as rn
              from ValTable))
where PreRn <=2
order by Code,yyyymm;

解説

add_months関数を使うより、12進数変換を使うほうがシンプルでしょう。