8-37 dense_Rank関数後に整形

SQLパズル

RankTable
Val
----
5000
3000
3000
2975
2850
2450
1600
1500
1350
1250
1250
1100
 950
 800

dense_Rank関数で順位を付けて、下記の形式で出力する。

出力結果
Top3  Next3  Rest
----  -----  ----
5000  2850   1500
3000  2450   1350
3000  1600   1250
2975  null   1250
null  null   1100
null  null    950
null  null    800

SQLクックブックのレシピ14.8を参考にさせていただきました

データ作成スクリプト

create table RankTable(Val) as
select 5000 from dual union all
select 3000 from dual union all
select 3000 from dual union all
select 2975 from dual union all
select 2850 from dual union all
select 2450 from dual union all
select 1600 from dual union all
select 1500 from dual union all
select 1350 from dual union all
select 1250 from dual union all
select 1250 from dual union all
select 1100 from dual union all
select  950 from dual union all
select  800 from dual;

SQL

select max(decode(ColumnID,'Top3' ,Val)) as Top3,
       max(decode(ColumnID,'Next3',Val)) as Next3,
       max(decode(ColumnID,'Rest' ,Val)) as Rest
from (select Val,ColumnID,
             Row_Number() over(partition by ColumnID order by Val desc) as Rn2
      from (select Val,case when Rn1 in (1,2,3) then 'Top3'
                            when Rn1 in (4,5,6) then 'Next3'
                       else 'Rest' end as ColumnID
            from (select Val,dense_Rank() over(order by Val desc) as Rn1
                    from RankTable)))
group by Rn2
order by Rn2;

解説

順を追って考えると分かりやすいでしょう。

---