DecisionTreePredict_MLE Example: Apply Model to Test Data | Teradata Vantage - DecisionTreePredict_MLE Example: Apply Model to Test Data - Teradata Vantage

Input

SQL Call

CREATE MULTISET TABLE singletree_predict AS (
  SELECT * FROM DecisionTreePredict_MLE (
    ON iris_attribute_test AS AttributeTable PARTITION BY pid
    ON iris_attribute_output as Model DIMENSION
    USING
    AttrTableGroupbyColumns ('attribute')
    AttrTablePidColumns ('pid')
    AttrTableValColumn ('attrvalue')
  ) AS dt
) WITH DATA;

Output

SELECT * FROM singletree_predict ORDER BY pid;

 pid pred_label 
 --- ---------- 
   5 1         
  10 1         
  15 1         
  20 1         
  25 1         
  30 1         
  35 1         
  40 1         
  45 1         
  50 1         
  55 2         
  60 2         
  65 2         
  70 2         
  75 2         
  80 2         
  85 2         
  90 2         
  95 2         
 100 2         
 105 3         
 110 3         
 115 3         
 120 2         
 125 3         
 130 2         
 135 2         
 140 3         
 145 3         
 150 3

Prediction Accuracy

The following SQL code calculates and displays the prediction accuracy.

DROP TABLE st_predict_accuracy;

CREATE MULTISET TABLE st_predict_accuracy AS (
  SELECT pid, CAST(pred_label AS INTEGER) AS pred_label, species
  FROM singletree_predict, iris_test WHERE id = pid
  ) WITH DATA;

SELECT (
  SELECT COUNT(pid) FROM st_predict_accuracy
  WHERE pred_label = species)/( 1.00 * (SELECT COUNT(pid)
  FROM st_predict_accuracy ) 
  ) AS prediction_accuracy;

 prediction_accuracy 
 ------------------- 
                0.90

Download a zip file of all examples and a SQL script file that creates their input tables.