9-51 大きさNのリージョンを求める(境界なし)

SQLパズル

Seatsテーブル
Seat  Status
----  ------
   1    占
   2    占
   3    空
   4    空
   5    空
   6    占
   7    空
   8    空
   9    空
  10    空
  11    空
  12    占
  13    占
  14    空
  15    空

Seatの昇順で、Statusが3つ連続して、空
となるSeatを以下の形式で出力する

出力結果
SeatStart  SeatEnd
---------  -------
   3          5
   7          9
   8         10
   9         11

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

データ作成スクリプト

create table Seats as
select 1 as 座席,'占' as 状態 from dual
union select 2,'占' from dual
union select 3,'空' from dual
union select 4,'空' from dual
union select 5,'空' from dual
union select 6,'占' from dual
union select 7,'空' from dual
union select 8,'空' from dual
union select 9,'空' from dual
union select 10,'空' from dual
union select 11,'空' from dual
union select 12,'占' from dual
union select 13,'占' from dual
union select 14,'空' from dual
union select 15,'空' from dual;

SQL

--■■■分析関数を使わない方法■■■
select a.座席 as SeatStart,b.座席 as SeatEnd
  from Seats a,Seats b
 where a.座席 < b.座席
   and exists(select 1 from Seats c
               where c.座席 between a.座席 and b.座席
              having 3 = all(count(*),count(nullif(c.状態,'占'))));

--■■■分析関数を使う方法■■■
select SeatStart,SeatEnd
from (select 座席 as SeatStart,
      Lead(座席,3-1) over(order by 座席) as SeatEnd,
      count(nullif(状態,'占'))
      over(order by 座席 Rows between current row and (3-1) following) as SeatCount
      from Seats)
where SeatCount = 3;

解説

Count関数は、nullをカウントしないことと、
nullif関数を組み合わせてます。

---

マニュアル(分析関数)(英語)
マニュアル(分析関数)

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

---