TD_TargetEncodingFit Examples | TargetEncodingFit - TD_TargetEncodingFit Examples - Teradata Vantage

TD_TargetEncodingFit Example

The following input table contains a subset of titantic dataset.

CREATE TABLE titanic_train (
   passenger INTEGER,
   survived INTEGER,
   pclass INTEGER,
   name VARCHAR(90) CHARACTER SET LATIN NOT CASESPECIFIC,
   gender VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
   age INTEGER,
   sibsp INTEGER,
   parch INTEGER,
   ticket VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
   fare FLOAT,
   cabin VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
   embarked VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( passenger );
 
INSERT INTO titanic_train (2, 1, 1, 'Cumings; Mrs. John Bradley (Florence Briggs Thayer)', 'female', 38, 1, 0, 'PC 17599', 71.2833, 'C85', 'C');
INSERT INTO titanic_train (4, 1, 1, 'Futrelle; Mrs. Jacques Heath (Lily May Peel)', 'female', 35, 1, 0, '113803', 53.1, 'C123', 'S');
INSERT INTO titanic_train (7, 0, 1, 'McCarthy; Mr. Timothy J', 'male', 54, 0, 0, '17463', 51.8625, 'E46', 'S');
INSERT INTO titanic_train (10, 1, 2, 'Nasser; Mrs. Nicholas (Adele Achem)', 'female', 14, 1, 0, '237736', 30.0708, '', 'C');
INSERT INTO titanic_train (16, 1, 2, 'Hewlett; Mrs. (Mary D Kingcome) ', 'female', 55, 0, 0, '248706', 16, '', 'S');
INSERT INTO titanic_train (21, 0, 2, 'Fynney; Mr. Joseph J', 'male', 35, 0, 0, '239865', 26, '', 'S');
INSERT INTO titanic_train (40, 1, 3, 'Nicola-Yarred; Miss. Jamila', 'female', 14, 1, 0, '2651', 11.2417, '', 'C');
INSERT INTO titanic_train (61, 0, 3, 'Sirayanian; Mr. Orsen', 'male', 22, 0, 0, '2669', 7.2292, '', 'C');
INSERT INTO titanic_train (80, 1, 3, 'Dowdell; Miss. Elizabeth', 'female', 30, 0, 0, '364516', 12.475, '', 'S');

SELECT * FROM titanic_train ORDER BY 1;

CategoryTable:

CREATE TABLE categoryTable AS (
SELECT ColumnName, count(*) AS CategoryCount FROM (
SELECT * FROM TD_CategoricalSummary(
ON titanic_train AS InputTable
USING
TargetColumns('gender','embarked')
) AS dt WHERE DistinctValue IS NOT NULL) AS CatTable GROUP BY ColumnName) WITH data;

SELECT * FROM categoryTable;

TD_TargetEncodingFit Query with EncoderMethod CBM_BETA

TD_TargetEncodingFit (
   ON titanic_train AS InputTable
   ON categoryTable AS CategoryTable DIMENSION
   USING
   EncoderMethod('CBM_BETA')
   TargetColumns('gender','embarked')
   ResponseColumn ('survived')
...DefaultValues(-1, -2)
)

Output:

TD_TargetEncodingFit Query with EncoderMethod CBM_DIRICHLET

TD_TargetEncodingFit (
   ON titanic_train AS InputTable
   ON categoryTable AS CategoryTable DIMENSION
   USING
   EncoderMethod('CBM_DIRICHLET')
   TargetColumns('gender','embarked')
   ResponseColumn ('pclass')
   DefaultValues(-1, -2)
   NumDistinctResponses(3)
)

Output:

TD_TargetEncodingFit Query with EncoderMethod CBM_GAUSSIAN_INVERSE_GAMMA

TD_TargetEncodingFit (
   ON titanic_train AS InputTable
   ON categoryTable AS CategoryTable DIMENSION
   USING
   EncoderMethod('CBM_GAUSSIAN_INVERSE_GAMMA)
   TargetColumns('gender','embarked')
   ResponseColumn ('age')
   DefaultValues(-1, -2)
)

Output: