【備忘録】BigQueryでカテゴリ変数を処理してみる

仕事で機械学習のテーブルデータを扱うときに、各カテゴリ毎の分析を上手くやる方法を少し考えた。ので、適当なサンプルデータを使ってそのやり方を備忘録として書いておく。

何をしたいのか

• 機械学習のテーブルデータなどで用いられるカテゴリ変数をBigQueryでうまく使いたい
• カテゴリ毎の他カラムの分布、カテゴリ変数をフラグ変数にして相関係数をみるなど
• それをハードコーディングするのではなく楽してやる

サンプルデータ

これはDataFormでサンプルデータとして使われている商品取引履歴のテーブルとなっている。
この中でカテゴリ変数はorder_status（注文状態）, payment_status（支払状態）, payment_method（支払方法）などがあるが、今回はpayment_methodを使っていこうと思う。

カテゴリ変数の内訳

クエリ

-- カテゴリ変数の種別数の上位3件を取得

SELECT
  payment_method
  , COUNT(*) AS cnt
FROM `fx-systemtrader-dev.dataform.dataset`
GROUP BY
  payment_method
ORDER BY COUNT(*) DESC
LIMIT 3

結果

これでカテゴリの内訳がわかり、割合が多いカテゴリの上位3件のカテゴリ数を取得することが出来た。

カテゴリ毎の他変数の概要（例として四分位数）

全データからpayment_methodの種別毎にパーセンタイルの計算をしている。
（これは特にひねったクエリではない）

クエリ

-- 上位3件のカテゴリ毎の価格の四分位数

SELECT
  DISTINCT
  payment_method
  , COUNT(*) OVER(PARTITION BY payment_method) AS cnt
  , PERCENTILE_CONT(amount, 0.01) OVER(PARTITION BY payment_method) AS PER01
  , PERCENTILE_CONT(amount, 0.25) OVER(PARTITION BY payment_method) AS PER25
  , PERCENTILE_CONT(amount, 0.50) OVER(PARTITION BY payment_method) AS PER50
  , PERCENTILE_CONT(amount, 0.75) OVER(PARTITION BY payment_method) AS PER75
  , PERCENTILE_CONT(amount, 0.99) OVER(PARTITION BY payment_method) AS PER99
FROM `fx-systemtrader-dev.dataform.dataset`
ORDER BY cnt DESC
LIMIT 3

結果

カテゴリ変数とほかの変数の相関係数

各カテゴリをフラグ変数（そのカテゴリであれば1, それ以外は0）として、その他の変数との相関係数を見るクエリを書いた。これは「一つのテーブルのデータを上手いこと分割して処理を行う」ということができないので、同じデータをいつくか複製して重複した処理を行う必要がある。

そこで、普段あまり使わないCROSS JOIN（積集合）を使う。CROSS JOINを使うと二つのテーブルのデータ数の積と同じだけデータが生成される。基本的に非常に大きいテーブルが生成されてしまうためあまり使われないが、こういった「複数回処理する必要があり、複数の同じデータが必要な場面」には役に立ちそう。

クエリ

-- 上位3件のカテゴリと価格の相関係数

WITH top3_category AS (
  SELECT
    payment_method AS cat
  FROM `fx-systemtrader-dev.dataform.dataset`
  GROUP BY
    payment_method
  ORDER BY COUNT(*) DESC
  LIMIT 3
)

SELECT
  cat
  , CORR(IF(payment_method = cat, 1, 0), amount) AS c
FROM top3_category
  CROSS JOIN `fx-systemtrader-dev.dataform.dataset`
GROUP BY cat

結果

今回は上位3件としているが、これが数十、数百カテゴリ存在した場合にハードコーディングするのが非常に大変になるため、上記のように機械的に各カテゴリの処理を反復する方法だと楽が出来る。