DecisionTree Example 1 - Teradata Vantage

The function DecisionTree acts on the train set to create the model. The Single_Tree_Predict function uses that model and a test set to predict the output. The prediction accuracy is determined based on the original and prediction results.

Input

This example uses the iris data set (iris_input). The data has values for four attributes (sepal_length, sepal_width, petal_length and petal_width), which are grouped into three categories (setosa (1), versicolor (2), virginica (3)). From the raw data, the example creates training and test data sets.

This code divides the 150 data rows into a training data set (80%) and a test data set (20%):

DROP TABLE iris_train;
DROP TABLE iris_test;

CREATE MULTISET TABLE iris_train AS (
  SELECT * FROM iris_input WHERE id%5!=0
) WITH DATA;

CREATE MULTISET TABLE iris_test AS (
  SELECT * FROM iris_input WHERE id%5=0
) WITH DATA;

This query returns the following table:

SELECT * FROM iris_train ORDER BY id;

This query returns the following table:

SELECT * FROM iris_test ORDER BY id;

The attribute tables, iris_attribute_train and iris_attribute_test, are created from the raw train and test data, iris_train and iris_test. The recommended way to create the attribute tables is to use the Unpivoting function:

CREATE MULTISET TABLE singletree1 AS ( 
  SELECT * FROM Unpivoting (
    ON iris_train
    USING
    TargetColumns ('sepal_length', 'sepal_width', 'petal_length', 'petal_width')
    AttributeColumn ('attribute')
    ValueColumn ('value_col')
    InputTypes ('false')
    Accumulate ('id')
  ) AS dt
) WITH DATA;

CREATE MULTISET TABLE iris_attribute_train AS (
  SELECT id AS pid, attribute, value_col AS attrvalue FROM singletree1
) WITH DATA;

CREATE MULTISET TABLE singletree2 AS (
  SELECT * FROM Unpivoting (
    ON iris_test
    USING
    TargetColumns ('sepal_length', 'sepal_width', 'petal_length', 'petal_width')
    AttributeColumn ('attribute')
    ValueColumn ('value_col')
    InputTypes ('false')
    Accumulate ('id')
  ) AS dt
) WITH DATA;

CREATE MULTISET TABLE iris_attribute_test AS (
  SELECT id AS pid, attribute, value_col AS attrvalue FROM singletree2
) WITH DATA;

This query returns the following table:

SELECT * FROM iris_attribute_train ORDER BY pid, attribute;

This query returns the following table:

SELECT * FROM iris_attribute_test ORDER BY pid, attribute;

Response tables, created from the raw train and test data, are used as inputs.

DROP TABLE iris_response_train;
CREATE MULTISET TABLE iris_response_train (pid INTEGER, response VARCHAR);
INSERT INTO iris_response_train SELECT id, species FROM iris_train;

This query returns the following table:

SELECT * FROM iris_response_train ORDER BY pid;

DROP TABLE iris_response_test;
CREATE MULTISET TABLE iris_response_test (pid INTEGER, response VARCHAR);
INSERT INTO iris_response_test SELECT id, species FROM iris_test;

This query returns the following table:

SELECT * FROM iris_response_test ORDER BY pid;

SQL Call

DROP TABLE iris_attribute_output;
DROP TABLE splits_small;

SELECT * FROM DecisionTree (
  ON iris_attribute_train AS AttributeTableName
  ON iris_response_train AS ResponseTableName
  OUT TABLE OutputTable (iris_attribute_output)
  OUT TABLE IntermediateSplitsTable (splits_small)
  USING
  NumSplits (3)
  SplitMeasure ('gini')
  MaxDepth (10)
  IDColumns ('pid')
  AttributeNameColumns ('attribute')
  AttributeValueColumn ('attrvalue')
  ResponseColumn ('response')
  MinNodeSize (10)
  ApproxSplits ('false')
) AS dt;

Output

Output model table is successfully stored in the table specified in OutputTable argument.
Depth of the tree is:6

This query returns the following table:

SELECT * FROM iris_attribute_output ORDER BY 1;