Example: XGBoost for Regression | TD_XGBoost | Teradata Vantage - TD_XGBoost for Regression - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

This section shows the input table, SQL query, and output tables of an example using TD_XGBoost for regression.

InputTable

The input is a Boston housing dataset sample, with 3 feature columns and a target (response) column 'medv'.

ID medv col_1 col_2 col_3
1 13.9 23 8.14 0.84054
1 36 20 3.97 0.66351
1 23.2 0 4.05 0.07022
1 22.4 15 10.59 0.21719
1 20 0 6.91 0.18836
1 36 20 3.97 0.66351
1 43.5 20 3.97 0.5405
1 11.8 16 1.95 2.77974
1 26.6 0 4.49 0.05735

SQL Call

SELECT * FROM TD_XGBoost (
ON housing_sample partition by ANY
OUT TABLE MetaInformationTable(xgb_out)
USING
ResponseColumn('medv')
InputColumns('[2:4]')
MaxDepth(3)
MinNodeSize(1)
NumParallelTrees(1)
ModelType('REGRESSION')
Seed(1)
RegularizationLambda(1000)
LearningRate(0.8)
NumBoostRounds(3)
ColumnSampling(1.0)
) as dt;

Output

task_index tree_num iter tree_order regression_tree
---------- -------- ---- ---------- --------------- 
0           1        1    0         {"id_":1,"sum_":233.400000,"sumSq_":6964.260000,"size_":9,"maxDepth_":3,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":4.010000,"attr_":"col_2","type_":"REGRESSION_NUMERIC_SPLIT","score_":710.645000,"scoreImprove_":710.645000,"leftNodeSize_":3,"rightNodeSize_":6},"leftChi
{"id_":2,"sum_":115.500000,"sumSq_":4484.250000,"size_":3,"maxDepth_":2,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":0.602005,"attr_":"col_3","type_":"REGRESSION_NUMERIC_SPLIT","score_":37.500000,"scoreImprove_":37.500000,"leftNodeSize_":1,"rightNodeSize_":2},"leftChild_
{"id_":4,"sum_":43.500000,"sumSq_":1892.250000,"size_":1,"maxDepth_":0,"value_":43.500000,"nodeType_":"REGRESSION_LEAF","prediction_":0.034765},"rightChild_":
{"id_":5,"sum_":72.000000,"sumSq_":2592.000000,"size_":2,"maxDepth_":0,"value_":36.000000,"nodeType_":"REGRESSION_LEAF","prediction_":0.057485}},"rightChild_":
{"id_":3,"sum_":117.900000,"sumSq_":2480.010000,"size_":6,"maxDepth_":2,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":15.500000,"attr_":"col_1","type_":"REGRESSION_NUMERIC_SPLIT","score_":138.720000,"scoreImprove_":138.720000,"leftNodeSize_":4,"rightNodeSize_":2},"leftCh
{"id_":6,"sum_":92.200000,"sumSq_":2147.560000,"size_":4,"maxDepth_":1,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":0.063785,"attr_":"col_3","type_":"REGRESSION_NUMERIC_SPLIT","score_":16.803333,"scoreImprove_":16.803333,"leftNodeSize_":1,"rightNodeSize_":3},"leftChild_
{"id_":12,"sum_":26.600000,"sumSq_":707.560000,"size_":1,"maxDepth_":0,"value_":26.600000,"nodeType_":"REGRESSION_LEAF","prediction_":0.021259},"rightChild_":
{"id_":13,"sum_":65.600000,"sumSq_":1440.000000,"size_":3,"maxDepth_":0,"value_":21.866667,"nodeType_":"REGRESSION_LEAF","prediction_":0.052323}},"rightChild_":
{"id_":7,"sum_":25.700000,"sumSq_":332.450000,"size_":2,"maxDepth_":1,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":19.500000,"attr_":"col_1","type_":"REGRESSION_NUMERIC_SPLIT","score_":2.205000,"scoreImprove_":2.205000,"leftNodeSize_":1,"rightNodeSize_":1},"leftChild_"
{"id_":14,"sum_":11.800000,"sumSq_":139.240000,"size_":1,"maxDepth_":0,"value_":11.800000,"nodeType_":"REGRESSION_LEAF","prediction_":0.009431},"rightChild_":
{"id_":15,"sum_":13.900000,"sumSq_":193.210000,"size_":1,"maxDepth_":0,"value_":13.900000,"nodeType_":"REGRESSION_LEAF","prediction_":0.011109}}}}
0           1        2    0         {"id_":1,"sum_":233.051497,"sumSq_":6944.447140,"size_":9,"maxDepth_":3,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":4.010000,"attr_":"col_2","type_":"REGRESSION_NUMERIC_SPLIT","score_":709.380059,"scoreImprove_":709.380059,"leftNodeSize_":3,"rightNodeSize_":6},"leftChi
{"id_":2,"sum_":115.350265,"sumSq_":4472.955398,"size_":3,"maxDepth_":2,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":0.602005,"attr_":"col_3","type_":"REGRESSION_NUMERIC_SPLIT","score_":37.727542,"scoreImprove_":37.727542,"leftNodeSize_":1,"rightNodeSize_":2},"leftChild_
{"id_":4,"sum_":43.465235,"sumSq_":1889.226633,"size_":1,"maxDepth_":0,"value_":43.465235,"nodeType_":"REGRESSION_LEAF","prediction_":0.034737},"rightChild_":
{"id_":5,"sum_":71.885030,"sumSq_":2583.728765,"size_":2,"maxDepth_":0,"value_":35.942515,"nodeType_":"REGRESSION_LEAF","prediction_":0.057393}},"rightChild_":
{"id_":3,"sum_":117.701233,"sumSq_":2471.491742,"size_":6,"maxDepth_":2,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":15.500000,"attr_":"col_1","type_":"REGRESSION_NUMERIC_SPLIT","score_":137.788955,"scoreImprove_":137.788955,"leftNodeSize_":4,"rightNodeSize_":2},"leftCh
{"id_":6,"sum_":92.021772,"sumSq_":2139.572918,"size_":4,"maxDepth_":1,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":0.063785,"attr_":"col_3","type_":"REGRESSION_NUMERIC_SPLIT","score_":17.024614,"scoreImprove_":17.024614,"leftNodeSize_":1,"rightNodeSize_":3},"leftChild_
{"id_":12,"sum_":26.578741,"sumSq_":706.429487,"size_":1,"maxDepth_":0,"value_":26.578741,"nodeType_":"REGRESSION_LEAF","prediction_":0.021242},"rightChild_":
{"id_":13,"sum_":65.443031,"sumSq_":1433.143431,"size_":3,"maxDepth_":0,"value_":21.814344,"nodeType_":"REGRESSION_LEAF","prediction_":0.052198}},"rightChild_":
{"id_":7,"sum_":25.679461,"sumSq_":331.918824,"size_":2,"maxDepth_":1,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":19.500000,"attr_":"col_1","type_":"REGRESSION_NUMERIC_SPLIT","score_":2.201477,"scoreImprove_":2.201477,"leftNodeSize_":1,"rightNodeSize_":1},"leftChild_"
{"id_":14,"sum_":11.790569,"sumSq_":139.017527,"size_":1,"maxDepth_":0,"value_":11.790569,"nodeType_":"REGRESSION_LEAF","prediction_":0.009423},"rightChild_":
{"id_":15,"sum_":13.888891,"sumSq_":192.901296,"size_":1,"maxDepth_":0,"value_":13.888891,"nodeType_":"REGRESSION_LEAF","prediction_":0.011100}}}}
0           1        3    0         {"id_":1,"sum_":232.703615,"sumSq_":6924.700934,"size_":9,"maxDepth_":3,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":4.010000,"attr_":"col_2","type_":"REGRESSION_NUMERIC_SPLIT","score_":708.116416,"scoreImprove_":708.116416,"leftNodeSize_":3,"rightNodeSize_":6},"leftChi
{"id_":2,"sum_":115.200741,"sumSq_":4461.692021,"size_":3,"maxDepth_":2,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":0.602005,"attr_":"col_3","type_":"REGRESSION_NUMERIC_SPLIT","score_":37.955128,"scoreImprove_":37.955128,"leftNodeSize_":1,"rightNodeSize_":2},"leftChild_
{"id_":4,"sum_":43.430497,"sumSq_":1886.208097,"size_":1,"maxDepth_":0,"value_":43.430497,"nodeType_":"REGRESSION_LEAF","prediction_":0.034710},"rightChild_":
{"id_":5,"sum_":71.770243,"sumSq_":2575.483924,"size_":2,"maxDepth_":0,"value_":35.885122,"nodeType_":"REGRESSION_LEAF","prediction_":0.057302}},"rightChild_":
{"id_":3,"sum_":117.502874,"sumSq_":2463.008913,"size_":6,"maxDepth_":2,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":15.500000,"attr_":"col_1","type_":"REGRESSION_NUMERIC_SPLIT","score_":136.863475,"scoreImprove_":136.863475,"leftNodeSize_":4,"rightNodeSize_":2},"leftCh
{"id_":6,"sum_":91.843937,"sumSq_":2131.620417,"size_":4,"maxDepth_":1,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":0.063785,"attr_":"col_3","type_":"REGRESSION_NUMERIC_SPLIT","score_":17.246563,"scoreImprove_":17.246563,"leftNodeSize_":1,"rightNodeSize_":3},"leftChild_
{"id_":12,"sum_":26.557500,"sumSq_":705.300780,"size_":1,"maxDepth_":0,"value_":26.557500,"nodeType_":"REGRESSION_LEAF","prediction_":0.021225},"rightChild_":
{"id_":13,"sum_":65.286437,"sumSq_":1426.319637,"size_":3,"maxDepth_":0,"value_":21.762146,"nodeType_":"REGRESSION_LEAF","prediction_":0.052073}},"rightChild_":
{"id_":7,"sum_":25.658937,"sumSq_":331.388496,"size_":2,"maxDepth_":1,"nodeType_":"REGRESSION_NODE","split_":
{"splitValue_":19.500000,"attr_":"col_1","type_":"REGRESSION_NUMERIC_SPLIT","score_":2.197959,"scoreImprove_":2.197959,"leftNodeSize_":1,"rightNodeSize_":1},"leftChild_"
{"id_":14,"sum_":11.781146,"sumSq_":138.795410,"size_":1,"maxDepth_":0,"value_":11.781146,"nodeType_":"REGRESSION_LEAF","prediction_":0.009416},"rightChild_":
{"id_":15,"sum_":13.877791,"sumSq_":192.593086,"size_":1,"maxDepth_":0,"value_":13.877791,"nodeType_":"REGRESSION_LEAF","prediction_":0.011091}}}}
0          -1       -1   -1         {"lossType":"MSE","numBoostedTrees":1,"iterNum":3,"avgResponses":0.000000}

Out MetaInformation Table

task_index tree_num iter mse                average residuals
---------- -------- ---- ---                -----------------
0          1        1    233.0514974108345  771.6052377797125
0          1        2    232.7036151951801  769.4112148342409
0          1        3    232.35635211224303 767.2245697367563