【Ruby】DBからの戻り値をdplyr風に操作するleft_outer_join関数を定義したら便利だった

最近、BigQueryに解析・レポート用にデータの紐付け＆クレンジングしたテーブルを入れるプログラムを実装しています。

例えば、ある学園の生徒名簿のテーブルを考えると、こんな感じになるはずです。

生徒id	名前	年齢
1	hoshimiya	17
2	ozora	13
3	kiriya	17

これがRubyのプログラムに渡ってくるとき、こんな形で変数に格納されるはずです。

students = [
  {'生徒id' => 1, '名前' => 'hoshimiya', '年齢' => 17},
  {'生徒id' => 2, '名前' => 'ozora', '年齢' => 13},
  {'生徒id' => 3, '名前' => 'kiriya', '年齢' => 17}
]

次に、この学園で「誰が何をを買ったのか」という購入履歴テーブルを考えます。

日付	生徒id	購入品目	購入金額
2016-03-25	2	ドーナツ	750
2016-03-25	1	ノコギリ	2000
2016-03-25	3	写真集	2000
2016-03-25	1	のり弁	500

これをSQLで紐付けるとすると、全く簡単です。

SELECT 
  購入履歴.日付,
  購入履歴.生徒id,
  購入履歴.購入品目,
  購入履歴.購入金額,
  名簿.名前,
  名簿.年齢
FROM
  購入履歴
LEFT OUTER JOIN
  名簿

ところが、実際のデータは汚く、購入履歴テーブルの生徒idに全角文字が混ざっていたり、何かのパラメータがparseされずに残っていました。

（私が実際に触っていたデータはGoogleAnalytics由来のアクセスログだったため、URLのパラメータが残っていたり、他のパラメータを切り出した一部に欲しいidがあったり、他のDBから値を取ってくる必要があったり、SQLだけで完結させることができませんでした）

そのため一旦購入履歴テーブルをrubyのプログラムに落とした後、きれいに生徒idを前処理をし、再び生徒名簿のテーブルから値を取ってきてjoinするという工程が必要でした。また、他のidでも同じような作業が何度か必要でした。

ここで、「もしSQLでそのままLEFT OUTER JOINできていたら同じ結果が返ってくるだろう」という関数を使うと、プログラムを比較的すっきりさせたまま実装することができました。R言語のdplyrのjoin系の関数の挙動を真似てみました。

（R言語ではDBの戻り値はデータフレームという型に入り、dplyrというライブラリで、DBのテーブルのようにjoinしたりgroup byしたりすることができます）

def left_outer_join(left_table, right_table, key)
  # right_tableにleft_tableと同じキーが存在する時、破壊的に代入する
  # 配列のまま探索すると遅いので、欲しいキーごとにまとめたハッシュに変換する
  right_table_dict = right_table.group_by{|row| row[key]}
  left_table.inject([]) do |res, left_row|
    target_val = left_row[key]
    if right_table_dict.has_key?(target_val)
      right_table_dict[target_val].each do |right_row|
        res << left_row.merge(right_row)
      end
    else
      # 右のテーブルにidが無かった場合、left_rowをそのまま結果に返す
      res << left_row
    end
    res
  end
end

こんな感じで使います。

left_outer_join(buying_history, students, '生徒id')

目的通り、購入履歴データに生徒名簿を紐付けた値を得ることができました。 この結果をBigQueryに入れておけば、年齢ごとの集計したレポートの作成などが簡単にできます。

日付	生徒id	購入品目	購入金額	名前	年齢
2016-03-25	2	ドーナツ	750	ozora	13
2016-03-25	1	ノコギリ	2000	hoshimiya	17
2016-03-25	3	写真集	2000	kiriya	17
2016-03-25	1	のり弁	500	hoshimiya	17