The following example assumes sparse input format for InputTable:
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);
FitTable Generated with TD_ScaleFit Function
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
TD_ScaleTransform SQL Call
SELECT * FROM TD_ScaleTransform(
ON scale_input_part_sparse AS InputTable PARTITION BY pid
ON sparseScaleFitOutPartitioned AS FitTable PARTITION BY pid
USING
AttributeNameColumn('attribute_column')
AttributeValueColumn('attribute_value')
Accumulate('pid','passenger')
)AS dt2 order by 1,2,3;
TD_ScaleTransform Output
pid passenger attribute_column attribute_value --- --------- ---------------- --------------- 1 2 fare 1.00000000000000E 000 1 4 fare 3.33741272513754E-01 1 7 fare 2.88397733372662E-01 1 11 fare -1.00000000000000E 000 1 12 fare -6.39083749058778E-01 2 22 age 1.62162162162162E-01 2 24 age 0.00000000000000E 000 2 32 age -7.56756756756757E-01 2 53 age 5.67567567567568E-01 2 55 age 1.00000000000000E 000 3 56 age 0.00000000000000E 000 3 56 fare 4.25277029050614E-01 3 63 age 9.78260869565217E-01 3 63 fare 1.00000000000000E 000 3 67 age 6.30434782608696E-01 3 67 fare 1.25786163522013E-01 3 76 age 5.43478260869565E-01 3 76 fare 9.16442048517520E-02 3 93 age 1.00000000000000E 000 3 93 fare 7.32854147948488E-01