唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryのSTRUCT型やARRAY型で無理やりGROUP BYで集計する

BigQueryではSTRUCT(構造体)型やARRAY(配列)型が使えます
データ型  |  BigQuery  |  Google Cloud

STRUCT型のカラムでGROUP BYしようとするとGrouping by expressions of type STRUCT is not allowed atのようなエラーが出てきて実行することができません
f:id:sucrose:20181009230316p:plain

同様にARRAY型のカラムだとGrouping by expressions of type ARRAY is not allowed atのようになります

これをどうにかしてGROUP BYすることを考えます

方法1: 構造体の中身を一度展開してGROUP BYする

構造体の場合、展開して新しいSTRUCTに入れ直して、展開したそれぞれのカラムでGROUP BYすると実行できます

SELECT
  STRUCT(s.x, s.y) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
) AS src
GROUP BY src.s.x, src.s.y

もしくはANY_VALUE()を使っても書けます

SELECT
  ANY_VALUE(s) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
) AS src
GROUP BY src.s.x, src.s.y

配列の場合も上の構造体の場合と同様に書けるはずですが、要素数が多いと大変なので次に紹介する方法をおすすめします

方法2: 構造体や配列を文字列に変換してGROUP BYする

構造体をFORMAT()関数で文字列に変換して、この文字列でGROUP BYするという手もあります
'%T'へのフォーマットはBigQueryのリテラルの形の文字列になるので(ほとんどの場合)元の構造体でGROUP BYするのと同じような結果が得られるはずです
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#t_and_t_behavior
というわけで以下のように書くことで、だいたいいい感じにSTRUCT型のカラムで集計できるはずです

SELECT
  ANY_VALUE(s) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
)
GROUP BY FORMAT('%T', s)

この方法はそのままARRAY型のカラムにも適用できます
以下のように書くことで配列をキーとした集計ができます

SELECT
  ANY_VALUE(a) AS a,
  COUNT(*) AS c
FROM (
  SELECT [1, 2] AS a
  UNION ALL
  SELECT [1, 2, 3] AS a
  UNION ALL
  SELECT [1, 2] AS a
)
GROUP BY FORMAT('%T', a)

BigQuery MLの変数に適用してくれる前処理の調査 / 学習された重みは標準化後の値に対する重みなのか? / nullの扱いは?

BigQueryにBigQuery MLという機械学習の機能が追加されました。今はロジスティック回帰による分類と線形回帰による回帰問題について学習ができるようです

学習時に変数に対してどのような前処理が行われるのか気になったので調べてみました
誤りなどがあったらコメントで指摘いただけると嬉しいです

前処理

The CREATE MODEL Statement  |  BigQuery  |  Google Cloud
上のドキュメントによると数値型のINT64やFLOAT64やNUMERICは前処理として標準化(平均が0で分散が1に変換)されてから利用されます
カテゴリカルな型(BOOL、STRING、BYTES、DATE、DATETIME、TIME型)はone hot encodngされるようです(値の種類数だけ次元があって、該当するものだけ1でほかは0になる)
TIMESTAMP型はそのままだと学習できなくて、文字列や数値に変換する必要があります
今の所ARRAYやSTRUCTは学習に使えなさそうです

学習された重みは標準化後の値に対する重みなのか

ML.WEIGHTS関数を使うと学習されたモデルの重みを見ることができます
この値は標準化を適用した後の変数に対する値なのかどうかが気になったので確認してみました
非常に簡単な線形回帰の学習をします
\(y = a + 2b\)という関係を学習させました

#standardSQL

CREATE OR REPLACE MODEL `for_blog_article.coef_model1`
OPTIONS(model_type='linear_reg') AS
SELECT
  *
FROM (
  SELECT 1 AS a, 0 AS b, 1 AS label
  UNION ALL
  SELECT 0 AS a, 1 AS b, 2 AS label
  UNION ALL
  SELECT 1 AS a, 1 AS b, 3 AS label
  UNION ALL
  SELECT 0 AS a, 0 AS b, 0 AS label
)

このクエリで学習したモデルに対して以下のようなクエリで重みを確認します

#standardSQL

SELECT
  *
FROM
  UNNEST(
    ARRAY(
      SELECT AS STRUCT
        *
      FROM ML.WEIGHTS(MODEL `for_blog_article.coef_model1`)
    )
  )

すると以下のような結果が得られました
見ての通り数値型の場合得られた重みは標準化後の重みに対するもののようでした。
もしもBigQuery MLで学習した重みを外に出して自前で計算するときには学習時にやったものと同様の前処理を行う必要がありそうです
f:id:sucrose:20180922233254p:plain

ML.FEATURE_INFO関数を使うと、学習時の平均や標準偏差などを見ることができます

f:id:sucrose:20180922233638p:plain

#standardSQL

SELECT
  *
FROM
  ML.FEATURE_INFO(MODEL `for_blog_article.coef_model1`)

nullの扱いはどうなってるのか?

上で学習したモデルについて試しにすべての変数をnullにして予測をしてみました

SELECT
  *
FROM
  ML.PREDICT(
    MODEL `for_blog_article.coef_model1`, (
      SELECT null AS a, null AS b
    )
  )

f:id:sucrose:20180923230231p:plain
学習時の平均値である0.5を欠損値であるnullの代わりに挿入して分類しているようです
f:id:sucrose:20180923230322p:plain

同様に学習する時も同様に欠損値に平均値を代入して学習していそうです
軽く試してみたところ、まず一部のカラムがnullの訓練データを加えると学習結果が変わるので無視されてはいなさそうということ
更に以下のようにnullのカラムに平均値を代入された場合の結果に等しい値をlabelに入れた場合に、実際に平均値のデータで学習した場合と学習結果は変わっていなさそうということがわかったので、学習時にもnullの代わりに平均値が使われていそうです

CREATE OR REPLACE MODEL `for_blog_article.coef_model2`
OPTIONS(model_type='linear_reg') AS
SELECT
  *
FROM (
  SELECT 1 AS a, 0 AS b, 1 AS label
  UNION ALL
  SELECT 0 AS a, 1 AS b, 2 AS label
  UNION ALL
  SELECT 1 AS a, 1 AS b, 3 AS label
  UNION ALL
  SELECT 0 AS a, 0 AS b, 0 AS label
  UNION ALL
  SELECT null AS a, 0 AS b, 0.5 AS label
)

one hot encodingのときのnullの扱いは?

予測時にカラムがnullのデータを渡したらエラーになりました
以下のような型が合わないというエラーメッセージなので何か別のエラーを踏んでるかもしれません

Invalid table-valued function ML.PREDICT
Model テーブル名 cannot accept column a due to type mismatch. Data type INT64 does not match what model expects.

ちなみにone hot encoding として扱われる文字列のカラムで、予測するときに存在しない値を入れた場合にはちゃんとそのカラムの値は使われずに計算されました

学習時には学習された重みに_null_filterというのが増えているのでnullはnullとして学習していそうな雰囲気があります
f:id:sucrose:20180923233958p:plain

おまけ: 多重共線性?

以下のように二通りの文字列を取るカラムを学習させます

CREATE OR REPLACE MODEL `for_blog_article.coef_model3`
OPTIONS(model_type='linear_reg') AS
SELECT
  *
FROM (
  SELECT 'x' AS a, 1 AS label
  UNION ALL
  SELECT 'y' AS a, 2 AS label
)

すると以下のような重みが得られました
f:id:sucrose:20180923234920p:plain
カテゴリカルな変数をone hot encodingにする場合、そのうちの次元を一つ削らないと多重共線性の問題が起こってしまって重みの大小関係を見る場合に悪影響が出てきてしまうかもしれない、という話をたまに聞きますが上の結果だけ見るとそういう削る処理はやってなさそうです