XGBoostPredict Example: Binary Classification, Sparse Format | Teradata Vantage - XGBoostPredict Example: Binary Classification, Sparse Format - Teradata Vantage

Input

• InputTable: sparse_housing_test_binary, created by inputting housing_test_binary (input table for XGBoostPredict Example: Binary Classification, Dense Format) 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: Binary Classification, Sparse Format

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.