myui

run: +main

_export:
  td:
    apikey: ${TD_API_KEY}
    database: rossmann
    engine: hive

+main:

myui

run: +main

_export:
  td:
    apikey: ${TD_API_KEY}
    database: rossmann
    engine: hive

+main:
  +prepare:

myui

SELECT 
  id,
  predicted as sales
FROM
  prediction
ORDER BY id ASC;

myui

INSERT OVERWRITE TABLE prediction
SELECT 
  id,
  EXP(predicted)-1 as predicted
FROM(
  SELECT
     id,
     avg(predicted) AS predicted
  FROM(
    SELECT

myui

WITH train_test as (
      select
        1 as train_first, false as output_row,
	rowid, stateholiday, store, promo, dayofweek, schoolholiday, promo2sinceweek, competitionopensinceyear, assortment, promo2sinceyear, competitiondistance, promointerval, promo2, storetype, competitionopensincemonth, year, month, day
      from
        training2
      union all
      select
        2 as train_first, true as output_row,
	rowid, stateholiday, store, promo, dayofweek, schoolholiday, promo2sinceweek, competitionopensinceyear, assortment, promo2sinceyear, competitiondistance, promointerval, promo2, storetype, competitionopensincemonth, year, month, day

myui

INSERT OVERWRITE TABLE testing2
SELECT
  rowid() as rowid,
  t1.id,
  t1.stateholiday, t1.store, t1.promo, t1.dayofweek, t1.date, t1.schoolholiday,
  SUBSTR(t1.date,1,4) as year,
  SUBSTR(t1.date,6,2) as month,
  SUBSTR(t1.date,9,2) as day,
  t2.promo2sinceweek,
  t2.competitionopensinceyear,

myui

myui

INSERT OVERWRITE TABLE model 
  SELECT 
    -- C: Categorical Variable, Q: Quantitative Variable
    train_randomforest_regr(features, label, '-trees 20 -attrs C,C,C,C,C,C,C,C,C,C,C,C,C,C,C,C,Q')
  FROM
    training3
  UNION ALL
  SELECT 
    train_randomforest_regr(features, label, '-trees 20 -attrs C,C,C,C,C,C,C,C,C,C,C,C,C,C,C,C,Q')
  FROM

myui

WITH train_ordered as (
  select * from training2
  order by rowid asc
), 
train_quantified as (
  select
    t0.rowid,
    t0.sales,
    t2.*,
    t0.competitiondistance

myui

WITH t1 as (
  SELECT  
    rowid() as rowid,
    t.stateholiday, t.store, t.promo, t.dayofweek, t.date, t.schoolholiday, t.sales, 
    SUBSTR(t.date,1,4) as year,
    SUBSTR(t.date,6,2) as month,
    SUBSTR(t.date,9,2) as day
  FROM
    train_original t 
  WHERE sales != 0