In this example, Responses is 'false' by default; therefore, OutputProb is optional (and is omitted).
Input
This example uses these input tables:
- Input table: housing_test, which has 54 observations of 14 variables
- Model: rft_model, output by DecisionForest Example: TreeType ('classification'), OutOfBag ('false')
sn | price | lotsize | bedrooms | bathrms | stories | driveway | recroom | fullbase | gashw | airco | garagepl | prefarea | homestyle |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
13 | 27000 | 1700 | 3 | 1 | 2 | yes | no | no | no | no | 0 | no | Classic |
16 | 37900 | 3185 | 2 | 1 | 1 | yes | no | no | no | yes | 0 | no | Classic |
25 | 42000 | 4960 | 2 | 1 | 1 | yes | no | no | no | no | 0 | no | Classic |
38 | 67000 | 5170 | 3 | 1 | 4 | yes | no | no | no | yes | 0 | no | Eclectic |
53 | 68000 | 9166 | 2 | 1 | 1 | yes | no | yes | no | yes | 2 | no | Eclectic |
104 | 132000 | 3500 | 4 | 2 | 2 | yes | no | no | yes | no | 2 | no | bungalow |
111 | 43000 | 5076 | 3 | 1 | 1 | no | no | no | no | no | 0 | no | Classic |
117 | 93000 | 3760 | 3 | 1 | 2 | yes | no | no | yes | no | 2 | no | Eclectic |
132 | 44500 | 3850 | 3 | 1 | 2 | yes | no | no | no | no | 0 | no | Classic |
140 | 43000 | 3750 | 3 | 1 | 2 | yes | no | no | no | no | 0 | no | Classic |
142 | 40000 | 2650 | 3 | 1 | 2 | yes | no | yes | no | no | 1 | no | Classic |
157 | 60000 | 2953 | 3 | 1 | 2 | yes | no | yes | no | yes | 0 | no | Eclectic |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
SQL Call
Use the Accumulate syntax element to pass the homestyle variable, to easily compare the actual and predicted response for each observation.
CREATE MULTISET TABLE rf_housing_predict AS ( SELECT * FROM DecisionForestPredict_MLE ( ON housing_test PARTITION BY ANY ON rft_model AS Model DIMENSION USING NumericInputs ('price ', 'lotsize ', 'bedrooms ', 'bathrms ', 'stories ', 'garagepl') CategoricalInputs ('driveway ', 'recroom ', 'fullbase ', 'gashw ', 'airco ', 'prefarea') IDColumn ('sn') Accumulate ('homestyle') Detailed ('false') ) AS dt ) WITH DATA;
Output
The predicted response is in the prediction column and the original classification values are in the homestyle column. The upper and lower confidence intervals are also shown in the output table.
This query returns the following table:
SELECT * FROM rf_housing_predict ORDER BY sn;
homestyle sn prediction confidence_lower confidence_upper --------- --- ---------- ---------------- ---------------- classic 13 classic 0.98 0.98 classic 16 classic 0.88 0.88 classic 25 classic 0.96 0.96 eclectic 38 eclectic 0.96 0.96 eclectic 53 eclectic 0.94 0.94 bungalow 104 bungalow 0.52 0.52 classic 111 classic 0.94 0.94 eclectic 117 eclectic 0.92 0.92 classic 132 classic 0.94 0.94 classic 140 classic 0.94 0.94 classic 142 classic 0.92 0.92 eclectic 157 eclectic 1.0 1.0 eclectic 161 eclectic 0.98 0.98 bungalow 162 bungalow 0.76 0.76 eclectic 176 eclectic 0.94 0.94 eclectic 177 eclectic 0.98 0.98 classic 195 classic 0.98 0.98 classic 198 classic 0.88 0.88 eclectic 224 eclectic 0.92 0.92 classic 234 classic 0.98 0.98 classic 237 classic 0.8 0.8 classic 239 classic 0.9 0.9 classic 249 classic 0.98 0.98 classic 251 classic 0.88 0.88 eclectic 254 eclectic 0.9 0.9 eclectic 255 eclectic 0.96 0.96 classic 260 classic 0.96 0.96 eclectic 274 eclectic 0.96 0.96 classic 294 classic 0.96 0.96 eclectic 301 eclectic 0.96 0.96 eclectic 306 eclectic 0.92 0.92 eclectic 317 eclectic 0.96 0.96 bungalow 329 bungalow 0.92 0.92 bungalow 339 bungalow 0.74 0.74 eclectic 340 eclectic 0.98 0.98 eclectic 353 eclectic 0.98 0.98 eclectic 355 eclectic 1.0 1.0 eclectic 364 eclectic 0.92 0.92 bungalow 367 bungalow 0.88 0.88 bungalow 377 bungalow 0.76 0.76 eclectic 401 eclectic 0.9 0.9 eclectic 403 eclectic 0.96 0.96 eclectic 408 eclectic 0.96 0.96 eclectic 411 eclectic 0.92 0.92 eclectic 440 eclectic 0.98 0.98 eclectic 441 eclectic 0.96 0.96 eclectic 443 eclectic 0.98 0.98 classic 459 classic 0.86 0.86 classic 463 classic 0.9 0.9 eclectic 469 eclectic 0.88 0.88 eclectic 472 eclectic 0.96 0.96 bungalow 527 bungalow 0.58 0.58 bungalow 530 bungalow 0.56 0.56 eclectic 540 eclectic 0.86 0.86
Prediction Accuracy
This query returns the prediction accuracy:
SELECT (SELECT count(sn) FROM rf_housing_predict WHERE homestyle = prediction) / ( 1.00 * (SELECT count(sn) FROM rf_housing_predict) ) AS pa;
pa ---- 1.00
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.