1.1 - 8.10 - DWT2D Example - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

This example uses climate data in cities in California (CA), Texas (TX), and Washington (WA). The cities are represented by two-dimensional coordinates (latitude and longitude). The data are temperature (in degrees Fahrenheit), pressure (in Mbars), and dew point (in degrees Fahrenheit). The function outputs a coefficient model table and a meta table, which are used as input to the function IDWT2D (ML Engine).

Input

InputTable: twod_climate_data
state city longitude latitude temp_f pressure_mbar dewpoint_f
CA ALPINE -117 32 34.9 1020.5 28.9
CA ALTURAS -121 41 36.6 1022 29.1
CA ANAHEIM -118 33 33.9 1020 28.4
CA AUBURN -122 38 34.9 1020.6 28.8
CA BAKER -117 35 39.4 1022 29.3
CA BARSTOW -118 34 32.3 1020.8 27.6
CA BRIDGEPORT -120 38 33.9 1020.1 28.4
CA BURNEY -122 40 32.3 1020.8 27.6
CA BUTTONWILLOW -120 35 32.1 1021.3 27.4
CA CALISTOGA -123 38 33.8 1021.7 28.2
CA CALLAHAN -123 41 34.6 1020.2 28.8
CA CECILVILLE -124 41 33.8 1021.7 28.3
CA CLOVERDALE -124 38 32.1 1021.3 27.4
CA COVELO -124 39 33.5 1020.2 28.3
CA GLENNVILLE -119 35 33.8 1021.7 28.2
CA HAIWEE -118 36 33.1 1020.2 28
CA HEMET -117 33 33.4 1020.2 28.3
CA IMPERIAL -116 32 34.4 1020.2 28.7
CA KENTFIELD -123 37 32.7 1020 27.9
CA KLAMATH -125 41 32.1 1021.3 27.5
... ... ... ... ... ... ...

SQL Call

SELECT * FROM DWT2D (
  ON twod_climate_data AS InputTable
  OUT TABLE OutputTable (dwt2d_coeftable)
  OUT TABLE MetaInformationTable (dwt2d_metatable)
  USING
  TargetColumns ('temp_f','pressure_mbar','dewpoint_f')
  PartitionColumns ('state')
  IndexColumns ('latitude', 'longitude')
  Wavelet ('db2')
  CompactOutput ('true')
  WaveletTransformLevel (2)
) AS dt;

Output

 messages                     
 ---------------------------- 
 Dwt2D finished successfully!
SELECT * FROM dwt2d_coeftable ORDER BY state, waveletid, waveletcomponent;
 state waveletid waveletcomponent temp_f                pressure_mbar        dewpoint_f            
 ----- --------- ---------------- --------------------- -------------------- --------------------- 
 ca            2 A2                 0.17966670080766614    5.367577820865212   0.14864598865259468
 ca            3 A2                 -2.8794432544680917   -80.83794342982637   -2.3434013863320886
 ca            4 A2                   85.17099422732936    2535.209096707586     71.20340790342276
 ca            5 A2                -0.43698818294264186  -14.301367416569166  -0.37656963780789143
 ca            6 A2                 -3.5518013494034726  -112.24731824224867   -3.0373591313812405
 ca            7 A2                   7.607131707178469   243.03773964415961     6.527150195494035
 ca            8 A2                   77.48362490347102   2309.1677654492873     64.67758744765001
 ca            9 A2                  -2.467185519590279   -72.80570197561599   -2.0559702715297323
 ca           10 A2                   4.307237263004058    138.9649861032441     3.698570590257976
 ...
 tx            1 A2                 -3.6011517287761174   -89.66062613652628    -2.533400946695857
 tx            2 A2                  -4.892774826100907  -122.68985857717036   -3.4903463430079986
 tx            3 A2                   32.55414532700897    781.9783426823088    22.511340139411953
 tx            4 A2                   52.65077802036848   1237.3018397594763     35.65115873239475
 tx            5 A2                 -18.081617481567218   -449.8871020273652   -12.869485244673767
 tx            6 A2                   5.199579156036784    125.1711586833209    3.5429928486997295
 tx            7 A2                  0.7131422719011882   22.194219313520705    0.5933788322995257
 tx            8 A2                  34.490654091515125    824.5284208268199    23.648074260046357
 tx            9 A2                   50.07836808898107   1177.1087409858105      33.8303478810003
 tx           10 A2                 -21.489198057562923   -517.9621336089269   -14.920617758952584
 ...
 wa            1 A2                    42.6254931061425   1468.4366999990582     35.28539169480957
 wa            2 A2                   47.21635578657956   1624.9053300999767     39.10397731411011
 wa            3 A2                   73.03586814995711   2509.1676792770127    60.485928056688806
 wa            4 A2                  29.612765080680177   1012.0954617994031    24.068537871549648
 wa            5 A2                   63.39836472182281    2184.274991658562    52.422101734722204
 wa            6 A2                   67.34481712227416   2319.5128100909124     55.70701016166999
 wa            7 A2                   82.16956338227835   2826.8562365727303      67.8884826575598
 wa            8 A2                  46.981578273758515   1609.3291255609581    38.192760986972914
 wa            9 A2                   40.32603519406097   1391.6270194096492     33.09817180508851
 wa           10 A2                   53.39390787028372   1851.1134440137755     43.76464770186756

CompactOutput('true') prevents rows in which all coefficient values have an absolute value less than 1e-12) from appearing in dwt2d_coeftable.

SELECT * FROM dwt2d_metatable ORDER BY state;
 state meta            content                                                                          
 ----- --------------- -------------------------------------------------------------------------------- 
 ca    range           (32,-125),(41,-116)                                                             
 ca    extensionmode   sym                                                                             
 ca    ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 ca    waveletname     db2                                                                             
 ca    length          (10, 10)                                                                        
 ca    highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 ca    ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 ca    lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 ca    blocklength     (4, 4),(4, 4),(4, 4),(4, 4),(6, 6),(6, 6),(6, 6)                                
 ca    level           2                                                                               
 tx    highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 tx    extensionmode   sym                                                                             
 tx    lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 tx    ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 tx    length          (11, 11)                                                                        
 tx    waveletname     db2                                                                             
 tx    blocklength     (5, 5),(5, 5),(5, 5),(5, 5),(7, 7),(7, 7),(7, 7)                                
 tx    range           (26,-105),(36,-95)                                                              
 tx    level           2                                                                               
 tx    ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 wa    range           (45,-125),(48,-118)                                                             
 wa    highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 wa    level           2                                                                               
 wa    ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 wa    extensionmode   sym                                                                             
 wa    waveletname     db2                                                                             
 wa    blocklength     (3, 4),(3, 4),(3, 4),(3, 4),(3, 5),(3, 5),(3, 5)                                
 wa    ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 wa    lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 wa    length          (4, 8)

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.