【BigQuery編】kaggleに登録したはいいが、手を動かしはじめられない人が見るページ

こちらの動画をみて、コードを読んだ感想です！

まず！動画を見る前にこちらのGithubのSQLでだいたい何をしているか見てからの方がいいです！

データの可視化

まずはデータの可視化に関して説明がありました。
kernelを眺めていたのですが、

写経するだけでも勉強になる

やってみよう。確かに美しいkernelがたくさんある。
可視化の技術をExplanatory Data Analysis（EDA）というらしい。

BigQuery

単にSQLなので、実際に使ってみるのが早い。使おう！
これもっと早く知っていれば…今までローカルで数時間色々と格闘していたのがすぐに終わったかもしれない…

今回のBigQueryの題材になっているのは、以下のコンペです。

データインポート

ここにきて、Google Cloud Storageに入れておいて意味がでてきた！

StorageからデータをBigQueryにインポートします！

分析1: Log MA and Days of Week Means (LB: 0.529)

実際に動画見る前はうわぁ、いやだなぁ...難しそうだなぁ...と思っていましたが、じっくり見ているとおや、そんなに厳しい訳ではないぞ！という風になりました！

目標

8月16日から8月31日までの生鮮食品の売上を予想するコンペです。

生鮮食品は痛みやすいので、売上を予測することができれば、処分する在庫を減らせるため、予測の精度を上げたいといった感じです。

Query1:プロモーションされた日とプロモーションされていない日を計算する

-- dest. table: mean_baseline.num_promotion
SELECT
  item_nbr, store_nbr,
  SUM(CASE WHEN onpromotion = 'True' THEN true ELSE false END) as num_onpromotion,
  14 - SUM(CASE WHEN onpromotion = 'True' THEN true ELSE false END) as num_onnopromotion
FROM
  corpora2.train
WHERE
  id >= 124035459
GROUP BY
  item_nbr, store_nbr

この一行目にコメントアウトで何をやっているか記入するのは、BigQueryのTipsのようです！
過去Query一覧で何やったかわかる。

ちなみに、mean_baselineはデータセット名、num_promotionはTable IDです。

id >= 124035459では、古いデータを捨てています。
厳密には2017年8月1日までのデータを捨てています。
（何故7月31日までではなく、8月1日までなのかは不明ということでした…）

8月上旬の売れ行きから8月下旬の売れ行きを予測するのはtkmさん曰くリーズナブルだそうです。

item_nbrとstore_nbr（itemとstoreのid？）でグルーピングして、プロモーションが何回したか数えるnum_onpromotionと14日分（8月2日から8月15日まで）からプロモーションされなかった日を計算するnum_onnopromotionがあります。

これで、店舗毎、商品毎のプロモーションの日数を計算できました。

Query2:分散の分析

-- dest. table: mean_baseline.train_logsum_2017_aug
SELECT
  item_nbr, store_nbr,
  CASE WHEN onpromotion = 'True' THEN true ELSE false END as onpromotion,
  SUM(CASE WHEN unit_sales < 0 THEN 0 ELSE LN(unit_sales + 1) END) as unit_sales
FROM
  corpora2.train
WHERE
  id >= 124035459
GROUP BY
  item_nbr, store_nbr, onpromotion

今回は、onpromotionがbooleanではなく文字列で入ってしまっていたため、onpromotionカラムがTrueの時にtrueと修正したのと、unit_salesに1を足して対数をとる。

ここで理系の人だと、あぁ〜1を足して対数ね！となるだろうが、私は文系だ！

まず対数をとるのは、変数変換というものらしい。結局は分散を分析するために対数にした方が、扱いが簡単だから対数にする！

そして、1を足すのは0だった場合、対数を計算できないからだそうだ！（クエリでは0未満のものは0としている）

計算の説明はこちらのサイトを参考にしました。

しかし「１を足すのがよい」といった理由は存在しない。

え

とはいえ、これで分散を分析できるようになった。

Query3:売上個数 / プロモーション日数を計算

-- dest. table: mean_baseline.train_logavg_2017_aug
SELECT
  t.item_nbr as item_nbr,
  t.store_nbr as store_nbr,
  t.onpromotion as onpromotion,
  CASE WHEN t.onpromotion = true THEN
    t.unit_sales / num_onpromotion
  ELSE
    t.unit_sales / num_onnopromotion END as unit_sales
FROM
  mean_baseline.train_logsum_2017_aug as t
LEFT OUTER JOIN
  mean_baseline.num_promotion as p
ON
  t.item_nbr = p.item_nbr AND t.store_nbr = p.store_nbr

LEFT OUTER JOINでtrain_logavg_2017_augテーブルとsum_num_promotionテーブルをジョインする。

そして、onpromotionがtrueのものは、売上個数 / プロモーション日数、falseのものは、売上個数 / プロモーションしていない日数 している。

falseでプロモーションしていない日数を計算する必要があるんかいな、全部14やからfalseなんちゃうんか、と思ったけど、例えば8月5日に入荷したやつとかは10日になるんだろうとか勝手に思った。

Query4: submitデータの準備

-- dest. table: mean_baseline.submit
SELECT
  t.id as id,
  CASE WHEN a.unit_sales is null THEN 0 ELSE EXP(a.unit_sales) - 1 END as unit_sales
FROM
  corpora2.test as t
LEFT OUTER JOIN
  mean_baseline.train_logavg_2017_aug as a
ON
  t.item_nbr = a.item_nbr AND t.store_nbr = a.store_nbr AND
  t.onpromotion = a.onpromotion

EXP (LOG (n)) = n

なので、先程対数をとったunit_salesをsubmitするように元にもどしているということでしょうか。

Query5: デバッグ

-- for debug, you need to import this kernle submit to mean_baseline.submit_kernel
SELECT
  s.id,
  ABS(s.unit_sales - k.unit_sales) as diff
FROM
  mean_baseline.submit as s
LEFT OUTER JOIN
  mean_baseline.submit_kernel as k
ON
  s.id = k.id
ORDER BY
  diff desc
limit 10

実際のkernelと比較するためにABSで絶対値を出して、diffを計算しています。

setting

• Destination Tableを記載した時は、必ずAllow Large Resultにチェックを入れる
• Flatten Resultもチェック入れとく
• バッチ処理する時は、Query PriorityをBatchにする
• Use Legacy SQLを外しておく

submit方法

分析2: Mean Baseline (LB ~ .59)

window関数

Query1:マスターを作る

SELECT
  *
FROM
(
SELECT
  distinct(date)
FROM
  corpora2.train
WHERE
  date >= '2017-01-01'
),
(
SELECT
  distinct(store_nbr)
FROM
  corpora2.train
WHERE
  date >= '2017-01-01'
),
(
SELECT
  distinct(item_nbr)
FROM
  corpora2.train
WHERE
  date >= '2017-01-01'
)

distinctで一意のデータとして作成しています。

2017年1月1日より新しいデータで、日付とstore_nbr、item_nbrの組み合わせが一意のマスターデータが完成します。

BigQueryのクロスジョインはカンマで記述できる。

クロスジョインの詳細はこちらを参考に。

また、副問合せについてもモヤモヤしたので、FROM句の副問合せはこちらが参考になると思います。

Query2:マスターにtrainingデータをジョインする

-- dest. table: ma8dw.train_2017_ext
SELECT
  m.date as date,
  FORMAT_DATE('%a', m.date) as dow,
  m.store_nbr as store_nbr,
  m.item_nbr as item_nbr,
  CASE WHEN t.unit_sales > 0 THEN LN(t.unit_sales + 1) ELSE 0 END unit_sales
FROM
  ma8dw.mst_data as m
LEFT OUTER JOIN
  corpora2.train as t
ON
  m.date = t.date AND m.store_nbr = t.store_nbr AND m.item_nbr = t.item_nbr
;

先程、作ったマスターデータにトレーニングデータをジョインしておきます。

前回と同じように1を足して対数をとります。

トレーニングデータも日付、store_nbr、item_nbrで一意になってるんですね。

Query3:window関数を使って期間を絞ってunit_salesの平均を出す

-- dest. table: ma8dw.train_2017_ext_ma_last
SELECT
  *
FROM
(
SELECT
  ROW_NUMBER() OVER(partition by store_nbr, item_nbr order by date desc) row_num,
  date,
  store_nbr,
  item_nbr,
  unit_sales as ma001,
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ma003,
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma007,
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as ma014,
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) as ma028,
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 55 PRECEDING AND CURRENT ROW) as ma056,
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 111 PRECEDING AND CURRENT ROW) as ma112,
  --AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 225 PRECEDING AND CURRENT ROW) as ma226
  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ma226
FROM
  ma8dw.train_2017_ext
)
WHERE
  row_num = 1

OVERが分析期間となる。今回はorder byしてROWS BETWEEN hogehooge PRECEDING AND CURRENT ROWでwindowを設定して平均を出している。

row_num = 1で、row_numでorder by date descしているので、一番新しいmoving average（window内の平均値だと思う）だけ取得することができる。

Query4:中央値を計算する

-- dest. table: ma8dw.mst_2017_ma8
CREATE TEMPORARY FUNCTION median(x ARRAY<FLOAT64>)
RETURNS FLOAT64
LANGUAGE js AS """
  var center = (x.length / 2) | 0;
  var x_sorted = x.sort();
  if (x_sorted.length % 2) {
    return x_sorted[center];
  } else {
    return (x_sorted[center - 1] + x_sorted[center]) / 2;
  }
""";
SELECT
  store_nbr,
  item_nbr,
  median([ma001, ma003, ma007, ma014, ma028, ma056, ma112, ma226]) as unit_sales
FROM
  `ma8dw.train_2017_ext_ma_last`

先程、window関数で計算した平均値の配列から中央値を計算します。

SQLでjsが書けることをはじめてしりましたが、median関数というのを準備しています。

Query5-1:unit_salesの平均値を計算する

何故か、Query5の箇所だけ動画とコードが違うので私の解釈となります！

-- dest. table: ma8dw.train_2017_mean_dow
SELECT
  store_nbr, item_nbr, dow,
  AVG(unit_sales) as unit_sales
FROM
(
  SELECT
    store_nbr, item_nbr, FORMAT_DATE('%a', date) as dow,
    CASE WHEN unit_sales > 0 THEN LN(unit_sales + 1) ELSE 0 END unit_sales
  FROM
    corpora2.train
  WHERE
    date >= '2017-01-01'
)
GROUP BY
  store_nbr, item_nbr, dow

まず、日付、store_nbr、item_nbrの組み合わせでunit_salesに1を足して対数をとった値の平均値を計算します。

しかし、これはマスターデータところで解決しているのでは？と思いながら…

Query5-2:store_nbrとitem_nbrの組み合わせのsales_unitの平均値を計算する

-- dest. table: ma8dw.train_2017_mean_week
SELECT
  store_nbr, item_nbr,
  AVG(unit_sales) as unit_sales
FROM
  ma8dw.train_2017_mean_dow
GROUP BY
  store_nbr, item_nbr

次に、日付を無視して2017年のsales_unitをstore_nbrとitem_nbrでgroup byして平均値を計算します。

ただ、weekと命名されているのが気になるんです…

Query5-3:テストデータにジョインして、unit_salesを再計算する

-- dest. table: ma8dw.submit_ma8dw
SELECT
  t.id as id,
  CASE
   WHEN a.unit_sales is not null and d.unit_sales is not null and w.unit_sales > 0 THEN
   EXP(a.unit_sales * d.unit_sales / w.unit_sales) - 1
   WHEN  a.unit_sales is not null THEN
   EXP(a.unit_sales) - 1
   ELSE
   0
  END as unit_sales
FROM
  (
  SELECT
  id,
  date,
  FORMAT_DATE('%a', date) as dow,
  store_nbr,
  item_nbr
  FROM
    corpora2.test
  ) as t
LEFT OUTER JOIN
  ma8dw.mst_2017_ma8 as a
ON
  t.item_nbr = a.item_nbr AND t.store_nbr = a.store_nbr
LEFT OUTER JOIN
  ma8dw.train_2017_mean_dow as d
ON
  t.item_nbr = d.item_nbr AND t.store_nbr = d.store_nbr AND t.dow = d.dow
LEFT OUTER JOIN
  ma8dw.train_2017_mean_week as w
ON
  t.item_nbr = w.item_nbr AND t.store_nbr = w.store_nbr

対数をとっていたものをもとに戻すために、exponentialするのですが、そのメソッド内で少し計算がされています。

定義は以下の通り。

a.unit_sales: testデータのunit_sales
d.unit_sales: 特定日のunit_salesの平均値
w.unit_sales：　2017年のunit_salesの平均値

a.unit_sales、d.unit_sales、w.unit_salesの全てが存在する場合はEXP(a.unit_sales * d.unit_sales / w.unit_sales) - 1と計算します。

testデータのsales_unitに、特定日のunit_salesの平均値と2017年のunit_salesの平均値の比をかけています。

上記以外でa.unit_salesが存在する場合は、EXP(a.unit_sales) - 1と計算します。そのままですね。

a.unit_salesが存在しない場合は、0という風に計算します。

Query6:デバッグ

-- for debug, you need to import this kernle submit to mean_baseline.submit_kernel
SELECT
  s.id,s.unit_sales, k.unit_sales as k_unit_sales,
  ABS(s.unit_sales - k.unit_sales) as diff
FROM
  ma8dw.submit_ma8dw_2 as s
LEFT OUTER JOIN
  ma8dw.sumit_kernel as k
ON
  s.id = k.id
ORDER BY
  diff desc
limit 10

デバッグようのクエリですね。

kernelと値が一致しているか確認します。

プラス

sparkとかHadoopとかが使えると良い