Example: How to Use TD_NaiveBayesPredict - Teradata VantageCloud Lake

Every complete example in this document is available in a zip file that you can download. The zip file includes a SQL script file that creates the input tables for the examples. If you are reading this document on https://docs.teradata.com/, you can download the zip file (SQLE-Analytic-Functions-Examples_xxxx) from the attachment in the left pane.

This section shows the input table, SQL query, and output tables of an example using TD_NaiveBayesPredict.

DROP TABLE housing_test;
CREATE TABLE housing_test (sn integer, price real, lotsize real, bedrooms  integer, bathrms integer, stories integer, driveway varchar(10), recroom varchar(10), fullbase varchar(10), gashw varchar(10), airco varchar(10), garagepl integer, prefarea varchar(10), homestyle varchar(10)) PRIMARY INDEX(sn);
insert into housing_test values(13,27000,1700,3,1,2,'yes','no','no','no','no',0,'no','Classic');
insert into housing_test values(16,37900,3185,2,1,1,'yes','no','no','no','yes',0,'no','Classic');
insert into housing_test values(25,42000,4960,2,1,1,'yes','no','no','no','no',0,'no','Classic');
insert into housing_test values(38,67000,5170,3,1,4,'yes','no','no','no','yes',0,'no','Eclectic');
insert into housing_test values(53,68000,9166,2,1,1,'yes','no','yes','no','yes',2,'no','Eclectic');
...

SELECT * FROM housing_test ORDER BY 1;

TD_NaiveBayesPredict SQL Call

SELECT * FROM TD_NaiveBayesPredict (
  ON housing_test AS InputTable
  ON housing_train_model AS ModelTable DIMENSION
  USING
  IDColumn ('sn')
  NumericInputs('price','lotsize','bedrooms','bathrms','stories','garagepl')
  CategoricalInputs('driveway','recroom','fullbase','gashw','airco','prefarea')
  Responses ('Classic', 'Eclectic', 'bungalow')
  OutputProb('t')
  Accumulate('homestyle')
) AS dt ORDER BY sn;

As the data is in dense format, you need to use TD_Unpivoting function to change the data to sparse format.

DROP TABLE housing_test_sparse;
CREATE MULTISET TABLE housing_test_sparse AS( 
SELECT * FROM TD_UNPIVOTING(
ON housing_test AS InputTable
USING
IDColumn('sn')
TargetColumns('price','lotsize','bedrooms','bathrms','stories','garagepl','driveway','recroom','fullbase','gashw','airco','prefarea')
AttributeColName('AttributeName') 
ValueColName('AttributeValue')
Accumulate('homestyle')
) AS dt) WITH data;

TD_NaiveBayesPredict SQL Call

SELECT * FROM TD_NaiveBayesPredict (
  ON housing_test_sparse AS InputTable
  ON housing_train_model_sparse AS ModelTable DIMENSION
  USING
  IDColumn ('sn')
  AttributeNameColumn('AttributeName') 
  AttributeValueColumn('AttributeValue')
  Responses ('Classic', 'Eclectic', 'bungalow')
  OutputProb('t')
  Accumulate('homestyle')
) AS dt ORDER BY sn;