1.1 - 8.10 - XGBoostPredict Example: Sparse Input Format - 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)

Input

  • InputTable: sparse_housing_test_binary, created by inputting housing_test_binary (input table for XGBoostPredict Example: Binary Classification) to the Unpivoting function:
    DROP TABLE sparse_housing_test_binary;
    CREATE MULTISET TABLE sparse_housing_test_binary AS ( 
      SELECT sn,homestyle, attribute, value_col FROM 
        Unpivoting (
          ON housing_test_binary
          USING
          TargetColumns (
            'price','lotsize','bedrooms','bathrms','stories',
            'garagepl','driveway','recroom','fullbase','gashw','airco','prefarea'
          )
          Accumulate ('sn','homestyle')
        ) AS dt
    ) WITH DATA;
  • ModelTable: sparse_housing_xgboost_model, output by XGBoost Example: Sparse Input Format
InputTable: sparse_housing_test_binary
sn homestyle attribute value_col
13 Classic price 27000.0
13 Classic lotsize 1700.0
13 Classic bedrooms 3
13 Classic bathrms 1
13 Classic stories 2
13 Classic garagepl 0
13 Classic driveway yes
13 Classic recroom no
13 Classic fullbase no
13 Classic gashw no
13 Classic airco no
13 Classic prefarea no
25 Classic price 42000.0
25 Classic lotsize 4960.0
25 Classic bedrooms 2
25 Classic bathrms 1
25 Classic stories 1
25 Classic garagepl 0
25 Classic driveway yes
25 Classic recroom no
25 Classic fullbase no

SQL Call

CREATE MULTISET TABLE sparse_housing_predict AS (
  SELECT * FROM XGBoost_Predict (
    ON sparse_housing_test_binary AS InputTable PARTITION BY sn
    ON sparse_housing_xgboost_model AS Model DIMENSION ORDER BY tree_id, iter, class_num
    USING
    AttributeNameColumn ('attribute')
    AttributeValueColumn ('value_col')
    IDColumn ('sn')
    NumBoostedTrees (1)
    OutputProb ('t')
    Accumulate ('homestyle')
  ) AS dt
) WITH DATA;

Output

SELECT * FROM sparse_housing_predict ORDER BY sn;
 sn  homestyle prediction prob               
 --- --------- ---------- ------------------ 
  13 classic   classic    0.6707321623751528
  16 classic   classic    0.6802196369290117
  25 classic   classic    0.6697030533275877
  38 eclectic  eclectic    0.599104917559982
  53 eclectic  eclectic   0.5844119355969892
 111 classic   classic    0.6663349841549067
 117 eclectic  eclectic   0.6119619284654599
 132 classic   classic    0.6808446318040215
 140 classic   classic    0.6904889279928939
 142 classic   classic    0.6562749646340886
 157 eclectic  eclectic   0.5958766743648317
 161 eclectic  eclectic   0.6150047360985926
 176 eclectic  eclectic   0.5752751525380305
 177 eclectic  eclectic   0.5901145665876577
 195 classic   classic    0.6771951966682854
 198 classic   classic    0.6781038486863248
 224 eclectic  eclectic    0.608146932276108
 234 classic   classic    0.6647769100201999
 237 classic   classic    0.6757075532754422
 239 classic   classic    0.6736816973452893
 249 classic   classic    0.6859277281807135
 251 classic   classic    0.6750519163012993
 254 eclectic  eclectic   0.5980274927196804
 255 eclectic  eclectic   0.6251361687462785
 260 classic   classic    0.6647465838551813
 274 eclectic  eclectic   0.5952706695267067
 294 classic   classic    0.6835990225587365
 301 eclectic  eclectic   0.5885785523544161
 306 eclectic  eclectic   0.6100821530647406
 317 eclectic  eclectic   0.5769831408669012
 340 eclectic  eclectic   0.6314031717596106
 353 eclectic  eclectic   0.5960781213821505
 355 eclectic  eclectic   0.6101385994331346
 364 eclectic  eclectic   0.5790929346873964
 401 eclectic  eclectic   0.5765403704997994
 403 eclectic  eclectic     0.60085682008034
 408 eclectic  eclectic   0.6180127551568644
 411 eclectic  eclectic    0.581000238433991
 440 eclectic  eclectic   0.5851477560582771
 441 eclectic  eclectic   0.5975718690762845
 443 eclectic  eclectic   0.5933467121168764
 459 classic   classic    0.6588541113295274
 463 classic   classic    0.6609464183496346
 469 eclectic  eclectic   0.6018135061442815
 472 eclectic  eclectic   0.5839143201785149
 540 eclectic  eclectic   0.5768592723117583

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.