1.0 - 8.00 - Single_Tree_Predict Example 1 - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

Input

Input tables are from DecisionTree Example 1:
  • attribute_table: iris_attribute_test
  • model_table: iris_attribute_output

SQL Call

CREATE MULTISET TABLE singletree_predict AS (
  SELECT * FROM Single_Tree_Predict@coprocessor (
    ON iris_attribute_test AS attribute_table PARTITION BY pid
      ORDER BY attribute
    ON iris_attribute_output as model_table DIMENSION
    USING
    AttrTable_GroupbyColumns ('attribute')
    AttrTable_pidColumns ('pid')
    AttrTable_valColumn ('attrvalue')
  ) AS dt
) WITH DATA;

Output

This query returns the following table:

SELECT * FROM singletree_predict ORDER BY 1;
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)/(SELECT COUNT(pid)
  FROM st_predict_accuracy
) WITH DATA AS prediction_accuracy;
prediction_accuracy
0.90000000000000000000