Oracleで件数の多いテーブルを日付をキーに1ヶ月分ずつ区切って検索するサンプル

IT 勉強 Ruby

紀伊國屋書店で「Python クックブック 第2版」を買ったbonlifeです。O'reillyのWeb直販で買えば、キャンペーンで『Pythonクックブック』Tシャツもらえたのに。
さてさて、MViewとかサマリテーブルとか作っておけば良かった…orz ってぐらいの集計処理を後からしなきゃいけなくなった時のためのメモです。
データ件数が多いテーブルの場合、適当な件数に絞って処理をしないとそりゃあもう大変なことになります。そんな時は、DATE型のフィールドをWHERE句の検索条件に追加し、データ件数に応じて1日分とか1週間分、1ヶ月分の情報だけを対象に処理させると便利。ただ、このやり方の場合、1ヶ月分ずつ区切ったとしても、1年分を検索するには12回SQLを発行しなきゃいけなくなります。SQLがすぐに返ってくる場合は良いですが、多少無理をさせている場合、「まだかな、まだかなー。」と待つ必要があり、結構なストレス…。そんな時はスクリプト言語の力を借りれば良いじゃないの！ということでRubyを使ってみました。(PL/SQLにチャレンジしてみようと思ったのですが、SELECTにINTOが必要とかなんとか理不尽なことを言われたので、挫折しましたよ、これ。)
あるテーブル(TABLE_A)のデータ件数を日ごとに集計するサンプルです。(1ヶ月分ずつ検索範囲を区切ってます。)

  • ora_count_by_date.rb
#!/usr/bin/env ruby

require 'oci8'
require 'date'

# Oracleに接続

conn = OCI8.new('user','password','localhost')

# ヒアドキュメントを使ってSQLの雛形を準備
# 絞込みのためのDATE型の検索条件には、バインド変数を指定

sql = <<EOS
SELECT   TRUNC(DATE_A,'DD'), COUNT(*)
FROM     TABLE_A
WHERE    DATE_A >= TO_DATE(:from_month,'YYYYMM')
  AND    DATE_A <  TO_DATE(:to_month,'YYYYMM')
GROUP BY TRUNC(DATE_A,'DD')
EOS

# とりあえずparseしてみる

cursor = conn.parse(sql)

# 検索期間をDateで指定

from   = Date.new(2006,4,1)
to     = Date.new(2007,4,1)
target = from

# 検索条件を月単位でインクリメント( >> 1)しながら処理
# バインドする値を毎回変更

while target <= to
  cursor.bind_param(':from_month',target.strftime('%Y%m'))
  cursor.bind_param(':to_month',(target >> 1).strftime('%Y%m'))
  cursor.exec()
  while r = cursor.fetch()
    puts r.join("\t")
  end
  target = target >> 1
end

よくあるサンプルだと、カンマでjoin()してCSVにしてますが、やっぱり"\t"でjoin()してTSVにした方がEXCELに貼り付けやすくて便利な気がします。以下のようにしてファイルにリダイレクトして、使ったり。

> ruby ora_count_by_date.rb > output.txt

Python本買っといてアレですが、Rubyも良いですね。

[参考URL]