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

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

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

データの可視化

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

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

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

BigQuery

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

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

データインポート

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

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

Google Cloud Storage

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

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

目標

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

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

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

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

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

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

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

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

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

Query2:分散の分析

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

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

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

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

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

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

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

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

LEFT OUTER JOINtrain_logavg_2017_augテーブルsum_num_promotionテーブルをジョインする。

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

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

Query4: submitデータの準備

EXP (LOG (n)) = n

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

Query5: デバッグ

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

setting

setting

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

submit方法

submit

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

window関数

Query1:マスターを作る

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

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

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

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

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

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

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

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

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

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

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

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

Query4:中央値を計算する

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

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

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

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

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

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

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

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

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

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

対数をとっていたものをもとに戻すために、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:デバッグ

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

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

プラス

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

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です