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

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

Example: InputTable: scale_input_table

CREATE TABLE input_table AS 
(SELECT * FROM titanic_train WHERE passenger IN (97,488,505,631,873) WITH data;
passenger survived pclass name sex age sibsp parch ticket fare cabin embarked
97 0 1 Goldschmidt, Mr. Geroge B Male 71 0 0 PC 17754 34.6542 A5 C
488 0 1 Kent, Mr. Edward Austin Male 58 0 0 11771 29.7 B37 C
505 1 1 Maioni, Miss, Roberta Female 16 0 0 110152 86.5 B79 S
631 1 1 Barkworth, Mr. Algernon Henry Wilson Male 80 0 0 27042 30 A23 S
873 0 1 Carlsson, Mr. Frans Olof Male 33 0 0 695 5 B51

B53

B55

S

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

TD_STATTYPE_SCLFIT Fare
min 5
max 86.5
sum 185.8542
count 5
null 0
avg 37.17084
multiplier 1
intercept 0
location 5
scale 81.5
globalscale_false NULL
MethodNumberMapping: [0:mean,1:sum,2:ustd,3:std,4:range,5:midrange,6:maxabs,7:rescale] 4
missvalue_KEEP NULL

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.

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

ParameterTable:

pid parameter_column value_column
1 scalemethod midrange
2 scalemethod range

AttributeTable:

pid attribute_column
1 fare
2 age

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

pid TD_Stattype_SCLFIT Fare Age
1 avg 4.38991600000000E 001 NULL
1 count 5.00000000000000E 000 NULL
1 globalscale_false NULL NULL
1 intercept 0.00000000000000E 000 NULL
1 location 4.39916500000000E 001 NULL
1 max 7.12833000000000E 001 NULL
1 min 1.67000000000000E 001 NULL
1 missvalue_KEEP NULL NULL
1 multiplier 1.00000000000000E 000 NULL
1 null 0.00000000000000E 000 NULL
1 scale 2.72916500000000E 001 NULL
1 ScaleMethodNumberMapping: [0:mean,1:sum,2:ustd,3:std,4:range,5:midrange,6:maxabs,7:rescale] 5.00000000000000E 000 NULL
1 sum 2.19495800000000E 002 NULL
1 unusedattributes_unscaled NULL NULL
2 avg NULL 4.40000000000000E 001
2 count NULL 4.00000000000000E 000
2 globalscale_false NULL NULL
2 intercept NULL NULL
2 location NULL 2.80000000000000E 001
2 max NULL 6.50000000000000E 002
2 min NULL 2.80000000000000E 001
2 missvalue_KEEP NULL NULL
2 multiplier NULL 1.00000000000000E 000
2 null NULL 1.00000000000000E 000
2 scale NULL 3.70000000000000E 002
2 ScaleMethodNumberMapping: [0:mean,1:sum,2:ustd,3:std,4:range,5:midrange,6:maxabs,7:rescale] NULL 4.00000000000000E 000
2 sum NULL 1.76000000000000E 002
2 unusedattributes_unscaled NULL NULL
3 avg 3.96600000000000E 001 3.62500000000000E 001
3 count 5.00000000000000E 000 4.00000000000000E 000
3 globalscale_false NULL NULL
3 intercept 0.00000000000000E 000 0.00000000000000E 000
3 location 0.00000000000000E 000 0.00000000000000E 000
3 max 8.34750000000000E 001 4.60000000000000E 001
3 min 7.65000000000000E 000 2.50000000000000E 001
3 missvalue_ZERO NULL NULL
3 multiplier 1.00000000000000E 000 1.00000000000000E 000
3 null 0.00000000000000E 000 1.00000000000000E 000
3 scale 8.34750000000000E 001 4.60000000000000E 001
3 ScaleMethodNumberMapping: [0:mean,1:sum,2:ustd,3:std,4:range,5:midrange,6:maxabs,7:rescale] 6.00000000000000E 000 6.00000000000000E 000
3 sum 1.98300000000000E 002 1.45000000000000E 002
3 unusedattributes_unscaled NULL NULL