こちらの動画をみて、コードを読んだ感想です!
まず!動画を見る前にこちらの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:プロモーションされた日とプロモーションされていない日を計算する
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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:分散の分析
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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としている)
計算の説明はこちらのサイトを参考にしました。
しかし「1を足すのがよい」といった理由は存在しない。
え
とはいえ、これで分散を分析できるようになった。
Query3:売上個数 / プロモーション日数を計算
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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データの準備
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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: デバッグ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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:マスターを作る
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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データをジョインする
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 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の平均を出す
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- 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:中央値を計算する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- 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の箇所だけ動画とコードが違うので私の解釈となります!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- 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の平均値を計算する
1 2 3 4 5 6 7 8 9 |
-- 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を再計算する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
-- 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:デバッグ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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とかが使えると良い