{"metadata": {"language_info": {"name": "python", "mimetype": "text/x-python", "codemirror_mode": {"name": "ipython", "version": 3}, "pygments_lexer": "ipython3", "file_extension": ".py", "version": "3.6.3", "nbconvert_exporter": "python"}, "kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}}, "nbformat_minor": 1, "nbformat": 4, "cells": [{"metadata": {"_uuid": "0dd6b7f01a7c53fc2a0ce04a3319461ff5eaeb30", "_cell_guid": "d99ce439-4b8d-4082-ae7c-6067395abb75"}, "cell_type": "markdown", "source": ["# Introduction\n", "\n", "I translate these kernels into Google BigQuery SQL.\n", "\n", "* [Shine Lee's Mean Baseline (LB ~ .59)](https://www.kaggle.com/ceshine/mean-baseline-lb-59/)\n", "* [Paulo Pinto's Log Moving Averages Forecasting (LB=0.546)](https://www.kaggle.com/paulorzp/log-moving-averages-forecasting-lb-0-546)\n", "\n", "# Tutorial Video\n", "I recoded my submission flow, like downloading data, inmporting data into BQ, coding SQL and making a submission. I upload it to Youtube as a Kaggle and BigQuery Tutorial video.\n", "\n", "I am not a Google salesman, but I love BigQuery. It's cheap and quite fast. I used BQ in many competitions."]}, {"metadata": {"_uuid": "56048ce26bb85fe8607784058101a458fadbb47d", "_cell_guid": "522893de-29b3-4b0a-91a0-20159d37a5e3", "collapsed": true}, "cell_type": "code", "source": ["from IPython.display import HTML\n", "HTML('<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/oNH_rhf32y0\" frameborder=\"0\" allowfullscreen></iframe>')"], "outputs": [], "execution_count": null}, {"metadata": {"_uuid": "23920ae72721d482c77012063b7c46a1af708982", "_cell_guid": "6e7f2169-ec8f-410d-8471-10e2e33a72c5"}, "cell_type": "markdown", "source": ["I am sorry that I made it in Japanese. I am making English sub.  \n", "\n", "I mentioned [Heads or Tails' Shopping for Insights - Favorita EDA](https://www.kaggle.com/headsortails/shopping-for-insights-favorita-eda) in the video. Thank these kernel authors for making such nice kernels.\n", "\n", "The SQL code in the video is also available here: https://github.com/tkm2261/kaggle-youtube-corpora ."]}, {"metadata": {"_uuid": "a183756cbac8a0f6792ec2b72ee6bb600b776e7a", "_cell_guid": "530a6fc9-8fdc-44c0-ae51-e85d25ea7a61"}, "cell_type": "markdown", "source": ["# SQL code for reproducing Shine Lee's Mean Baseline"]}, {"metadata": {"_uuid": "560f947d6be4e26155bde0afb0831aca18c78f2d", "_cell_guid": "01663d51-943b-4fc5-affa-756ccd12966a"}, "cell_type": "markdown", "source": ["```SQL\n", "-- dest. table: mean_baseline.num_promotion\n", "SELECT\n", "  item_nbr, store_nbr,\n", "  SUM(CASE WHEN onpromotion = 'True' THEN true ELSE false END) as num_onpromotion,\n", "  14 - SUM(CASE WHEN onpromotion = 'True' THEN true ELSE false END) as num_onnopromotion\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  id >= 124035459\n", "GROUP BY\n", "  item_nbr, store_nbr\n", "\n", "-- dest. table: mean_baseline.train_logsum_2017_aug\n", "SELECT\n", "  item_nbr, store_nbr,\n", "  CASE WHEN onpromotion = 'True' THEN true ELSE false END as onpromotion,\n", "  SUM(CASE WHEN unit_sales < 0 THEN 0 ELSE LN(unit_sales + 1) END) as unit_sales\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  id >= 124035459\n", "GROUP BY\n", "  item_nbr, store_nbr, onpromotion\n", "\n", "\n", "-- dest. table: mean_baseline.train_logavg_2017_aug\n", "SELECT\n", "  t.item_nbr as item_nbr,\n", "  t.store_nbr as store_nbr,\n", "  t.onpromotion as onpromotion,\n", "  CASE WHEN t.onpromotion = true THEN\n", "    t.unit_sales / num_onpromotion\n", "  ELSE\n", "    t.unit_sales / num_onnopromotion END as unit_sales\n", "FROM\n", "  mean_baseline.train_logsum_2017_aug as t\n", "LEFT OUTER JOIN\n", "  mean_baseline.num_promotion as p\n", "ON\n", "  t.item_nbr = p.item_nbr AND t.store_nbr = p.store_nbr\n", "\n", "\n", "-- dest. table: mean_baseline.submit\n", "SELECT\n", "  t.id as id,\n", "  CASE WHEN a.unit_sales is null THEN 0 ELSE EXP(a.unit_sales) - 1 END as unit_sales\n", "FROM\n", "  corpora2.test as t\n", "LEFT OUTER JOIN\n", "  mean_baseline.train_logavg_2017_aug as a\n", "ON\n", "  t.item_nbr = a.item_nbr AND t.store_nbr = a.store_nbr AND\n", "  t.onpromotion = a.onpromotion\n", "\n", "-- for debug, you need to import Shine Lee's kernle submit file to mean_baseline.submit_kernel\n", "SELECT\n", "  s.id,\n", "  ABS(s.unit_sales - k.unit_sales) as diff\n", "FROM\n", "  mean_baseline.submit as s\n", "LEFT OUTER JOIN\n", "  mean_baseline.submit_kernel as k\n", "ON\n", "  s.id = k.id\n", "ORDER BY\n", "  diff desc\n", "limit 10\n", "```"]}, {"metadata": {"_uuid": "e5fa387bdeeb1fe1cb5e50c8e012ba28b04728c9", "_cell_guid": "28fd8bf7-ec88-4b7f-bf6b-f94c8799d481"}, "cell_type": "markdown", "source": ["# SQL code for reproducing Paulo Pinto's Log Moving Averages Forecasting"]}, {"metadata": {"_uuid": "689fe8f0ac7dfb921768b4b7e7270801a66777ef", "_cell_guid": "dd42deb6-4f90-420d-893f-4004c9c9a02a"}, "cell_type": "markdown", "source": ["```SQL\n", "-- dest. table: ma8.mst_data\n", "SELECT\n", "  *\n", "FROM\n", "(\n", "SELECT\n", "  distinct(date)\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  date >= '2017-01-01'\n", "),\n", "(\n", "SELECT\n", "  distinct(store_nbr)\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  date >= '2017-01-01'\n", "),\n", "(\n", "SELECT\n", "  distinct(item_nbr)\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  date >= '2017-01-01'\n", ")\n", "\n", "-- dest. table: ma8.train_2017_ext\n", "SELECT\n", "  m.date as date,\n", "  m.store_nbr as store_nbr,\n", "  m.item_nbr as item_nbr,\n", "  CASE WHEN t.unit_sales > 0 THEN LN(t.unit_sales + 1) ELSE 0 END unit_sales\n", "FROM\n", "  ma8.mst_data as m\n", "LEFT OUTER JOIN\n", "  corpora2.train as t\n", "ON\n", "  m.date = t.date AND m.store_nbr = t.store_nbr AND m.item_nbr = t.item_nbr\n", ";\n", "\n", "-- dest. table: ma8.train_2017_ext_ma_last\n", "SELECT\n", "  *\n", "FROM\n", "(\n", "SELECT\n", "  ROW_NUMBER() OVER(partition by store_nbr, item_nbr order by date desc) row_num,\n", "  date,\n", "  store_nbr,\n", "  item_nbr,\n", "  unit_sales as ma001,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ma003,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma007,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as ma014,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) as ma028,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 55 PRECEDING AND CURRENT ROW) as ma056,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 111 PRECEDING AND CURRENT ROW) as ma112,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 223 PRECEDING AND CURRENT ROW) as ma224\n", "FROM\n", "  ma8.train_2017_ext\n", ")\n", "WHERE\n", "  row_num = 1\n", "\n", "-- dest. table: ma8.mst_2017_ma8\n", "CREATE TEMPORARY FUNCTION median(x ARRAY<FLOAT64>)\n", "RETURNS FLOAT64\n", "LANGUAGE js AS \"\"\"\n", "  var center = (x.length / 2) | 0;\n", "  var x_sorted = x.sort();\n", "  if (x_sorted.length % 2) {\n", "    return x_sorted[center];\n", "  } else {\n", "    return (x_sorted[center - 1] + x_sorted[center]) / 2;\n", "  }\n", "\"\"\";\n", "SELECT\n", "  store_nbr,\n", "  item_nbr,\n", "  EXP(median([ma001, ma003, ma007, ma014, ma028, ma056, ma112, ma224])) - 1 as unit_sales\n", "FROM\n", "  `ma8.train_2017_ext_ma_last`\n", "\n", "\n", "-- dest. table: mean_baseline.submit_ma8\n", "SELECT\n", "  t.id as id,\n", "  CASE WHEN a.unit_sales is null THEN 0 ELSE a.unit_sales END as unit_sales\n", "FROM\n", "  corpora2.test as t\n", "LEFT OUTER JOIN\n", "  ma8.mst_2017_ma8 as a\n", "ON\n", "  t.item_nbr = a.item_nbr AND t.store_nbr = a.store_nbr\n", "\n", "-- for debug, you need to import Paulo Pinto's kernle submit file to mean_baseline.submit_kernel\n", "SELECT\n", "  s.id,\n", "  ABS(s.unit_sales - k.unit_sales) as diff\n", "FROM\n", "  ma8.submit_ma8 as s\n", "LEFT OUTER JOIN\n", "  ma8.submit_kernel as k\n", "ON\n", "  s.id = k.id\n", "ORDER BY\n", "  diff desc\n", "limit 10\n", "```"]}]}