3. 「最大何人まで座れますか?」
次に最大何人まで座れるかを求めるSQLについてです。『SQLで数列を扱う』では、以下のSQLが提示されています。
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の否定
SELECT start_seat, '~', end_seat, seat_cnt FROM Sequences WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);
これらをwindow
関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。
seat | status |
1 | 占 |
2 | 空 |
3 | 空 |
4 | 空 |
5 | 空 |
6 | 占 |
7 | 空 |
8 | 占 |
9 | 空 |
10 | 空 |
start_seat | end_seat | seat_cnt |
2 | 5 | 4 |
答えは、下記となります。
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
文の結果から解説していきます。
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という列別名の通り、合計(累計)を求めるのに使います。
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
句で使いグループ化します。
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と同じような考え方です。
start_seat | end_seat | seat_cnt |
2 | 5 | 4 |
where
句でseat_cnt=maxSeat_cnt
の行を抽出してます。
まとめると、最初にcase
式で、seatの昇順でstatus='空'
になった(コントロールブレイクした)行を求めて、willSumとして累計を求めるのが1つめのポイントで、willSumの累計でグループ化してから集合関数とwindow
関数を使うのが2つめのポイントとなります。
ちなみに別解として、『分析関数の衝撃5 (総集編)』で扱った旅人算の感覚を使ってもよいです。