1.1 - 8.10 - DecisionForestPredict_MLE Example: Omit Responses - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)
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
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.