トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-298 ミニミニ予約システムその1

SQLパズル

部屋テーブル
部屋ID  許容人数
------  --------
   101         1
   102         1
   103         1
   104         1
   201         2
   202         2

仮予約テーブル
予約ID  到着予定日   出発予定日  予約人数   予約完了フラグ   キャンセルフラグ
------  ----------  ----------  --------  --------------  ----------------
     1  2003/08/01  2003/08/03         2            null              null
     2  2003/08/02  2003/08/03         2            null              null
     3  2003/08/03  2003/08/04         2            null              null
     4  2003/08/01  2003/08/04         2            null              null
     5  2003/08/04  2003/08/08         3            null              null
     6  2003/08/04  2003/08/08         1            null              null

(1)
出来るだけ「2人は2人部屋に1人は1人部屋に」というように割り当てる。
(候補となる部屋が複数あったら、部屋IDの小さいほうを優先して割り当てる)
優先順位は予約IDの古いものからとする。
(2)
上記で予約状況が作成できたら仮予約の予約完了を'Y'にします。

更新結果
予約状況テーブル
予約ID  部屋ID  到着予定日  出発予定日
------  ------  ----------  ----------
     1     201  2003/08/01  2003/08/03
     2     202  2003/08/02  2003/08/03
     3     201  2003/08/03  2003/08/04
     4     101  2003/08/01  2003/08/04
     4     102  2003/08/01  2003/08/04
     5     201  2003/08/04  2003/08/08
     5     101  2003/08/04  2003/08/08
     6     102  2003/08/04  2003/08/08

こちらを参考にさせていただきました


データ作成スクリプト

create table 部屋(
部屋ID   number(3) primary key,
許容人数 number(1) check (許容人数 between 1 and 2));

insert into 部屋 values(101,1);
insert into 部屋 values(102,1);
insert into 部屋 values(103,1);
insert into 部屋 values(104,1);
insert into 部屋 values(201,2);
insert into 部屋 values(202,2);

create table 仮予約(
予約ID           number(2) primary key,
到着予定日       date,
出発予定日       date,
予約人数         number(1),
予約完了フラグ   char(1), --予約完了の場合 'Y'
キャンセルフラグ char(1)  --キャンセルの場合 'Y'
);

insert into 仮予約(予約ID,到着予定日,出発予定日,予約人数)
select 1,to_date('2003-08-01','YYYY-MM-DD'),to_date('2003-08-03','YYYY-MM-DD'),2 from dual
union select 2,to_date('2003-08-02','YYYY-MM-DD'),to_date('2003-08-03','YYYY-MM-DD'),2 from dual
union select 3,to_date('2003-08-03','YYYY-MM-DD'),to_date('2003-08-04','YYYY-MM-DD'),2 from dual
union select 4,to_date('2003-08-01','YYYY-MM-DD'),to_date('2003-08-04','YYYY-MM-DD'),2 from dual
union select 5,to_date('2003-08-04','YYYY-MM-DD'),to_date('2003-08-08','YYYY-MM-DD'),3 from dual
union select 6,to_date('2003-08-04','YYYY-MM-DD'),to_date('2003-08-08','YYYY-MM-DD'),1 from dual;

create table 予約状況(
部屋ID     number(3) references 部屋(部屋ID),
予約ID     number(2) references 仮予約(予約ID),
到着予定日 date,
出発予定日 date);


SQL

declare
    Books pls_Integer;
    wk_部屋ID       部屋.部屋ID%type;
    wk_許容人数     部屋.許容人数%type;
    wk_許容人数合計 pls_Integer;
begin
    for r1 in (select 予約ID,到着予定日,出発予定日,予約人数
                 from 仮予約
               order by 予約ID) Loop
        Books := r1.予約人数;

        while Books > 0 loop
            select 部屋ID,許容人数,許容人数合計 into wk_部屋ID,wk_許容人数,wk_許容人数合計
            from (select 部屋ID,許容人数,sum(許容人数) over() as 許容人数合計,
                  Row_Number() over(order by case when 許容人数 <= Books
                                                  then -許容人数
                                                  else 許容人数 end,部屋ID) as Rn
                    from 部屋 a
                   where not exists(select 1 from 予約状況 b
                                     where b.部屋ID = a.部屋ID
                                       and r1.到着予定日 <= b.出発予定日
                                       and b.到着予定日  <= r1.出発予定日
                                       and b.出発予定日 != r1.到着予定日))
             where Rn= 1;

             --空室不足で予約不可
             exit when wk_許容人数合計 < r1.予約人数;

            if Books > 0 then
                insert into 予約状況(予約ID,部屋ID,到着予定日,出発予定日)
                values(r1.予約ID,wk_部屋ID,r1.到着予定日,r1.出発予定日);

                Books := Books - wk_許容人数;

                exit when Books <= 0;
            end if;
        end Loop;
    end Loop;
end;
/


解説

空室を探すロジックでの部屋の許容人数の優先順位は、
許容人数 <= 予約人数 の降順
許容人数  > 予約人数 の昇順
の順番としています。

例として、
5以下を降順、6以上を昇順に出力するSQLはこうなります。

select Rn
from (select RowNum as Rn from dict where RowNum <= 10)
order by case when Rn <= 5 then -Rn else Rn end;

Rn
--
 5
 4
 3
 2
 1
 6
 7
 8
 9