PostgreSQLの分析関数の衝撃5
（Row_Number関数の応用例）

SQLで旅人算の感覚

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 |