This example uses test data and the model output by the AdaBoost Example: Create Model to Predict Home Style to use real estate sales data to predict home style.
Input
- AttributeTable: housing_test_att, created by inputting housing_train (the raw data for DecisionForest Example: TreeType ('classification'), OutOfBag ('false')) to the Unpivoting function:
CREATE MULTISET TABLE housing_prediction AS ( SELECT * FROM AdaBoostPredict ( ON housing_test_att AS AttributeTable partition BY sn ON abd_model AS Model DIMENSION ORDER BY classifier_id, node_id USING AttrTableGroupByColumns ('attribute') AttrTablePidColumns ('sn') AttrTableValColumn ('value_col') ) AS dt ) WITH DATA;
- Model: abd_model, output by AdaBoost Example: Create Model to Predict Home Style
This query returns the following table:
SELECT * FROM housing_test_att ORDER BY 1, 2;
sn | attribute | value_col |
---|---|---|
13 | airco | no |
13 | bathrms | 1 |
13 | bedrooms | 3 |
13 | driveway | yes |
13 | fullbase | no |
13 | garagepl | 0 |
13 | gashw | no |
13 | lotsize | 1700.0 |
13 | prefarea | no |
13 | price | 27000.0 |
13 | recroom | no |
13 | stories | 2 |
16 | airco | yes |
16 | bathrms | 1 |
16 | bedrooms | 2 |
16 | driveway | yes |
16 | fullbase | no |
16 | garagepl | 0 |
16 | gashw | no |
16 | lotsize | 3185.0 |
16 | prefarea | no |
16 | price | 37900.0 |
16 | recroom | no |
16 | stories | 1 |
... | ... | ... |
SQL Call
SELECT * FROM housing_prediction;
Output
The pred_label column contains the predicted response.CREATE MULTISET TABLE housing_prediction AS ( SELECT * FROM AdaBoostPredict ( ON housing_test_att AS AttributeTable PARTITION BY sn ON abd_model AS Model DIMENSION ORDER BY classifier_id, node_id USING AttrTableGroupbyColumns ('attribute') AttrTablePidColumns ('sn') AttrTableValColumn ('value') ) AS dt ) WITH DATA;
sn pred_label --- ---------- 469 eclectic 530 bungalow 162 bungalow 177 eclectic 38 eclectic 301 eclectic 463 eclectic 53 eclectic 364 eclectic 13 classic 255 eclectic 440 eclectic 198 classic 339 bungalow 260 classic 377 bungalow 234 classic 459 classic 340 eclectic 472 eclectic 274 eclectic 251 eclectic 237 classic 224 eclectic 329 bungalow 249 classic 540 eclectic 140 classic 306 eclectic 411 eclectic 132 classic 527 bungalow 367 bungalow 104 bungalow 355 eclectic 117 eclectic 403 eclectic 142 classic 443 eclectic 401 eclectic 161 eclectic 239 classic 111 classic 16 classic 157 eclectic 25 classic 176 eclectic 195 classic 441 eclectic 317 eclectic 353 eclectic 254 eclectic 408 eclectic 294 classic
Prediction Accuracy
This query returns the prediction accuracy:
SELECT (SELECT COUNT(*) FROM housing_prediction, housing_test WHERE housing_prediction.sn = housing_test.sn AND housing_prediction.pred_label = housing_test.homestyle) / (1.00 *(SELECT COUNT(sn) FROM housing_prediction)) AS pa;
pa ---- 0.93
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.