7-50 前後の値で分岐

SQLパズル

IDListテーブル
ID  Val
--  ---
 A    1
 A    2
 A    3
 B    4
 B    5
 C    6
 A    7
 A    8

IDListテーブルから、
IDが連続しているIDごとの、Valの最小値と最大値を出力する。

出力結果
ID  MinVal  MaxVal
--  ------  ------
 A       1       3
 B       4       5
 C       6       6
 A       7       8

データ作成スクリプト

create table IDList(
ID  char(1),
Val number(1));

insert into IDList values('A',1);
insert into IDList values('A',2);
insert into IDList values('A',3);
insert into IDList values('B',4);
insert into IDList values('B',5);
insert into IDList values('C',6);
insert into IDList values('A',7);
insert into IDList values('A',8);
commit;

SQL

--■■■Lag関数とLead関数を組み合わせた方法■■■
select ID,Val as MinVal,
decode(LeadID2,ID,LeadVal,Val) as MaxVal
from (select ID,Val,
      Lag(ID)  over(order by Val) as LagID2,
      Lead(ID) over(order by Val) as LeadID2,
      Lead(Val) over(order by Val) as LeadVal
      from (select ID,Val,
            Lag(ID)  over(order by Val) as LagID1,
            Lead(ID) over(order by Val) as LeadID1
            from IDList)
      where LagID1  is null
         or LeadID1 is null
         or (not ID = all(LagID1,LeadID1)))
where LagID2 is null
   or ID !=LagID2;

--■■■上のSQLをドモルガンの法則で変形■■■
select ID,Val as MinVal,
decode(LeadID2,ID,LeadVal,Val) as MaxVal
from (select ID,Val,
      Lag(ID)  over(order by Val) as LagID2,
      Lead(ID) over(order by Val) as LeadID2,
      Lead(Val) over(order by Val) as LeadVal
      from (select ID,Val,
            Lag(ID)  over(order by Val) as LagID1,
            Lead(ID) over(order by Val) as LeadID1
            from IDList)
      where LagID1  is null
         or LeadID1 is null
         or ID != LagID1
         or ID != LeadID1)
where LagID2 is null
   or ID !=LagID2;

--■■■case式とLag関数を組み合わせる方法(10g以降)■■■
select ID,min(Val) as MinVal,max(Val) as MinVal
from (select ID,Val,sum(WillSum) over(order by Val) as MakeGroup
        from (select ID,Val,
              case when lnnvl(ID = Lag(ID) over(order by Val))
                   then 1 else 0 end as WillSum
              from IDList))
group by MakeGroup,ID
order by MakeGroup;

--■■■旅人算の感覚を使う方法■■■
select ID,min(Val) as MinVal,max(Val) as MinVal
from (select ID,Val,
        Row_Number() over(order by Val)
      - Row_Number() over(partition by ID order by Val) as makeGroup
      from IDList)
group by ID,makeGroup
order by min(Val);

解説

Lag関数とLead関数で、前後の値を求めてます。
case式とLag関数を組み合わせてもいいでしょう。

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

---