TD_ScaleFit Examples | ScaleFit | Teradata Vantage - Dense Input - Analytics Database

Example: InputTable: scale_input_table

CREATE TABLE input_table AS 
(SELECT * FROM titanic_train WHERE passenger IN (97,488,505,631,873) WITH data;

TD_ScaleFit SQL Call

SELECT * FROM TD_ScaleFit (
  ON input_table AS InputTable
  OUT PERMANENT TABLE OutputTable (scaleFitOut)
  USING
  TargetColumns ('fare')
  MissValue ('keep')
  ScaleMethod ('range')
  GlobalScale ('f')
) AS dt2;

TD_ScaleFit Output

Example: InputTable: scale_input_partitioned

DROP TABLE scale_input_partitioned;
CREATE multiset TABLE scale_input_partitioned (
pid INTEGER,
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 scale_input_partitioned VALUES (1,  2, 1,  1,  'Cumings; Mrs. John Bradley (Florence Briggs Thayer)', 'female',   38,  1,  0,  'PC 17599',  7.12833000000000E 001,  'C85', 'C');
INSERT INTO scale_input_partitioned VALUES (1,  4, 1,  1,  'Futrelle; Mrs. Jacques Heath (Lily May Peel)', 'female',   35,  1,  0,  '113803',  5.31000000000000E 001,  'C123', 'S');
INSERT INTO scale_input_partitioned VALUES (1,  7, 0,  1,  'McCarthy; Mr. Timothy J', 'male',   54,  0,  0,  '17463',  5.18625000000000E 001,  'E46', 'S');
INSERT INTO scale_input_partitioned VALUES (1, 11, 1,  3,  'Sandstrom; Miss. Marguerite Rut', 'female',    4,  1,  1,  'PP 9549',  1.67000000000000E 001,  'G6', 'S');
INSERT INTO scale_input_partitioned VALUES (1, 12, 1,  1,  'Bonnell; Miss. Elizabeth', 'female',   58,  0,  0,  '113783',  2.65500000000000E 001,  'C103', 'S');
INSERT INTO scale_input_partitioned VALUES (2, 22, 1,  2,  'Beesley; Mr. Lawrence', 'male',   34,  0,  0,  '248698',  1.30000000000000E 001,  'D56', 'S');
INSERT INTO scale_input_partitioned VALUES (2, 24, 1,  1,  'Sloper; Mr. William Thompson', 'male',   28,  0,  0,  '113788',  3.55000000000000E 001,  'A6', 'S');
INSERT INTO scale_input_partitioned VALUES (2, 32, 1,  1,  'Spencer; Mrs. William Augustus (Marie Eugenie)', 'female',    NULL,  1,  0,  'PC 17569',  1.46520800000000E 002,  'B78', 'C');
INSERT INTO scale_input_partitioned VALUES (2, 53, 1,  1,  'Harper; Mrs. Henry Sleeper (Myna Haxtun)', 'female',   49,  1,  0,  'PC 17572',  7.67292000000000E 001,  'D33', 'C');
INSERT INTO scale_input_partitioned VALUES (2, 55, 0,  1,  'Ostby; Mr. Engelhart Cornelius', 'male',   65,  0,  1,  '113509',  6.19792000000000E 001,  'B30', 'C');
INSERT INTO scale_input_partitioned VALUES (3, 56, 1,  1,  'Woolner; Mr. Hugh', 'male',    NULL,  0,  0,  '19947',  3.55000000000000E 001,  'C52', 'S');
INSERT INTO scale_input_partitioned VALUES (3, 63, 0,  1,  'Harris; Mr. Henry Birkhardt', 'male',   45,  1,  0,  '36973',  8.34750000000000E 001,  'C83', 'S');
INSERT INTO scale_input_partitioned VALUES (3, 67, 1,  2,  'Nye; Mrs. (Elizabeth Ramell)', 'female',   29,  0,  0,  'C.A. 29395',  1.05000000000000E 001,  'F33', 'S');
INSERT INTO scale_input_partitioned VALUES (3, 76, 0,  3,  'Moen; Mr. Sigurd Hansen', 'male',   25,  0,  0,  '348123',  7.65000000000000E 000,  'F G73', 'S');
INSERT INTO scale_input_partitioned VALUES (3, 93, 0,  1,  'Chaffee; Mr. Herbert Fuller', 'male',   46,  1,  0,  'W.E.P. 5734',  6.11750000000000E 001,  'E31', 'S');

DROP TABLE scale_parameters;
CREATE MULTISET TABLE scale_parameters (
pid INTEGER,
parameter_column VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,
value_column VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( pid );
INSERT INTO scale_parameters values(1, 'scalemethod', 'std');
INSERT INTO scale_parameters values(2, 'scalemethod', 'range');

DROP TABLE scale_attributes;
CREATE MULTISET TABLE scale_attributes (
pid INTEGER,
attribute_column VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( pid );
INSERT INTO scale_attributes values(1,  'fare');
INSERT INTO scale_attributes values(2,  'age');

The following example uses this as input table.

ParameterTable:

AttributeTable:

TD_ScaleFit SQL Call

SELECT * FROM TD_scaleFit(
ON scale_input_partitioned AS InputTable PARTITION BY pid
ON scale_parameters AS ParameterTable PARTITION BY pid
ON scale_attributes AS AttributeTable PARTITION BY pid
OUT PERMANENT TABLE OutputTable(scaleFitOut_partitioned)
USING
TargetColumns('fare', 'age')

MissValue('zero')
ScaleMethod('maxabs')
globalScale('f')
)AS dt2;

TD_ScaleFit Output