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

Lag関数とLead関数の使用例

3. 「最大何人まで座れますか？」

　次に最大何人まで座れるかを求めるSQLについてです。『SQLで数列を扱う』では、以下のSQLが提示されています。

第1段階：すべてのシーケンスを保持するビューを作る。

CREATE VIEW Sequences (start_seat, end_seat, seat_cnt) AS
SELECT S1.seat  AS start_seat,
       S2.seat  AS end_seat,
       S2.seat - S1.seat + 1 AS seat_cnt
  FROM Seats3 S1, Seats3 S2
 WHERE S1.seat <= S2.seat  --ステップ1：始点と終点の組み合わせを作る
   AND NOT EXISTS 
      --ステップ2：シーケンス内のすべての行が満たすべき条件を記述する
       (SELECT *
          FROM Seats3 S3
         WHERE (     S3.seat BETWEEN S1.seat AND S2.seat 
                 AND S3.status <> '空')  --条件1の否定
            OR  (S3.seat = S2.seat + 1 AND S3.status = '空' )
                                                         --条件2の否定
            OR  (S3.seat = S1.seat - 1 AND S3.status = '空' ));
                                                         --条件3の否定

第2段階：最大のシーケンスを求める

SELECT start_seat, '～', end_seat, seat_cnt
  FROM Sequences
 WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);

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

Seats3

seat	status
1	占
2	空
3	空
4	空
5	空
6	占
7	空
8	占
9	空
10	空

出力結果

start_seat	end_seat	seat_cnt
2	5	4

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

window関数で書き換えたSQL

select start_seat,end_seat,seat_cnt
from (select min(seat) as start_seat,
      max(seat) as end_seat,
      count(*) as seat_cnt,
      max(count(*)) over() as maxSeat_cnt
      from (select seat,sum(willSum) over(order by seat) as makeGroup
            from (select seat,status,
                  case when Lag(status) over(order by seat) = '空'
                       then 0 else 1 end as willSum
                  from Seats3) a
            where status = '空') a
      group by makeGroup) a
where seat_cnt = maxSeat_cnt;

　4つのselect文がありますが、最も内側のselect文の結果から解説していきます。

最も内側のselect文の結果

seat	status	willSum
1	占	1
2	空	1
3	空	0
4	空	0
5	空	0
6	占	0
7	空	1
8	占	0
9	空	1
10	空	0

　Lag関数を使ってseatの昇順での1行前のstatusを求めて、case式でそれが空ならば0、そうでなければ1とし、willSumを列別名としてます。willSumという列別名の通り、合計（累計）を求めるのに使います。

次に内側のselect文の結果

seat	makeGroup
2	1
3	1
4	1
5	1
7	2
9	3
10	3

　where句でstatus='空'の行を抽出して、window関数のsum関数でseatの昇順でのwillSumの累計を求め、makeGroupを列別名としてます。makeGroupという列別名の通り、group by句で使いグループ化します。

次に内側のselect文の結果

start_seat	end_seat	seat_cnt	maxSeat_cnt
2	5	4	4
7	7	1	4
9	10	2	4

　makeGroupでグループ化して、集合関数のmin関数でstart_seatを求め、集合関数のmax関数でend_seatを求め、集合関数のcount(*)でseat_cntを求め、window関数のmax関数でcount(*)の最大値を求めてます。

　『PostgreSQLの分析関数の衝撃1』で、最頻値（モード）を求めたSQLと同じような考え方です。

最も外側のselect文の結果

start_seat	end_seat	seat_cnt
2	5	4

　where句でseat_cnt=maxSeat_cntの行を抽出してます。

　まとめると、最初にcase式で、seatの昇順でstatus='空'になった（コントロールブレイクした）行を求めて、willSumとして累計を求めるのが1つめのポイントで、willSumの累計でグループ化してから集合関数とwindow関数を使うのが2つめのポイントとなります。

　ちなみに別解として、『分析関数の衝撃5 （総集編）』で扱った旅人算の感覚を使ってもよいです。