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

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

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一発でできることになります。