意外と難しい
##例表(TIME_TABLE)
TIME_TABLE
| room_id | event_name | start_date | end_date |
|---|---|---|---|
| 1 | おもちゃ見本市 | 2017/1/10 | 2017/2/1 |
| 1 | A社臨時会議室 | 2017/3/11 | 2017/3/31 |
| 1 | D社株主総会 | 2017/4/20 | 2017/4/21 |
| 2 | おもちゃ見本市 | 2017/1/10 | 2017/2/1 |
dateは１時間単位でしか設定されない想定。
##例えば部屋を汚した利用者を特定する
過去データしか無いとわかってる場合は容易
SELECT "部屋1を汚したイベント".event_name FROM (
SELECT * FROM TIME_TABLE
WHERE
room_id = 1
AND start_date <= SYSDATE
ODER BY start_date DESC
) "部屋1を汚したイベント"
WHERE ROWNUM <= 1
##例えば部屋が利用不可にされたので前後の利用者を得つつ現刻から未来、今、過去で近い順に並べる
直後の利用者情報が一番ほしいけど現在利用中、過去利用中も後ろに表示させたい的な動作
難しい。オラオラで実装。ベターかはわからない。
SELECT "部屋1利用するイベント".event_name FROM (
SELECT
SIGN(SYSDATE - start_date) as sign_start_flg,/*過去日なら-1 今なら0 未来日なら+1*/
SIGN(SYSDATE - start_date) as sign_end_flg,
ABS(SYSDATE - start_date) as abs_start_dt,
ABS(SYSDATE - start_date) as abs_end_dt,
TIME_TABLE.*
FROM TIME_TABLE
WHERE
room_id = 1
ORDER BY sign_start_flg ASC, abs_end_dt ASC /*開始日が未来でかつ終了日が現在日から最も近いイベント*/
) "部屋1利用するイベント"
WHERE ROWNUM <= 10
-- AND sign_end_flg > 0 /*未来、使用中の部屋は拾わない*/
ORDER BYにSELECTで指定した4値の与え方でいろいろ使い回せるのが楽というのもある
##秘話
例からは想像できない化物SQLのリファクタリング結果で
もともとは4クエリ発行してたやつをプログラム上で加工してたのが遅すぎて、早くしろと言われた奴がUNIONで悪魔融合。
負荷テスト担当して目標性能の1/3だったのでNGだしたら、じゃあお前がなんとかしろ言われ作った苦肉のSQL。
性能60倍ぐらい改善した。