This example uses home sales data to create a model that predicts home style, which can be input to the AdaBoostPredict Example: Use Model to Predict Home Style.
Input
- AttributeTable: housing_train_att, created by inputting housing_train (the raw data for DecisionForest Example: TreeType ('classification'), OutOfBag ('false')) to the Unpivoting function:
CREATE MULTISET TABLE housing_train_att AS ( SELECT * FROM Unpivoting ( ON housing_train USING TargetColumns ('price', 'lotsize', 'bedrooms', 'bathrms', 'stories', 'driveway', 'recroom', 'fullbase', 'gashw', 'airco', 'garagepl', 'prefarea') Accumulate ('sn') ) AS dt ) WITH DATA;
- CategoricalAttributeTable: housing_cat, created and populated with this code:
CREATE MULTISET TABLE housing_cat (attribute VARCHAR); INSERT INTO housing_cat VALUES ('driveway'); INSERT INTO housing_cat VALUES ('recroom'); INSERT INTO housing_cat VALUES ('fullbase'); INSERT INTO housing_cat VALUES ( 'gashw'); INSERT INTO housing_cat VALUES ('airco'); INSERT INTO housing_cat VALUES ('prefarea');
- ResponseTable: housing_train_response, created by selecting the columns sn and homestyle from housing_train:
CREATE MULTISET TABLE housing_train_response AS ( SELECT sn, homestyle AS response FROM housing_train ) WITH DATA;
This query returns the following table:
SELECT * FROM housing_train_att ORDER BY 1, 2;
sn | attribute | value_col |
---|---|---|
1 | airco | no |
1 | bathrms | 1 |
1 | bedrooms | 3 |
1 | driveway | yes |
1 | fullbase | yes |
1 | garagepl | 1 |
1 | gashw | no |
1 | lotsize | 5850.0 |
1 | prefarea | no |
1 | price | 42000.0 |
1 | recroom | no |
1 | stories | 2 |
2 | airco | no |
2 | bathrms | 1 |
2 | bedrooms | 2 |
2 | fullbase | no |
2 | garagepl | 0 |
2 | gashw | no |
2 | lotsize | 4000.0 |
2 | prefarea | no |
2 | price | 38500.0 |
2 | recroom | no |
2 | stories | 1 |
... | ... | ... |
This query returns the following table:
SELECT * FROM housing_cat ORDER BY 1;
attribute |
---|
airco |
driveway |
fullbase |
gashw |
prefarea |
recroom |
This query returns the following table:
SELECT * FROM housing_train_response ORDER BY 1;
sn | response |
---|---|
1 | Classic |
2 | Classic |
3 | Classic |
4 | Eclectic |
5 | Eclectic |
6 | Eclectic |
7 | Eclectic |
8 | Eclectic |
9 | Eclectic |
10 | Eclectic |
... | ... |
SQL Call
This call creates the model, abd_model, using the default values for the optional syntax elements.
SELECT * FROM AdaBoost ( ON housing_train_att AS AttributeTable ON housing_cat AS CategoricalAttributeTable ON housing_train_response AS ResponseTable OUT TABLE OutputTable (abd_model) USING IDColumns ('sn') AttributeNameColumns ('attribute') AttributeValueColumn ('value_col') ResponseColumn ('response') IterNum (20) NumSplits (10) MaxDepth (3) MinNodeSize (100) ) AS dt ;
Output
Because the syntax element IterNum has the value 20, the function builds 20 classification trees.
message ------------------------------------------------------------- Running 20 round AdaBoost, computing 20 classification trees. AdaBoost model created.
SELECT * FROM abd_model ORDER BY 1,3;
classifier_id classifier_weight node_id node_label node_majorfreqs attribute split_value left_bucket right_bucket left_label right_label left_majorfreqs right_majorfreqs ------------- ------------------ ------- ---------- -------------------- --------- ----------- ----------- ------------ ---------- ----------- --------------------- -------------------- 1 3.262482509964738 0 eclectic 0.601626016260163 price 53900.0 classic eclectic 0.28455284552845556 0.544715447154472 1 3.262482509964738 1 classic 0.28455284552845556 price 48500.0 classic classic 0.2256097560975614 0.05894308943089416 1 3.262482509964738 2 eclectic 0.5447154471544708 price 104900.0 eclectic bungalow 0.5447154471544708 0.09959349593495924 1 3.262482509964738 5 eclectic 0.5447154471544708 stories 4.0 eclectic eclectic 0.5142276422764215 0.030487804878049363 2 3.4876460124354876 0 eclectic 0.7288110867979543 price 80000.0 eclectic bungalow 0.6697301239970743 0.1690736688548506 2 3.4876460124354876 1 eclectic 0.6697301239970801 price 48500.0 classic eclectic 0.0809628008752734 0.6697301239970801 2 3.4876460124354876 2 bungalow 0.16907366885485062 garagepl 1.0 eclectic bungalow 0.02625820568927791 0.16469730123997098 2 3.4876460124354876 4 eclectic 0.6697301239970824 bedrooms 6.0 eclectic classic 0.6697301239970804 7.293946024799422E-4 3 1.6906105382771976 0 eclectic 0.6259284659900001 price 54500.0 classic eclectic 0.2651826886650764 0.4349583937505778 3 1.6906105382771976 1 classic 0.2651826886650767 fullbase NaN yes no eclectic classic 0.060894884269497324 0.23470627424854384 3 1.6906105382771976 2 eclectic 0.43495839375057627 price 88000.0 eclectic eclectic 0.252841243092843 0.18211715065773326 3 1.6906105382771976 4 classic 0.23470627424854384 bedrooms 3.0 classic classic 0.09599809277475274 0.1387081814737911 4 1.6740798084368844 0 eclectic 0.548083894487584 price 104900.0 eclectic bungalow 0.5480838944875873 0.1527284245574851 4 1.6740798084368844 1 eclectic 0.548083894487584 price 80000.0 eclectic eclectic 0.3705785106889343 0.17750538379864966 4 1.6740798084368844 3 eclectic 0.37057851068893205 stories 2.0 eclectic classic 0.2837824293909481 0.11327699314940258 5 1.7984453313028665 0 eclectic 0.4236017713806748 price 80000.0 eclectic bungalow 0.34224743072046554 0.3552653314369221 5 1.7984453313028665 1 eclectic 0.34224743072046454 stories 2.0 classic eclectic 0.16921573899590506 0.21218415072304744 5 1.7984453313028665 2 bungalow 0.3552653314369222 fullbase NaN yes no eclectic bungalow 0.04305447851181635 0.32129005293930696 5 1.7984453313028665 3 classic 0.1692157389959053 lotsize 3450.0 classic classic 0.030125978938623593 0.13908976005728171 5 1.7984453313028665 4 eclectic 0.21218415072304758 price 48500.0 classic eclectic 0.0055070814635300004 0.21218415072304758 6 2.0065403177996077 0 eclectic 0.5644776404927336 price 80000.0 eclectic bungalow 0.4427273772859118 0.23361427012906577 6 2.0065403177996077 1 eclectic 0.44272737728591155 stories 2.0 eclectic classic 0.3485804105336721 0.1268263808769799 6 2.0065403177996077 2 bungalow 0.23361427012906574 airco NaN no yes eclectic bungalow 0.07699456096423427 0.20103200371969665 6 2.0065403177996077 3 eclectic 0.34858041053367234 price 48500.0 classic eclectic 0.012434424878136811 0.34858041053367234 6 2.0065403177996077 4 classic 0.12682638087697978 gashw NaN yes no eclectic classic 0.022236554394313657 0.12680244210077782 7 1.646659147091757 0 eclectic 0.5564240112347473 price 88000.0 eclectic bungalow 0.47511111964895203 0.18752907116729645 7 1.646659147091757 1 eclectic 0.47511111964895264 stories 2.0 classic eclectic 0.20233814698502278 0.30097983780777077 7 1.646659147091757 3 classic 0.20233814698502273 price 54500.0 classic eclectic 0.20233814698502292 0.030973858138061583 7 1.646659147091757 4 eclectic 0.3009798378077729 price 54500.0 eclectic eclectic 0.1925904662326647 0.10838937157510817 8 1.8676613987295947 0 eclectic 0.6912461504770633 price 104900.0 eclectic bungalow 0.6912461504770628 0.0391404220197992 8 1.8676613987295947 1 eclectic 0.6912461504770633 lotsize 3450.0 classic eclectic 0.08554732714066553 0.6392722632068916 8 1.8676613987295947 4 eclectic 0.6392722632068915 price 53900.0 eclectic eclectic 0.38055689268558895 0.2587153705213025 9 1.7879293221967405 0 eclectic 0.4257232170550743 price 53900.0 classic bungalow 0.4231550269731866 0.15112175597531124 9 1.7879293221967405 1 classic 0.42315502697318647 lotsize 3640.0 eclectic classic 0.17778729163933016 0.3491808070342713 9 1.7879293221967405 2 bungalow 0.15112175597531124 recroom NaN yes no bungalow eclectic 0.10999558364575182 0.10111729104887975 9 1.7879293221967405 6 eclectic 0.10111729104887963 price 104900.0 eclectic bungalow 0.1011172910488796 0.011200186626450753 10 1.6075683914971037 0 eclectic 0.5144703493395812 price 80000.0 eclectic bungalow 0.405117670093333 0.13349028834796411 10 1.6075683914971037 1 eclectic 0.4051176700933345 lotsize 3640.0 classic eclectic 0.19669900604556773 0.31422433545811096 10 1.6075683914971037 2 bungalow 0.1334902883479642 bedrooms 4.0 eclectic bungalow 0.10052768516019424 0.06403971117270774 10 1.6075683914971037 3 classic 0.19669900604556775 price 53900.0 classic eclectic 0.19669900604556767 0.011800954217085394 10 1.6075683914971037 4 eclectic 0.3142243354581112 lotsize 6600.0 eclectic classic 0.3117246574276332 0.02911201258006993 11 1.8472017973241108 0 eclectic 0.4086889990570311 price 80000.0 classic bungalow 0.3995746742922139 0.19173632667289012 11 1.8472017973241108 1 classic 0.39957467429221377 lotsize 3640.0 eclectic classic 0.1898127327641567 0.3077327278887372 11 1.8472017973241108 2 bungalow 0.19173632667289003 lotsize 5500.0 eclectic bungalow 0.014934969351423626 0.19062281420344424 11 1.8472017973241108 3 eclectic 0.18981273276415672 garagepl 1.0 eclectic classic 0.17950315083112764 0.02875674789181465 11 1.8472017973241108 4 classic 0.3077327278887375 garagepl 2.0 classic eclectic 0.3048328513332632 0.04160019619157111 11 1.8472017973241108 6 bungalow 0.19062281420344443 stories 3.0 bungalow bungalow 0.16081878105873448 0.029804033144709946 12 1.9027789437396634 0 eclectic 0.5835782146822769 price 53900.0 eclectic eclectic 0.40873669128969553 0.17484152339258136 12 1.9027789437396634 1 eclectic 0.4087366912896956 bedrooms 3.0 eclectic classic 0.3067621277139097 0.2577616707695073 12 1.9027789437396634 2 eclectic 0.17484152339258052 bedrooms 3.0 bungalow eclectic 0.027099911687511153 0.16996765823956902 12 1.9027789437396634 6 eclectic 0.1699676582395691 price 104900.0 eclectic bungalow 0.16996765823956936 0.008642418888452352 13 1.805080722860764 0 eclectic 0.5163346059155665 price 80000.0 eclectic bungalow 0.44563768635136314 0.16325003708571337 13 1.805080722860764 1 eclectic 0.4456376863513629 bedrooms 3.0 classic eclectic 0.20886408749791152 0.30415009324982445 13 1.805080722860764 2 bungalow 0.16325003708571328 fullbase NaN no yes bungalow eclectic 0.16031407394228503 0.018830999936693515 13 1.805080722860764 3 classic 0.20886408749791152 garagepl 1.0 eclectic classic 0.13493987110703912 0.106362342777085 13 1.805080722860764 4 eclectic 0.30415009324982445 airco NaN no yes eclectic classic 0.2891733311445946 0.042868788827266496 14 1.9999442299120962 0 classic 0.5271880873666717 price 53900.0 classic eclectic 0.5271880873666719 0.16960631462670026 14 1.9999442299120962 1 classic 0.5271880873666718 gashw NaN yes no eclectic classic 0.037297539385064166 0.5268465118289741 14 1.9999442299120962 2 eclectic 0.16960631462669978 price 95000.0 eclectic bungalow 0.1439096759056536 0.07892296564985647 14 1.9999442299120962 4 classic 0.5268465118289742 bedrooms 3.0 classic classic 0.3229051416093831 0.20394137021959113 15 1.8148455453578554 0 eclectic 0.742350161248668 bedrooms 3.0 eclectic eclectic 0.20515560028480057 0.5371945609638675 15 1.8148455453578554 1 eclectic 0.2051556002848005 airco NaN yes no eclectic eclectic 0.040822180590534995 0.16433341969426551 15 1.8148455453578554 2 eclectic 0.5371945609638688 lotsize 3450.0 eclectic eclectic 0.09203702512412008 0.44515753583974865 15 1.8148455453578554 4 eclectic 0.16433341969426554 driveway NaN yes no classic eclectic 0.13023567509998896 0.03754718251923919 15 1.8148455453578554 6 eclectic 0.4451575358397485 driveway NaN no yes classic eclectic 0.008849615359914235 0.4448119310764073 16 1.950875441375468 0 eclectic 0.6168268098150248 lotsize 3150.0 classic eclectic 0.15413178524395588 0.589273464609344 16 1.950875441375468 2 eclectic 0.5892734646093444 price 88000.0 eclectic bungalow 0.533798214503899 0.09070479290585637 16 1.950875441375468 5 eclectic 0.5337982145038996 stories 2.0 eclectic eclectic 0.4728359981488058 0.06096221635509386 17 1.3867857520305669 0 classic 0.48260033144079995 price 53900.0 classic eclectic 0.4826003314408001 0.18010946394429106 17 1.3867857520305669 1 classic 0.48260033144079995 price 48500.0 classic classic 0.0738779896357663 0.40872234180503364 17 1.3867857520305669 2 eclectic 0.18010946394429037 price 104900.0 eclectic bungalow 0.18010946394429037 0.004066060317112978 17 1.3867857520305669 5 eclectic 0.18010946394429037 recroom NaN no yes eclectic eclectic 0.11780150517361454 0.062307958770675825 18 1.9213045242690043 0 eclectic 0.6871546217475291 price 80000.0 eclectic eclectic 0.6018440797437357 0.0853105420037934 18 1.9213045242690043 1 eclectic 0.6018440797437355 price 48500.0 classic eclectic 0.036932945808784555 0.6018440797437355 18 1.9213045242690043 2 eclectic 0.08531054200379388 stories 2.0 eclectic bungalow 0.03617934985643258 0.06615818788305579 18 1.9213045242690043 4 eclectic 0.6018440797437355 lotsize 6600.0 eclectic classic 0.5995582871473516 0.02000038456926448 18 1.9213045242690043 6 bungalow 0.06615818788305576 lotsize 10500.0 bungalow eclectic 0.06612494328463332 0.014699992941998974 19 1.6885236443596874 0 classic 0.5670633628328706 price 53900.0 classic eclectic 0.5670633628328708 0.13104787961357944 19 1.6885236443596874 1 classic 0.5670633628328706 gashw NaN yes no eclectic classic 0.018078058437651923 0.5667618678449464 19 1.6885236443596874 2 eclectic 0.13104787961357883 stories 3.0 eclectic eclectic 0.0399968690681781 0.09105101054540074 19 1.6885236443596874 4 classic 0.5667618678449466 bedrooms 3.0 classic classic 0.25140587641564466 0.315355991429302 19 1.6885236443596874 5 eclectic 0.039996869068178134 garagepl 2.0 bungalow eclectic 0.03771800307592036 0.016539625782621678 20 1.7347581671638745 0 eclectic 0.7063755333565411 price 54500.0 eclectic eclectic 0.6142772637076755 0.09209826964886558 20 1.7347581671638745 1 eclectic 0.6142772637076755 bedrooms 4.0 eclectic classic 0.6118950373433272 0.017449315120367792 20 1.7347581671638745 2 eclectic 0.0920982696488646 lotsize 6360.0 eclectic eclectic 0.0689537257005812 0.0231445439482834 20 1.7347581671638745 3 eclectic 0.6118950373433273 price 47600.0 classic eclectic 0.007562368115208696 0.6118950373433273 20 1.7347581671638745 5 eclectic 0.06895372570058118 airco NaN no yes bungalow eclectic 0.008938565591992029 0.06074999040370812 20 1.7347581671638745 6 eclectic 0.023144543948283317 airco NaN yes no bungalow eclectic 0.017289569267637608 0.015275881566152382
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.