Input
- InputTable: sparse_housing_train_binary, created by inputting housing_train_binary (InputTable for XGBoost Example 1: Binary Classification) to the Unpivoting function:
DROP TABLE sparse_housing_train_binary; CREATE MULTISET TABLE sparse_housing_train_binary AS ( SELECT sn, homestyle, attribute, value_col AS value1 FROM Unpivoting ( ON housing_train_binary USING TargetColumns ( 'price','lotsize','bedrooms','bathrms','stories', 'garagepl','driveway','recroom','fullbase','gashw','airco','prefarea' ) Accumulate ('sn','homestyle') ) AS dt ) WITH DATA;
- AttributeTable: sparse_housing_attributes, created and populated with this code:
DROP TABLE IF EXISTS sparse_housing_attributes; CREATE MULTISET TABLE sparse_housing_attributes ( attributename VARCHAR, attributetype NUMERIC ); INSERT INTO sparse_housing_attributes VALUES ('price','1'), ('lotsize','1'), ('bedrooms','1'), ('bathrms','1'), ('stories','1'), ('garagepl','1'), ('driveway','0'), ('recroom','0'), ('fullbase','0'), ('gashw','0'), ('airco','0'), ('prefarea','0');
sn | homestyle | attribute | value1 |
---|---|---|---|
1 | Classic | airco | no |
1 | Classic | bathrms | 3 |
1 | Classic | bedrooms | 1 |
1 | Classic | driveway | yes |
1 | Classic | fullbase | yes |
1 | Classic | garagepl | 1 |
1 | Classic | gashw | no |
1 | Classic | lotsize | 5850.0 |
1 | Classic | prefarea | no |
1 | Classic | price | 42000.0 |
1 | Classic | recroom | no |
1 | Classic | stories | 2 |
2 | Classic | airco | no |
2 | Classic | bathrms | 1 |
2 | Classic | bedrooms | 2 |
2 | Classic | driveway | yes |
... | ... | ... | ... |
price | lotsize | bedrooms | bathrms | stories | garagepl | driveway | recroom | fullbase | gashw | airco | prefarea |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
SQL Call
DROP TABLE IF EXISTS sparse_housing_xgboost_model ; SELECT * FROM XGBoost ( ON sparse_housing_train_binary AS InputTable ON sparse_housing_attributes AS AttributeTable OUT TABLE OutputTable (sparse_housing_xgboost_model) USING ResponseColumn ('homestyle') PredictionType ('classification') AttributeNameColumn ('attribute') AttributeValueColumn ('value1') LossFunction ('binomial') IterNum (10) MaxDepth (10) MinNodeSize (1) RegularizationLambda (1) ShrinkageFactor (0.1) IDColumn ('sn') NumBoostedTrees (2) ) AS dt;
Output
message |
---|
Parameters: Number of boosting iterations : 10 Number of boosted trees : 2 Number of total trees (all subtrees): 20 Prediction Type : CLASSIFICATION LossFunction : BINOMIAL Regularization : 1.0 Shrinkage : 0.1 MaxDepth : 10 MinNodeSize : 1 Variance : 0.0 Seed : 1 ColumnSubSampling Features: 12 XGBoost model created in table specified in OutputTable argument |
This query returns the following table:
SELECT tree_id, iter, class_num, CAST (tree AS VARCHAR(30)) AS tree, cast(region_prediction AS VARCHAR(30)) AS region_pred FROM sparse_housing_xgboost_model order by 1,2;
For simplicity, the last two output columns show only the first 30 characters of each value.
tree_id | iter | class_num | tree | region_pred |
---|---|---|---|---|
-1 | -1 | -1 | {"classifier":"CLASSIFICATION" | |
0 | 1 | 0 | {"sum_":1.2000001063938726E-6, | {"1792":0.06969074,"1280":-0.1 |
0 | 2 | 0 | {"sum_":-0.14652091000004508," | {"384":0.042531442,"385":0.030 |
0 | 3 | 0 | {"sum_":0.2814020000000276,"su | {"1664":0.052631423,"1665":0.0 |
0 | 4 | 0 | {"sum_":1.2547231599999824,"su | {"1280":-0.06937855,"1281":-0. |
0 | 5 | 0 | {"sum_":1.9154821700000046,"su | {"768":0.027756682,"1538":0.03 |
0 | 6 | 0 | {"sum_":1.98376042000001,"sumS | {"768":0.026932025,"1538":0.03 |
0 | 7 | 0 | {"sum_":2.091817570000011,"sum | {"768":0.026147524,"769":0.035 |
0 | 8 | 0 | {"sum_":2.360585519999989,"sum | {"768":0.023378344,"769":0.025 |
0 | 9 | 0 | {"sum_":2.623490840000003,"sum | {"768":0.02468738,"769":0.0227 |
0 | 10 | 0 | {"sum_":3.1594640700000087,"su | {"770":0.024006711,"771":0.023 |
1 | 1 | 0 | {"sum_":-2.4000000620194584E-6 | {"1664":0.07176345,"1792":0.07 |
1 | 2 | 0 | {"sum_":1.2196362000000476,"su | {"1152":-0.108543135,"1024":-0 |
1 | 3 | 0 | {"sum_":1.693821100000016,"sum | {"512":-0.07265504,"1536":0.06 |
1 | 4 | 0 | {"sum_":1.5348959100000301,"su | {"1536":0.053727582,"1537":0.0 |
1 | 5 | 0 | {"sum_":1.6560536700000048,"su | {"256":-0.04895391,"257":-0.06 |
1 | 6 | 0 | {"sum_":1.8388901200000012,"su | {"512":-0.046615,"513":-0.0458 |
1 | 7 | 0 | {"sum_":2.205078370000002,"sum | {"1540":0.026542466,"1541":0.0 |
1 | 8 | 0 | {"sum_":2.621658569999993,"sum | {"258":-0.040129118,"259":-0.0 |
1 | 9 | 0 | {"sum_":2.9240273799999925,"su | {"16":-0.042044364,"20":-0.040 |
1 | 10 | 0 | {"sum_":3.2108299899999864,"su | {"260":-0.03580759,"522":-0.03 |