キムラデービーブログ

本エントリはFirebird Advent Calendar 2018 の七日目です。今年も参加者募集!

Firebirdの分析関数の衝撃(8) 「3人なんですけど座れますか？」(行の折り返しを考慮する)

2. 「3人なんですけど座れますか？」その2：行の折り返しも考慮する

ミックさんの『SQLで数列を扱う』では、最初折り返しを考慮しない、つまり1席から15席までを連続した席として
扱っていましたが、映画館やコンサートホールでは、一定の幅で折り返ししています。この場合数値としては連続していても列で分断される場合があり「折り返しを考慮する」とは、それを考慮したものになります。たとえば今回の場合は以下のようになり、結果として前回の結果から(9, 10, 11)を省いたものになります。

A列: 1 2 3 4 5
B列: 6 7 8 9 10
C列:11 12 13 14 15

クエリとしては:haed_cntに3を入れるのは前回と同じで、そのクエリに条件「OR S3.row_id <> S1.row_id」を追加したものになります。
実行前にSeatsからSeats2を作っておきましょう。列名をいれるRow_ID列を追加作成し、その値'A','B','C'をseat番号を割り算してそれぞれのアスキーキャラクタを生成するascii_char関数で生成しています。

create table Seats2(seat int,Row_ID char(2), status char(2));
insert into Seats2 select seat,ascii_char(65+(seat-1)/5),status from Seats;
commit;

SELECT S1.seat AS start_seat, '〜' , S2.seat AS end_seat
FROM Seats2 S1, Seats2 S2
WHERE S2.seat = S1.seat + (3 -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));

START_SEAT CONSTANT END_SEAT
============ ======== ============
3 〜 5
7 〜 9
8 〜 10

さて残念ながら、このページのwindow関数で書き換えたSQL1,2ともにFirebirdでは動作しません。これは「名前つきウインドウ(named windows)」がFirebird 3.0は未サポートのためです。(4.0にて実装予定)そのため「 window W1 as (partition by row_id order by seat)」のようにW1を扱うことができません。

Window Functions

window関数を使わないSQL1,2は動作します。

ROW_ID START_SEAT END_SEAT
====== ============ =====================
A 3 5
B 7 9
B 8 10