Help us understand the problem. What is going on with this article?

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

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

kawasima
Clojure関連のことをブログがわりに書き綴ります。 ※ここでの発言はシステムエンジニアを代表するものであって、所属する組織は二の次です。
https://github.com/kawasima/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした