分析関数の衝撃（後編）

SELECT S1.seat   AS start_seat, '～' , S2.seat AS end_seat
  FROM Seats S1, Seats S2
 WHERE S2.seat = S1.seat + (:head_cnt -1)  --始点と終点を決める
   AND NOT EXISTS
          (SELECT *
             FROM Seats S3
            WHERE S3.seat BETWEEN S1.seat AND S2.seat
              AND S3.status <> '空' );

select SeatStart,SeatEnd
from (select seat as SeatStart,
      Lead(seat,3-1) over(order by seat) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows between current row
                    and (3-1) following) as SeatCount
      from Seats)
where SeatCount = 3;

order by seat         --seatの昇順で、
 Rows between         --行の範囲は、
current row           --小さいほうは、現在の行から
  and (3-1) following --大きいほうは、2行後まで

select SeatStart,SeatEnd
from (select seat as SeatStart,
      max(seat)
      over(order by seat
           Rows between 2 following
                    and 2 following) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows between current row
                    and 2 following) as SeatCount
      from Seats) dummy
where SeatCount = 3;

--これでも可
select SeatStart,SeatEnd
from (select seat as SeatStart,
      max(seat)
      over(order by seat
           Rows between 2 following
                    and 2 following) as SeatEnd,
      count(nullif(status,'占'))
      over(order by seat
           Rows 2 following) as SeatCount
      from Seats) dummy
where SeatCount = 3;