クロス集計の表を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するかピボットテーブル使えよ…