3人なんですけど座れますか? - その2:行の折り返しも考慮する
次に人数分の空席を探すSQL(行の折り返しも考慮する)についてです。「SQLで数列を扱う」では、以下のSQLが提示されています。
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));
これを分析関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
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 |
手続き型の言語であれば、
- row_idの昇順、seatの昇順にソート
- 各値を、配列に保存
- 配列の添字の最小値からループ
- 配列の添字を元に、row_idが等しい、3つの連続した空席を探す
といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。
select Row_ID,SeatStart,SeatEnd from (select Row_ID, seat as SeatStart, Lead(seat,3-1) over(partition by Row_ID order by seat) as SeatEnd, count(nullif(status,'占')) over(partition by Row_ID order by seat Rows between current row and (3-1) following) as SeatCount from Seats2) where SeatCount = 3;
インラインビューの中のselect
文にstatus列を追加した、SQLのイメージはこうなります。
SQL自体は前の、「3人なんですけど座れますか? その1:行の折り返しを考慮しない」で使った分析関数に、partition by
句で「Row_ID」を指定して、「Row_ID」でパーティションを切っただけとなります。SQLのイメージを比較すると分かりやすいと思います。
DB2では、下記のSQLとなります。
select Row_ID,SeatStart,SeatEnd from (select Row_ID, seat as SeatStart, max(seat) over(partition by Row_ID order by seat Rows between 2 following and 2 following) as SeatEnd, count(nullif(status,'占')) over(partition by Row_ID order by seat Rows between current row and 2 following) as SeatCount from Seats2) dummy where SeatCount = 3; --これでも可 select Row_ID,SeatStart,SeatEnd from (select Row_ID, seat as SeatStart, max(seat) over(partition by Row_ID order by seat Rows between 2 following and 2 following) as SeatEnd, count(nullif(status,'占')) over(partition by Row_ID order by seat Rows 2 following) as SeatCount from Seats2) dummy where SeatCount = 3;