Sparse Input with Partition - 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ā„¢

The following example assumes the sparse input format for InputTable, ParameterTable, and AttributeTable:

CREATE MULTISET TABLE scale_input_part_sparse (pid int, passenger int, attribute_column varchar(20), attribute_value real);
insert into scale_input_part_sparse values (1, 2,   'age',  38);
insert into scale_input_part_sparse values (1, 2,   'fare', 71.2833);
insert into scale_input_part_sparse values (1, 4,   'age',  35);
insert into scale_input_part_sparse values (1, 4,   'fare', 53.1);
insert into scale_input_part_sparse values (1, 7,   'age',  54);
insert into scale_input_part_sparse values (1, 7,   'fare', 51.8625);
insert into scale_input_part_sparse values (1, 11,  'age',  4);
insert into scale_input_part_sparse values (1, 11,  'fare', 16.7);
insert into scale_input_part_sparse values (1, 12,  'age',  58);
insert into scale_input_part_sparse values (1, 12,  'fare', 26.55);
insert into scale_input_part_sparse values (2, 22,  'age',  34);
insert into scale_input_part_sparse values (2, 22,  'fare', 13);
insert into scale_input_part_sparse values (2, 24,  'age',  28);
insert into scale_input_part_sparse values (2, 24,  'fare', 35.5);
insert into scale_input_part_sparse values (2, 32,  'age',  NULL);
insert into scale_input_part_sparse values (2, 32,  'fare', 146.5208);
insert into scale_input_part_sparse values (2, 53,  'age',  49);
insert into scale_input_part_sparse values (2, 53,  'fare', 76.7292);
insert into scale_input_part_sparse values (2, 55,  'age',  65);
insert into scale_input_part_sparse values (2, 55,  'fare', 61.9792);
insert into scale_input_part_sparse values (3, 56,  'age',  NULL);
insert into scale_input_part_sparse values (3, 56,  'fare', 35.5);
insert into scale_input_part_sparse values (3, 63,  'age',  45);
insert into scale_input_part_sparse values (3, 63,  'fare', 83.475);
insert into scale_input_part_sparse values (3, 67,  'age',  29);
insert into scale_input_part_sparse values (3, 67,  'fare', 10.5);
insert into scale_input_part_sparse values (3, 76,  'age',  25);
insert into scale_input_part_sparse values (3, 76,  'fare', 7.65);
insert into scale_input_part_sparse values (3, 93,  'age',  46);
insert into scale_input_part_sparse values (3, 93,  'fare', 61.175);

CREATE MULTISET TABLE sparse_scale_parameters (
pid INTEGER,
parameter_column VARCHAR(150) CHARACTER SET UNICODE NOT CASESPECIFIC,
value_column VARCHAR(150) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( pid );
insert into sparse_scale_parameters values(1, 'scalemethod', 'midrange');
insert into sparse_scale_parameters values(2, 'scalemethod', 'range');

CREATE MULTISET TABLE sparse_scale_attributes (
pid INTEGER,
attribute_column VARCHAR(150) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( pid );
insert into sparse_scale_attributes values(1,  'fare');
insert into sparse_scale_attributes values(2,  'age');

TD_ScaleFit Call

SELECT * FROM TD_ScaleFit(
ON scale_input_part_sparse AS InputTable PARTITION BY pid
ON sparse_scale_parameters AS ParameterTable PARTITION BY pid
ON sparse_scale_attributes AS AttributeTable PARTITION BY pid
OUT PERMANENT TABLE OutputTable(sparseScaleFitOutPartitioned)
USING
AttributeNameColumn('attribute_column')
AttributeValueColumn('attribute_value')
MissValue('zero')
ScaleMethod('maxabs')
globalScale('f')
)AS dt2 order by 1,3,2;

TD_ScaleFit Output

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