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 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