はじめに

BigQuery ML 2値分類チュートリアルをやってみた 01の続きです。

ステップ4のメモ

SELECT
  * EXCEPT(dataframe)
FROM
  `census.input_view`

ステップ4のSQL文ですがEXCEPT句を久々に見たので改めて調べました。

リファレンスに記載されている書き方は下記でした。

SELECT
  *
FROM
  UNNEST(ARRAY<int64>[1, 2, 3]) AS number EXCEPT DISTINCT
SELECT
  1;

FROM句で指定するテーブルの右側にEXCEPTと除きたいデータ（ここではDISTINCT SELECT 1）を記載することで除いています。

チュートリアルに戻ると、ステップ4に関しては該当カラムを除くように使っています。挿入箇所がリファレンスと異なりますが、行ってい操作は同じになります。 このExceotの書き方はBigQueryに限った書き方ではなく、SQL ServerやPostgreSQLにもサポートされている書き方になります。

チュートリアルで説明が無かった下記の２つのオプションについて確認します。

data_split_method='NO_SPLIT',
max_iterations=15) AS

data_split_method は、AUTO_SPLIT, RANDOM, CUSTOM, SEQ, NO_SPLITが選べます。

https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-glm#data_split_method

デフォルトはAUTO_SPLITです。

CUSTOMは指定したカラムがbool型でTRUEかNULLだと評価として使われ、FALSEだとtraiingデータとして使われるようです。 カラムの指定は別のオプションのDATA_SPLIT_COLで指定可能です。SEQを指定した場合も同様にDATA_SPLIT_COLを使います。

SEQは指定したカラムでの小さい値から指定した割合までを学習データとして、それ以降を評価データとして使います。カラムの指定はCUSTOMと同じく、 DATA_SPLIT_COL、割合の指定はDATA_SPLIT_EVAL_FRACTIONオプションで指定します。

ステップ5のメモ

ステップ5のSQLの結果とモデルの詳細画面の結果が異なるので調べました。 ステップ5のSQLの結果はチュートリアルに説明があるように、入力データの中からdataframe列がevaluationのものを抽出しています。 ステップ4で作ったロジスティック回帰のモデルを作るためのSQLではデータの分け方をno_splitにしているので、 データの分離をせずにすべてのデータを学習データと評価データに使います。 そのため、モデル詳細画面に記載されている精度の各値と、dataframe列がevaluationのデータをもとに学習したモデルに入れたデータの各精度指標が異なってきます。 また、それだけではなく今回はauto_class_weights=TRUEのオプションで各クラスのウェイトを重みつけすることもしているので、 この影響もあるので下記のように単純に学習データをすべて入れても計算結果が異なります。 異なるといっても大きな差がでていないのでここではそこまで議論しなくてもよいかもしれません。

SELECT
  *
FROM
  ML.EVALUATE (MODEL `census.census_model`,
    (
    SELECT
      *
    FROM
      `census.input_view`
    )

ステップ6のメモ

実際に使うときのthresholdはML.PREDICTのthresholdを使うことで指定できます。 https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-predict

リファレンスを参考にしてチュートリアルのSQLを修正すると下記のとおりになります。

SELECT
  *
FROM
  ML.PREDICT (MODEL `census.census_model`,
    (
    SELECT
      *
    FROM
      `census.input_view`
    WHERE
      dataframe = 'prediction'
     ),
     STRUCT(0.80 AS threshold)
  )

任意のしきい値を使う場合はこちらを使いたいと思います。STRUCTやAS thresholdを忘れないようにすることが注意ポイントです。

参考