td_intern rossmann

train_quantfy.sql

WITH train_ordered as (
    select * from training2
    order by rowid asc
), 
train_quantified as (
  select
    t0.rowid,
    t2.*
  from
    train_ordered t0 
    LATERAL VIEW quantify( true, stateholiday, store, promo, dayofweek, date, schoolholiday, promo2sinceweek,
competitionopensinceyear, assortment, promo2sinceyear, competitiondistance, promointerval, promo2,
storetype, competitionopensincemonth,
sales, year, month, day
) t2 as stateholiday, store, promo, dayofweek, date, schoolholiday, promo2sinceweek,
competitionopensinceyear, assortment, promo2sinceyear, competitiondistance, promointerval, promo2,
storetype, competitionopensincemonth,
sales, year, month, day
) INSERT OVERWRITE TABLE training3
SELECT
  t1.rowid,
  ARRAY( t1.stateholiday, t1.store, t1.promo, t1.dayofweek, t1.schoolholiday,
  IF(t1.promo2sinceweek IS NULL, 0, t1.promo2sinceweek), 
  IF(t1.competitionopensinceyear IS NULL, 0, t1.competitionopensinceyear), 
  t1.assortment,
  IF(t1.promo2sinceyear IS NULL, 0, t1.promo2sinceyear), 
  IF(t1.competitiondistance IS NULL, 0, t1.competitiondistance), 
IF(t1.promointerval IS NULL, 0, t1.promointerval), t1.promo2, t1.storetype, 
IF(t1.competitionopensincemonth IS NULL, 0, t1.competitionopensincemonth),
t1.year, t1.month, t1.day
) AS features,
  LN(1+t1.sales) AS label, 
  t1.sales
FROM
  train_quantified t1
  order by rowid asc;