1.1 - 8.10 - AdaBoost Example: Create Model to Predict Home Style - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

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;
housing_train_att
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;
housing_cat
attribute
airco
driveway
fullbase
gashw
prefarea
recroom

This query returns the following table:

SELECT * FROM housing_train_response ORDER BY 1;
housing_train_response
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.