（元）社内SEの徒然なる日記

SQL（Oracle）で日付の連続データを作成する

2013-06-20（Thu）

■　日付の連続データ作成

日付別の件数集計を行う処理があるのですが、この出力条件に「件数が０件の日付も表示する」というものが加わると、Oracle（というかSQL）では面倒です。なにせ、抽出元には出力するための日付がそもそもないのですからね。

最初は、無理にSQLで作らずに言語側（Java）で日付のリストを作って、SQLで取得した抽出結果をぶつけようかと思ったのですが、ちょっと調べるとSQLでも日付の連続データの作成が出来そうです。

下記は、2013年5月1日から2013年5月15日までの日付リストを作成するSQLです。


-- 日付の連番（リスト）を作るＳＱＬ
SELECT
        TO_DATE('20130501', 'YYYYMMDD') + ROWNUM - 1
  FROM  ALL_CATALOG
 WHERE  TO_DATE('20130501', 'YYYYMMDD') + ROWNUM - 1 <= TO_DATE('20130515', 'YYYYMMDD')
ORDER BY 1

抽出結果は、こんな感じになります。

■　解説

必要なのは、開始日と終了日、そして開始日と終了日の差日数以上の件数があるテーブルです。

今回は、テーブル（というかビュー）として「ALL_CATALOG」を使用します。ALL_CATALOGビューは、ユーザーが使える表やクラスタ、ビュー、シノニムとかを保持するビューで、私の環境では10,000件以上の件数があります。開始日と終了日の差日数が10,000件を超えない限りは、これで十分です（まぁ、万が一超えるようならunion allで結合すれば良いだけの話だが）。

ALL_CATALOGビュー自体の項目は使用せずに、ROWNUM擬似列だけを使用します。

ROWNUM擬似列は、抽出結果に対する1から始まる行番号を表します。

ORACLEの日付計算は、単純に加減算したい日数をプラスするだけで可能です。そこで、作成したい日付リストの開始日にROUWNUM（正確には、ROWNUMから１減算した値）を加算することで、日付の連続データを作成することが出来ます。

これだけだと、ALL_CATALOGビューの全件の日付リストが出来てしまうので、Where句で終了日以前の日付のみ抽出するようにします。

これで、指定した範囲内の日付の連続データを抽出することが出来ました。

■　後書き

この方法、自力で考えた訳ではなくGoogle先生に教えて頂きました。

最近は、それなりにSQLを使えるようになってきたと思っていたのですが、まだまだ奥が深いようです。
たまには、こういったパズルのようなSQLも面白いですね。