Mysql予約テーブルから空き時間を探す

Question

yoyaku_tbl

revdat date,
revstart time,
revend time,
tanto text

yoyaku_tblのレコード
2018-11-01 | 10:00:00 | 10:30:00 | Aさん
2018-11-01 | 10:00:00 | 11:00:00 | Bさん
2018-11-01 | 14:00:00 | 15:30:00 | Bさん
2018-11-01 | 11:00:00 | 12:45:00 | Aさん
2018-11-01 | 14:30:00 | 15:45:00 | Aさん
2018-11-02 | 10:00:00 | 10:00:00 | Aさん
2018-11-02 | 10:00:00 | 10:00:00 | Bさん

上記テーブルから

例えば
2018-11-01のAさんは
30分を１枠とした場合
9:00〜12:00までで何枠取れるか（空きがあるか）
12:00〜18:00までで何枠取れるか（空きがあるか）
を取得したい。

結論として
午前中は10:30〜11:00までの１枠
午後は
12：45〜14：30までで2枠
15：45〜18：00までで4枠
計8枠

と言う結果を返したい。

複雑な計算で、お力添えをいただきたく質問いたしました。

Comments

[yambejp]

予約がダブってはいけない条件で、空いているコマを探すということですよね？
Bさんが10時からAさんとダブってますがダイジョブ？

[u-sukesan]

yambejp 様
予約は担当が別であれば、同じ時間でダブっても大丈夫です

[u-sukesan]

最後のカラムは
予約している人ではなく
担当スタッフと言う意味です。

説明不足ですいません

[yambejp]

なんとなく理解したので回答つけてあります
考え方が違う場合は補足お願いします

Answer 1

簡単に汎用的に対処するなら、
空き時間も予約済みも含めた「予約状況」テーブルを作っておいて、
予約が入り次第「予約状況」テーブルもメンテしておけば、
空き時間も求めやすいのではないかと。

レコード数の増加を気にするのであれば、
定期的に過去の時間帯を削除すればよいかと。

SQLって一般的に、
ないものを絞り込むことよりも、
あるものを絞り込むことに特化しているので、
空き時間なのか予約済みなのかを示すフラグをテーブル内に設けて
インデックスを張っておけば、
空き時間はすぐに抽出できるはずです。

Answer 2

こんな感じでどうでしょう

• 元データ

create table yoyaku_tbl(
revdat date,
revstart time,
revend time,
tanto varchar(20));

insert into yoyaku_tbl values
('2018-11-01','10:00:00','10:30:00','Aさん'),
('2018-11-01','10:00:00','11:00:00','Bさん'),
('2018-11-01','14:00:00','15:30:00','Bさん'),
('2018-11-01','11:00:00','12:45:00','Aさん'),
('2018-11-01','14:30:00','15:45:00','Aさん'),
('2018-11-02','10:00:00','10:00:00','Aさん'),
('2018-11-02','10:00:00','10:00:00','Bさん');

• 考え方

ランク付けをする

select revstart,revend,(select count(*)+1 from yoyaku_tbl where revdat=t1.revdat and tanto=t1.tanto and revstart<t1.revstart) as rank
from yoyaku_tbl as t1
where revdat='2018-11-01' and tanto='Aさん'

• 結果

revstart	revend	rank
10:00:00	10:30:00	1
11:00:00	12:45:00	2
14:30:00	15:45:00	3

• ランク付けされたテーブル通しを1ずらしてjoinする

ついでに9:00からと18:00までを追加する

select t2.revend
,t4.revstart
,time_to_sec(timediff(t4.revstart,t2.revend))/(60*30) as blank
from (
select revstart,revend,(select count(*)+1 
from yoyaku_tbl 
where revdat=t1.revdat and tanto=t1.tanto and revstart<t1.revstart) as rank
from yoyaku_tbl as t1
where revdat='2018-11-01' and tanto='Aさん'
union all select null,'09:00:00',0
) as t2
inner join (
select revstart,revend,(select count(*)+1 
from yoyaku_tbl 
where revdat=t3.revdat and tanto=t3.tanto and revstart<t3.revstart) as rank
from yoyaku_tbl as t3
where revdat='2018-11-01' and tanto='Aさん'
union all select '18:00:00',null,count(*)+1 from yoyaku_tbl 
where revdat='2018-11-01' and tanto='Aさん'
) as t4 on t2.rank=t4.rank-1

Comments

[u-sukesan]

yambejp　様

いつもありがとうござます。
やっと構造が理解できました（笑）

全然こんな発想は思いつきませんでした。
素晴らしいと思います。

あとは
午前（9:00〜12:00）までで何コマ
午後（12:00〜18:00）までで何コマ
と言う結果にしたいと思っておりますので

t2、t4それぞれのランク付けの段階で12:00までと18:00までのランク付けを出して
joinする感じですかね

あ、いっぺんにはできませんね
12:00までのqueryと18:00までのqueryを2回実行する感じですかね
時間またいだ場合はどうなるんだろ

select t2.revend
,t4.revstart
,time_to_sec(timediff(t4.revstart,t2.revend))/(60*30) as blank
from (
select revstart,revend,(select count(*)+1
from yoyaku_tbl
where revdat=t1.revdat and tanto=t1.tanto and revstart<t1.revstart) as rank
from yoyaku_tbl as t1
where revdat='2018-11-01' and tanto='Aさん'
and `rev_e_time` BETWEEN '09:00:00' AND '12:00:00'
union all select null,'09:00:00',0
) as t2
inner join (
select revstart,revend,(select count(*)+1
from yoyaku_tbl
where revdat=t3.revdat and tanto=t3.tanto and revstart<t3.revstart) as rank
from yoyaku_tbl as t3
where revdat='2018-11-01' and tanto='Aさん'
and `rev_e_time` BETWEEN '09:00:00' AND '12:00:00'
union all select '12:00:00',null,count(*)+1 from yoyaku_tbl
where revdat='2018-11-01' and tanto='Aさん'
and `rev_e_time` BETWEEN '09:00:00' AND '12:00:00'
) as t4 on t2.rank=t4.rank-1

[u-sukesan]

12:00までのランク付けを２つ
18時までのランク付けを２つ
作ってそれぞれをjoinですかかね？

[yambejp]

AM/PMは必須なのですね？
Aさんのデータを見る限り11:00～12:45の区間でAM/PMを無視してとれていますけど・・・

[u-sukesan]

そうなんです(T_T)
11:00～12:45と言う例がおかしいですが

例えば
一人の人が11:00〜3コマ予約した場合
11:00〜12:30と言う予約が発生します。