SQL
oracle

日付FROM-TOのデータを穴埋めする

More than 1 year has passed since last update.

例えば、新幹線みたいに料金が繁忙期と閑散期で異なる設定がされている場合、こんな料金設定テーブルを考えつくかと思います。

CHARGE_PLAN

PLAN_NAME
START_DATE
END_DATE

閑散期
2015-05-10
2015-07-19

繁忙期
2015-07-20
2015-08-31

閑散期
2015-09-01
2015-12-20

こんな形のテーブルは、「今日有効な料金設定はどれだっけ?」をとってくるには問題ないですが、月次データ集計などで他のイベントテーブルとジョインして処理しようとすると面倒です。

OracleにはMODEL句というあまり現場で使われているのを見かけない機能があり、これを使うとダミーのView的なものが簡単に作れます。

http://codezine.jp/article/detail/3091

年分のカレンダーは以下のようなSQLで作れます。

SELECT c1 FROM dual

MODEL dimension by (1 as rn)
MEASURES (sysdate as c1)
rules iterate (365)
(c1[ITERATION_NUMBER]=SYSDATE-ITERATION_NUMBER)
ORDER BY 1


実行結果

16-06-17

16-06-18
16-06-19
16-06-20
16-06-21

17-06-14
17-06-15
17-06-16

365行が選択されました。


さて、これを先の料金設定のFROM-TOのデータから穴埋めした範囲データを作ります。

カレンダーとクロスジョインしてFROM-TOで挟みうつだけです。


SELECT d, PLAN_NAME
FROM (
SELECT d FROM dual
MODEL dimension by (1 as rn)
MEASURES (TO_DATE('2017-12-31') as d)
rules iterate (365)
(d[ITERATION_NUMBER]=TO_DATE('2017-12-31')-ITERATION_NUMBER)
ORDER BY 1) CAL
CROSS JOIN CHARGE_PLAN CP
WHERE d BETWEEN CP.start_date AND CP.end_date
ORDER BY d;


実行結果

17-05-10 閑散期

17-05-11 閑散期
17-05-12 閑散期
17-05-13 閑散期

17-07-17 閑散期
17-07-18 閑散期
17-07-19 閑散期
17-07-20 繁忙期
17-07-21 繁忙期
17-07-22 繁忙期

17-08-30 繁忙期
17-08-31 繁忙期
17-09-01 閑散期

17-12-16 閑散期
17-12-17 閑散期
17-12-18 閑散期
17-12-19 閑散期
17-12-20 閑散期

225行が選択されました。


これで、実際の乗車イベントデータとJOINして集計すれば、乗客ごとの利用料金集計などもSQL一発でできることになります。