9-54 最大のリージョンを求める(境界あり)

SQLパズル

Seats4テーブル
Row_ID  Seat  Status
------  ----  ------
   A      1     占
   A      2     占
   A      3     空
   A      4     空
   A      5     空
   B      6     占
   B      7     空
   B      8     空
   B      9     空
   B     10     空
   C     11     空
   C     12     占
   C     13     占
   C     14     空
   C     15     空
   D     16     空
   D     17     空
   D     18     空
   D     19     空

Row_IDが同じで、Seatの昇順で、
Statusが最も長く連続して空なSeatを
以下の形式で出力する

出力結果
Row_ID  SeatStart  SeatEnd  SeatCount
------  ---------  -------  ---------
   B       7         10         4
   D      16         19         4

プログラマのためのSQL第2版の24章[リージョン、ラン、シーケンス]を参考にさせていただきました

データ作成スクリプト

create table Seats4 as
select 'A' as Row_ID,1 as 座席,'占' as 状態 from dual
union select 'A', 2,'占' from dual
union select 'A', 3,'空' from dual
union select 'A', 4,'空' from dual
union select 'A', 5,'空' from dual
union select 'B', 6,'占' from dual
union select 'B', 7,'空' from dual
union select 'B', 8,'空' from dual
union select 'B', 9,'空' from dual
union select 'B',10,'空' from dual
union select 'C',11,'空' from dual
union select 'C',12,'占' from dual
union select 'C',13,'占' from dual
union select 'C',14,'空' from dual
union select 'C',15,'空' from dual
union select 'D',16,'空' from dual
union select 'D',17,'空' from dual
union select 'D',18,'空' from dual
union select 'D',19,'空' from dual;

SQL

--■■■lnnvl述語を使う方法■■■
select Row_ID,start_seat,end_seat,seat_cnt
from (select Row_ID,
      min(座席) as start_seat,
      max(座席) as end_seat,
      count(*)  as seat_cnt,
      max(count(*)) over() as maxseat_cnt
      from (select Row_ID,座席,
            sum(willSum) over(partition by Row_ID order by 座席) as makeGroup
              from (select Row_ID,座席,状態,
                    case when lnnvl(Lag(状態) over(partition by Row_ID order by 座席) = '空')
                         then 1 else 0 end as willSum
                    from Seats4)
             where 状態 = '空')
      group by Row_ID,makeGroup)
where seat_cnt = maxseat_cnt;

--■■■lnnvl述語を使わない方法■■■
select Row_ID,start_seat,end_seat,seat_cnt
from (select Row_ID,
      min(座席) as start_seat,
      max(座席) as end_seat,
      count(*)  as seat_cnt,
      max(count(*)) over() as maxseat_cnt
      from (select Row_ID,座席,
            sum(willSum) over(partition by Row_ID order by 座席) as makeGroup
              from (select Row_ID,座席,状態,
                    case when Lag(状態) over(partition by Row_ID order by 座席) = '空'
                         then 0 else 1 end as willSum
                    from Seats4)
             where 状態 = '空')
      group by Row_ID,makeGroup)
where seat_cnt = maxseat_cnt;

--■■■旅人算の感覚を使う方法■■■
select Row_ID,start_seat,end_seat,seat_cnt
from (select Row_ID,
      min(座席) as start_seat,
      max(座席) as end_seat,
      count(*)  as seat_cnt,
      max(count(*)) over() as maxseat_cnt
      from (select Row_ID,座席,状態,
              Row_Number() over(partition by Row_ID order by 座席)
            - Row_Number() over(partition by Row_ID,状態 order by 座席) as makeGroup
            from Seats4)
      where 状態 = '空'
      group by Row_ID,makeGroup)
where seat_cnt = maxseat_cnt;

解説

空が、連続した状態かを調べて、
グループ化してます。

lnnvl述語を使う方法のほうが分かりやすいと思います。

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

---

CodeZine：SQLで数列を扱う
CodeZine：分析関数の衝撃（後編）

---