This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH train_ordered as ( | |
select * from training2 | |
order by rowid asc | |
), | |
train_quantified as ( | |
select | |
t0.rowid, | |
t0.sales, | |
t2.*, | |
t0.competitiondistance |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT OVERWRITE TABLE prediction | |
SELECT | |
id, | |
EXP(predicted)-1 as predicted | |
FROM( | |
SELECT | |
id, | |
avg(predicted) AS predicted | |
FROM( | |
SELECT |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
id, | |
predicted as sales | |
FROM | |
prediction | |
ORDER BY id ASC; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
Month, | |
Group1, | |
Group2, | |
Group3, | |
CONCAT(Group1, “-”, Group2) as NewGroup, | |
SUM(Usage) as total_usage | |
FROM usage | |
GROUP BY 1, 2, 3, 4, 5 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
Month, | |
CONCAT(Group1, “-”, Group2) as NewGroup, | |
SUM(Usage) as total_usage | |
FROM usage | |
GROUP BY 1, 2 |
OlderNewer