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