Rails + PostgreSQLで表形式に集計する

表形式大好き日本人

２５年くらい前から、表形式での集計に悩まされてきた。例えばこんなソースがあったとする。

name	question	answer
mike	好きな食べ物	魚
john	好きな食べ物	肉
mike	好きな国	日本
john	好きな国	ギリシャ

(この形式をSTIというのだそうで、最近知った)。
これをこう表形式に集計しろというニーズがしょっこらしょっちゅうある。

name	好きな食べ物	好きな国
mike	魚	日本
john	肉	ギリシャ

個人的にはSTIのままのほうが様々な集計がしやすいのになぜわざわざ集計しづらい２次元の表形式にしようとするのかは甚だ謎であるし、設問が多くなると横方向に果てしなくスクロールしないといけないし、３次元とか４次元とかになってしまったらもはや視覚化が不可能になってしまうじゃん！と思うので集計しやすい最初の形式のままいろいろしたほうがいいと思う。

でも客がそうせえというのだから仕方ない

「プログラマの三代美徳は怠惰・傲慢・短気」を信条としている私としては、上記の設問に対する回答はここ２５年くらい、「Excelのピボットテーブルを使え」だった。だが、「システム開発」という仕事をしているとこれが通じないことがわかった。何もせずに済むなら何もしない、というのを美徳としてきたのだが、システムを開発してなんぼの仕事なので、無駄とわかっていてもシステムを作らない訳にはいかない。

Excelでは25年も前からできることなのに・・・？

Rubyに限らず、３年ほど前の派遣先でも性懲りもなく同じようなニーズがあった。このときはJavaだったが、Javaでシコシコ処理した。しかし今回の派遣先で、Railsで会社の人の作ったコードは、ActiveRecordを大量に作成してしまいメモリリークを起こしてしまっていた。Excelは1995年くらいに、メモリ128MBとかで普通にできていたことが、2018年現在、4GBメモリがあっても不足しメモリリークを起こしてしまう。なんじゃこりゃ。

ActiveRecordに仕事をさせてはいけない

どうやらこれが超絶原則のようなのだが、ActiveRecordはDBへのアクセスを抽象化してあたかもただオブジェクトを触っているだけかのような感覚でDB操作が行えてしまうすぐれものなのだが、欠点がある。複雑なテーブルの処理が苦手（メモリを派手に使ってしまう）のである。

また、ActiveRecordはテーブルのカラムをModelのMethodとして表現するため、カラムが可変長というのも原則苦手である。

そして私はこうした

pivottable.rb

Table.select('name, jsonb_object_agg(question, answer) as ans')
     .group('name, question').map { |row| [row.name, row.ans] }.to_h

jsonb_object_aggはPostgreSQL9.4?あたりから使えるJSON関数の一つで、複数レコードを一つのJSON形式のフィールドに集約してくれるという機能を持つ。SQLでは昔からsumやcountといった集計関数が利用できるが、それのstring版と考えるといい。
この集計関数のおかげで、普通にTable.allとすると４個作成されるActiveRecordオブジェクト数が2数に減っていることに注目してほしい。設問数が100個、ユーザーが100人だともとのSTIを読み込むと10,000個のActiveRecordオブジェクトが生成されるが、上記の集計関数を用いると設問数の影響を受けず100個しかActiveRecordオブジェクトが作られない。O（n＊m）計算量→O(n)計算量に減ったのだ。

更に、1個のJSONを返すようなクエリを書くことも原理的には可能だが、流石にやりすぎかと思って今のところやっていない。

なお、他のRDBMSにも似たような関数があるようだが、上記のクエリ式はPostgreSQL以外では利用できない。
これで、以下のような結果が得られる。

pivottable.json

{
  "mike": {
    "好きな食べ物": "魚",
    "好きな国": "日本"
  },
  "john": {
    "好きな食べ物": "肉",
    "好きな国": "ギリシャ"
  }
}

上記ではJSONで表記しているが実際にはRubyのHashとして得られる。
あとは煮るなり焼くなり、という形になっている。個人的には、このJSONをそのままフロントに渡してJavaScriptで処理してくれたらいいと思ったりしているが、あくまでRailsでCSVを作るそうだ、そうですか・・・。

まあHashなのでActiveRecordをコネコネするよりは１０倍速いはずだ・・・。

結論

• STI表をそのままExcelに渡してピボットテーブルで修家してもらおう！
• なんならExcelはODBC経由でDBから直接データも取れるぞ(JOINとかも使えるぞ)
• 俺ならS3 static website hostingにhtml表を作ってExcelでWebクエリとかやるな・・・
• 最近（結構前？）ならPowerQueryというのもあるぞ