PostgreSQLの分析関数の衝撃3
（数列を扱うSQLとrange指定）

Lag関数とLead関数の使用例

2. 「3人なんですけど座れますか？」
その2：行の折り返しも考慮する

　次に人数分の空席を探すSQL（行の折り返しも考慮する）についてです。『SQLで数列を扱う』では、以下のSQLが提示されています。

人数分の空席を探す　その2：行の折り返しも考慮する

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

　これをwindow関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。

Seats2

seat	Row_ID	status
1	A	占
2	A	占
3	A	空
4	A	空
5	A	空
6	B	占
7	B	占
8	B	空
9	B	空
10	B	空
11	C	空
12	C	空
13	C	空
14	C	占
15	C	空

出力結果

Row_ID	SeatStart	SeatEnd
A	3	5
B	8	10
C	11	13

　答えは、下記となります。

window関数で書き換えたSQL1

select Row_ID,SeatStart,SeatEnd
from (select Row_ID,seat as SeatStart,
      Lead(seat,2) over W1 as SeatEnd,
      case when status='空' then 1 else 0 end+
      case when Lead(status)   over W1 ='空'
           then 1 else 0 end+
      case when Lead(status,2) over W1 ='空'
           then 1 else 0 end as SeatCount
        from Seats2
      window W1 as (partition by row_id order by seat)) a
 where SeatCount = 3
order by SeatStart;

window関数で書き換えたSQL2

select Row_ID,SeatStart,SeatEnd
from (select Row_ID,seat as SeatStart,
      Lead(seat,2) over W1 as SeatEnd,
          status='空'
      and Lead(status='空')   over W1
      and Lead(status='空',2) over W1 as willOut
        from Seats2
      window W1 as (partition by row_id order by seat)) a
 where willOut
order by SeatStart;

　インラインビューの中のselect文にstatus列を追加した、SQLのイメージは下記となります。

SQLのイメージ

　SQL自体は前問で使ったwindow関数に、partition by句でRow_IDを指定して、Row_IDでパーティションを切っただけとなります。SQLのイメージを比較すると分かりやすいと思います。

　Lead関数を何度も使いたくないのであれば、window関数を使わない下記のSQLでもいいです。

window関数を使わないSQL1

select a.Row_ID,a.seat as start_seat,max(b.seat) as end_seat
  from Seats2 a,Seats2 b
 where a.Row_ID = b.Row_ID
   and b.seat between a.seat and a.seat+(3-1)
group by a.Row_ID,a.seat
having count(nullif(b.status,'占')) = 3
order by a.seat;

window関数を使わないSQL2

select Row_ID,seat as start_seat,seat+(3-1) as end_seat
  from Seats2 a
 where exists(select 1 from Seats2 b
               where b.Row_ID = a.Row_ID
                 and b.seat between a.seat and a.seat+(3-1)
              having count(nullif(b.status,'占')) = 3)
order by seat;