クロス集計

クロス集計の表をCSVとして吐き出す

class CrossTabulater
  DELM = ","

  def initialize
    @items = {}
    @headers = {}
  end
  
  attr_writer :x_sorter
  attr_writer :y_sorter

  def add(x_axis, y_axis, raw_data)

    if not @headers.key? y_axis
      @headers[y_axis] = 1
    end

    if not @items.key? x_axis
      @items[x_axis] = {}
    end

    item = @items[x_axis]
    if item.key? y_axis
      item[y_axis] = item[y_axis] + raw_data
    else
      item[y_axis] = raw_data
    end
  end

  def write_to f
    x_axis_items = @headers.keys.sort(&@x_sorter)

    f.print DELM ,  x_axis_items.join(",") , DELM , "\n"

    @items.keys.sort(&@y_sorter).each do |key|
      value = @items[key]

      f.print key
      x_axis_items.each do |h|
        v = value[h] || 0
        f.print DELM , v
      end
      f.print "\n"
    end
  end
end
c = CrossTabulater.new
c.x_sorter = lambda { |a, b|
        aa = a.split("-")
        bb = b.split("-")
        r = aa.first.to_i - bb.first.to_i
        r != 0 ? r : aa.last.to_i - bb.last.to_i }
c.y_sorter = lambda {|a, b| b.to_i - a.to_i }


File.foreach("src.csv") do |line|
  item = line.chomp.split(",",  3)
  c.add(item[0], item[1], item[2])
end

File.open("dst.csv", "w") do |f|
  c.write_to f
end

#c.write_to $stdout

黙ってOracleでgroup by cubeするかピボットテーブル使えよ…