社内SEの徒然なる日記

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

■ 日付の連続データ作成

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

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

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

-- 日付の連番(リスト)を作る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


抽出結果は、こんな感じになります。
Oracleで日付リスト

■ 解説

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

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

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

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

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

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

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

■ 後書き

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

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


スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する