5-60 having句でrank within

SQLパズル

dayTable
day1      Seq  Val
--------  ---  ----
20071201    1  AAAA
20071201    2  BBBB   ←出力対象
20071201    3  CCCC   ←出力対象
20071202    1  DDDD   ←出力対象
20071202    2  EEEE   ←出力対象
20071203    1  FFFF   ←出力対象
20071203    2  GGGG   ←出力対象
20071203    3  HHHH   ←出力対象
20071203    4  IIII   ←出力対象
20071204    1  JJJJ   ←出力対象
20071205    1  KKKK   ←出力対象
20071205    2  LLLL   ←出力対象
20071205    3  MMMM   ←出力対象
20071205    4  NNNN
20071206    1  OOOO

20071201のSeqが2の行から
20071205のSeqが3の行までのデータを出力する。

データ作成スクリプト

create table dayTable(day1,Seq,Val) as
select 20071201,1,'AAAA' from dual union
select 20071201,2,'BBBB' from dual union
select 20071201,3,'CCCC' from dual union
select 20071202,1,'DDDD' from dual union
select 20071202,2,'EEEE' from dual union
select 20071203,1,'FFFF' from dual union
select 20071203,2,'GGGG' from dual union
select 20071203,3,'HHHH' from dual union
select 20071203,4,'IIII' from dual union
select 20071204,1,'JJJJ' from dual union
select 20071205,1,'KKKK' from dual union
select 20071205,2,'LLLL' from dual union
select 20071205,3,'MMMM' from dual union
select 20071205,4,'NNNN' from dual union
select 20071206,1,'OOOO' from dual;

SQL

--■■■3つの条件に分けて、orを使う方法■■■
select day1,Seq,Val
  from dayTable
 where (day1 = 20071201 and 2 <= Seq)
    or day1 between 20071202 and 20071204
    or (day1 = 20071205 and Seq <= 3);

--■■■Rank関数を使う方法1■■■
select day1,Seq,Val
from (select day1,Seq,Val,
      Rank() over(order by day1,Seq) as Rank,
      sum(case when day1 < 20071201
                 or day1 = 20071201 and Seq <= 2
               then 1 else 0 end) over() as CompRank1,
      sum(case when day1 < 20071205
                 or day1 = 20071205 and Seq <= 3
               then 1 else 0 end) over() as CompRank2
        from dayTable)
where Rank between CompRank1 and CompRank2;

--■■■Rank関数を使う方法2■■■
select day1,Seq,Val
from (select day1,Seq,Val,Rank,
      max(case when day1 = 20071201 and Seq = 2 then Rank end) over() as CompRank1,
      max(case when day1 = 20071205 and Seq = 3 then Rank end) over() as CompRank2
      from (select day1,Seq,Val,
            Rank() over(order by day1,Seq) as Rank
              from dayTable))
where Rank between CompRank1 and CompRank2;

--■■■having句でrank withinを使う方法■■■
select day1,Seq,Val
  from dayTable a
 where exists(select 1
                from dayTable b
              having rank(a.day1,a.Seq) within group(order by b.day1,b.Seq)
             between rank(20071201,2)   within group(order by b.day1,b.Seq)
                 and rank(20071205,3)   within group(order by b.day1,b.Seq));

解説

7-40 指定した範囲のデータを取得
の類題です。

having句でrank withinを使う方法が分かりやすいと思います。

---