TD_TargetEncodingTransform Examples - Analytics Database

TD_TargetEncodingTransform Example

InputTable: The following 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;

Input table:

TD_TargetEncodingTransform Query with EncoderMethod CBM_BETA

SELECT * FROM TD_TargetEncodingFit (
   ON titanic_train AS InputTable
   ON categoryTable AS CategoryTable DIMENSION
   OUT PERMANENT TABLE OutputTable(betaEncFitTbl)    
   USING    
   EncoderMethod('CBM_BETA')
   TargetColumns('gender','embarked')
   ResponseColumn ('survived')
   DefaultValues(-1, -2)
) AS dt;

CBM_BETA FitTable:

Transform query:

SELECT * FROM TD_TargetEncodingTransform (
   ON titanic_train AS InputTable
   ON betaEncFitTbl AS FitTable DIMENSION
   USING
   Accumulate('passenger')
) AS dt ORDER BY passenger;

Transform output:

TD_TargetEncodingFit Query with EncoderMethod CBM_DIRICHLET

SELECT * FROM TD_TargetEncodingFit (
   ON titanic_train AS InputTable
   ON categoryTable AS CategoryTable DIMENSION
   OUT PERMANENT TABLE OutputTable(dirichletEncFitTbl)    
   USING    
   EncoderMethod('CBM_DIRICHLET')
   TargetColumns('gender','embarked')
   ResponseColumn ('pclass')
   DefaultValues(-1, -2)
   NumDistinctResponses(3)
) AS dt;

CBM_DIRICHLET FitTable:

Transform query:

SELECT * FROM TD_TargetEncodingTransform (
   ON titanic_train AS InputTable
   ON dirichletEncFitTbl AS FitTable DIMENSION
   USING
   Accumulate('passenger')
) AS dt ORDER BY passenger;

Transform output:

TD_TargetEncodingTransform Query with EncoderMethod CBM_GAUSSIAN_INVERSE_GAMMA

SELECT * FROM TD_TargetEncodingFit (
   ON titanic_train AS InputTable
   ON categoryTable AS CategoryTable DIMENSION
   OUT PERMANENT TABLE OutputTable(GIGEncFitTbl)    
   USING    
   EncoderMethod('CBM_GAUSSIAN_INVERSE_GAMMA')
   TargetColumns('gender','embarked')
   ResponseColumn ('age')
   DefaultValues(-1, -2)
) AS dt;

Output:

Transform query:

SELECT * FROM TD_TargetEncodingTransform (
   ON titanic_train AS InputTable
   ON GIGEncFitTbl AS FitTable DIMENSION
   USING
   Accumulate('passenger')
) AS dt ORDER BY passenger;

Transform output: