2. 旅人算の感覚を応用する（3人旅人算）
次は、3人旅人算の感覚を応用したSQLについてです。『PostgreSQLの分析関数の衝撃3』の「3. 最大何人まで座れますか？」では、下記のselect文で、case式でwillSumを求めて、willSumの累計でグループ化しました。
PostgreSQLの分析関数の衝撃3で使用した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;
旅人算の感覚（3人旅人算）を使って書き換えてみましょう。まずは、テーブルのデータと、出力結果を考えます。
Seats3
| seat | status |
| 1 | 占 |
| 2 | 空 |
| 3 | 空 |
| 4 | 空 |
| 5 | 空 |
| 6 | 占 |
| 7 | 空 |
| 8 | 占 |
| 9 | 空 |
| 10 | 空 |
出力結果
| start_seat | end_seat | seat_cnt |
| 2 | 5 | 4 |
答えは下記となります。
旅人算の感覚（3人旅人算）を使って書き換えた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,status,
Row_Number() over(order by seat)
-Row_Number() over(partition by status order by seat) as makeGroup
from Seats3) a
where status = '空'
group by makeGroup) a
where seat_cnt = maxseat_cnt;
1進む条件が異なる3人の旅人（旅人A,旅人B,旅人C）が数直線の原点からプラス方向に同時にスタートしたとして、
毎回1進む旅人Aの位置
Row_Number() over(order by 座席)
状態='空'なら1進む旅人Bの位置
Row_Number() over(partition by 状態 order by 座席)
状態='占'なら1進む旅人Cの位置
Row_Number() over(partition by 状態 order by 座席)
と考えてます。
順を追って説明していくと、最も内側のselect文で、2つのRow_Number関数の結果の差を求め、旅人Aと旅人Bの位置の差、および、旅人Aと旅人Cの位置の差を求めています。
旅人Aと旅人Bの位置の差は、広義の単調増加（大きくなるかそのまま）であると分かります。状態が連続して'空'であるなら、旅人Aと旅人Bの位置の差はそのままで、連続して'空'でなくなったら旅人Aと旅人Bの位置の差が大きくなります。そして、旅人Aとの位置の差を列別名makeGroupとして求めてます。
そして、次に内側のselect文のwhere句で状態 = '空' を指定し、group by makeGroupでグループ化して、window関数のmax関数でcount(*)が最大のグループを求め、最も外側のselect文のwhere句で使用しています。SQLのイメージは下記となります。旅人Aと旅人Bの位置の差に注目すると分かりやすいでしょう。
SQLのイメージ
座席 状態 3人の旅人の位置 ---- ---- - 0- 1- 2- 3- 4- 5- 6- 7- 8- 9-10- 1 占 |B |AC| | | | | | | | | | 2 空 | |BC|A | | | | | | | | | 3 空 | | C|B |A | | | | | | | | 4 空 | | C| |B |A | | | | | | | 5 空 | | C| | |B |A | | | | | | 6 占 | | | C| |B | |A | | | | | 7 空 | | | C| | |B | |A | | | | 8 占 | | | | C| |B | | |A | | | 9 空 | | | | C| | |B | | |A | | 10 空 | | | | C| | | |B | | |A |
