7-38 連続した範囲の最大最小を取得

SQLパズル

TableA
ColA  ColB
----  ----
   1     1
   1     2
   2     1
   2     2
   2     5
   3     1
   3     5
   4     1
   4     2
   4     3
   4     5

TableAのColAごとに、
連続したColBの中での、最大値と最小値を出力する。

出力結果
ColA  Min  Max
----  ---  ---
   1    1    2
   2    1    2
   2    5    5
   3    1    1
   3    5    5
   4    1    3
   4    5    5

データ作成スクリプト

create table TableA(
ColA number(1),
ColB number(1));

insert into TableA values(1,1);
insert into TableA values(1,2);
insert into TableA values(2,1);
insert into TableA values(2,2);
insert into TableA values(2,5);
insert into TableA values(3,1);
insert into TableA values(3,5);
insert into TableA values(4,1);
insert into TableA values(4,2);
insert into TableA values(4,3);
insert into TableA values(4,5);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select distinct ColA,
(select max(b.ColB) from TableA b
  where b.ColA  = a.ColA
    and b.ColB <= a.ColB
    and not exists(select 1 from TableA c
                    where c.ColA = b.ColA
                      and c.ColB = b.ColB-1)) as min,
(select min(b.ColB) from TableA b
  where b.ColA  = a.ColA
    and b.ColB >= a.ColB
    and not exists(select 1 from TableA c
                    where c.ColA = b.ColA
                      and c.ColB = b.ColB+1)) as max
from TableA a
order by ColA,min;

--■■■階層問い合わせとConnect_by_Rootを使う方法(10g以降)■■■
select ColA,Connect_by_Root ColB as min,ColB as max
from TableA a
where Connect_by_IsLeaf = 1
start with not exists(select 1 from TableA b
                       where b.ColA = a.ColA
                         and b.ColB = a.ColB-1)
connect by prior ColA = ColA
       and prior ColB = ColB-1
order by ColA,min;

■■■分析関数とignore nullsを使う方法(10g以降)■■■
select distinct ColA,
Last_Value( case when ExistPrev = 0 then ColB end ignore nulls)
over(order by ColA,ColB) as Min,
First_Value(case when ExistNext = 0 then ColB end ignore nulls)
over(order by ColA,ColB Rows between Current Row and Unbounded Following) as Max
from (select ColA,ColB,LagColA,LeadColA,LagColB,LeadColB,
      case when ColA = LagColA  and ColB -1 = LagColB
           then 1 else 0 end as ExistPrev,
      case when ColA = LeadColA and ColB +1 = LeadColB
           then 1 else 0 end as ExistNext
      from (select ColA,ColB,
            Lag(ColA)  over(order by ColA,ColB) as LagColA,
            Lead(ColA) over(order by ColA,ColB) as LeadColA,
            Lag(ColB)  over(partition by ColA order by ColB) as LagColB,
            Lead(ColB) over(partition by ColA order by ColB) as LeadColB
            from TableA))
order by ColA,min;

--■■■case式とLag関数を使う方法■■■
select ColA,min(ColB) as min,max(ColB) as max
from (select ColA,ColB,
      sum(WillSum) over(partition by ColA order by ColB) as gcode
        from (select ColA,ColB,
              case when Lag(ColB) over(partition by ColA order by ColB) = ColB-1
                   then 0 else 1 end as WillSum
              from TableA))
group by ColA,gcode
order by ColA,gcode;

--■■■旅人算の感覚で一般化する方法■■■
select ColA,min(ColB) as min,max(ColB) as max
from (select ColA,ColB,
      Row_Number() over(partition by ColA order by ColB) - ColB as willGroup
        from TableA)
group by ColA,willGroup
order by ColA,min;

解説

階層問い合わせとConnect_by_Rootを使う方法では、
葉と根の値を出力してます。

旅人算の感覚で一般化する方法もあります。

9-52 最大のリージョンを求める(境界なし)

---