1.1 - 8.10 - DWT 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 hourly climate data for five cities (Asheville, Greenville, Brownsville, Nashville and Knoxville) on a given day. The data are temperature (in degrees Fahrenheit), pressure (in mbars), and dewpoint (in degrees Fahrenheit). The function creates the coefficient model table and the meta table, which are used as input to the function IDWT (ML Engine).

Input

InputTable: ville_climatedata
city period temp_f pressure_mbar dewpoint_f
Asheville 2010-01-01 00:00:00 34.9 1020.5 28.9
Asheville 2010-01-01 01:00:00 34.4 1020.2 28.7
Asheville 2010-01-01 02:00:00 33.9 1020 28.4
Asheville 2010-01-01 03:00:00 33.4 1020.2 28.3
Asheville 2010-01-01 04:00:00 33.1 1020.2 28
Asheville 2010-01-01 05:00:00 32.7 1020 27.9
Asheville 2010-01-01 06:00:00 32.5 1020.3 27.7
Asheville 2010-01-01 07:00:00 32.3 1020.8 27.6
Asheville 2010-01-01 08:00:00 32.1 1021.3 27.4
Asheville 2010-01-01 09:00:00 33.8 1021.7 28.2
Asheville 2010-01-01 10:00:00 36.4 1022.1 28.9
Asheville 2010-01-01 11:00:00 39.4 1022 29.3
Asheville 2010-01-01 12:00:00 42.1 1021.1 29.2
Asheville 2010-01-01 13:00:00 44.2 1020 29.1
Asheville 2010-01-01 14:00:00 45.6 1019.3 28.9
Asheville 2010-01-01 15:00:00 46.2 1019 28.5
Asheville 2010-01-01 16:00:00 45.8 1019.2 28.5
Asheville 2010-01-01 17:00:00 44.1 1019.6 28.6
Asheville 2010-01-01 18:00:00 41.2 1020.1 28.5
Asheville 2010-01-01 19:00:00 39.6 1020.6 28.8
Asheville 2010-01-01 20:00:00 38.2 1020.9 29
Asheville 2010-01-01 21:00:00 37.2 1021.1 29
Asheville 2010-01-01 22:00:00 36.3 1021 29
Asheville 2010-01-01 23:00:00 35.5 1020.9 29
Brownsville 2010-01-01 00:00:00 35.1 1020.5 28.9
Brownsville 2010-01-01 01:00:00 34.6 1020.2 28.8
Brownsville 2010-01-01 02:00:00 34.1 1020 28.5
Brownsville 2010-01-01 03:00:00 33.7 1020.1 28.4
... ... ... ... ...

SQL Call

SELECT * FROM DWT (
  ON ville_climatedata AS InputTable
  OUT TABLE OutputTable (dwt_coef_table)
  OUT TABLE MetaInformationTable (dwt_meta_table)
  USING
  TargetColumns ('temp_f', 'pressure_mbar', 'dewpoint_f')
  SortColumn ('period')
  PartitionColumns ('city')
  Wavelet ('db2')
  WaveletTransformLevel (2)
) AS dt;

Output

 messages                   
 -------------------------- 
 Dwt finished successfully!
SELECT * FROM dwt_coef_table ORDER BY city, waveletid, waveletcomponent;
 city        waveletid waveletcomponent temp_f                  pressure_mbar          dewpoint_f             
 ----------- --------- ---------------- ----------------------- ---------------------- ---------------------- 
 asheville           1 A2                     69.45400635094613     2040.7728765877373      57.65367785792577
 asheville           2 A2                     69.30023184096771     2040.6503284930213      57.59180262661559
 asheville           3 A2                      65.9380131645126      2040.058678320547     56.005921506979654
 asheville           4 A2                     65.36879316306577     2042.9121070722786      55.77428721187067
 asheville           5 A2                     84.14229220639045      2041.646914693414      58.26704310019643
 asheville           6 A2                     90.15648816047914     2038.6691751450737      57.09991198056049
 asheville           7 A2                     76.62920597456294     2041.6417468245272     57.802572142074276
 asheville           8 A2                      71.1744954681006     2041.9750000000006      58.05290063509463
 asheville           9 D2                   0.16626587736527298    0.13358166849354802    0.08023230358803346
 asheville          10 D2                 -0.023653810567672906  -0.015280398304071241    0.03459936490539306
 asheville          11 D2                   -1.0857608828455234    -0.3926120667978239    -0.5292146071760548
 asheville          12 D2                   -0.7317867492503041     1.5893185039809623     0.6914741335952215
 asheville          13 D2                    3.2634446290970445     -1.206962788129431   -0.20110571585150083
 asheville          14 D2                   -0.7483811196359333     0.1460816684935935   -0.12532849302035487
 asheville          15 D2                   -1.4011615179401318    0.05580127018947678   0.052900635094612625
 asheville          16 D2                   -0.8415943703856321    0.46650635094601967    0.19742785792574935
 asheville          17 D1                   0.30618621784789823    0.18371173070875102    0.12247448713916143
 asheville          18 D1                                   0.0   -0.20612611751289478   -0.08365163037377954
 asheville          19 D1                  0.022414386804204156    0.12247448713924314   -0.07071067811865106
 asheville          20 D1                 -0.025881904510249854   -0.16129734390449357   -0.03535533905932553
 asheville          21 D1                   -0.9176295349746102    0.04829629131444335   -0.47002196088940806
 asheville          22 D1                   -0.3096537355539475     0.2414814565723873    0.15782982619848873
 asheville          23 D1                   0.32860060465209884    0.20012020066997138    0.06470476127563174
 asheville          24 D1                    0.4769569963015101   -0.24494897427831575    0.10953353488403295
 asheville          25 D1                    0.7572613096391514    -0.1612973439043799   -0.10006010033495549
 asheville          26 D1                  -0.47256036002637813  -0.012940952255007687   -0.16730326074755908
 asheville          27 D1                  -0.21906706976806234    0.07417819582474294    0.10953353488403295
 asheville          28 D1                 -0.061237243569578936    0.03882285676547781                    0.0
 asheville          29 D1                   -0.4898979485566315   -0.06123724356950788                    0.0
 brownsville         1 A2                     69.84943103339883     2040.7774519052846      57.72410254037846
 brownsville         2 A2                     69.70815652342041     2040.6424038105683      57.68399818095332
 brownsville         3 A2                     66.37718784696528      2040.034455543378      56.32601769641199
 brownsville         4 A2                     65.78888935249812     2042.7775077073732       56.2960580837557
 brownsville         5 A2                      84.8067953818635     2041.4398874705828      59.04159437038564
 brownsville         6 A2                     90.64856347802647     2038.6382536380943     57.797788568297015
 brownsville         7 A2                     77.10375724475216     2041.3780208718854     58.443750000000016
 brownsville         8 A2                     71.57617015055332     2041.7545753175477     58.505024047358106
 brownsville         9 D2                   0.17419055981796205    0.12565698604089448    0.04485571585150083
 brownsville        10 D2                  0.049222777168864695   -0.08815698604064437 -0.0012259526419242661
 brownsville        11 D2                   -1.1011615179401275    -0.3214101615138816    -0.5725158773652694
 brownsville        12 D2                   -0.6884854790610753      1.578493186433775     0.6544228634060048
 brownsville        13 D2                    3.2992699466443476    -1.1898874705822209    -0.1710816684934109
 brownsville        14 D2                   -0.7346551669940204    0.15568103339887784   -0.08325317547305744
 brownsville        15 D2                    -1.421137470582046    0.07287658773657313  0.0016746824526912008
 brownsville        16 D2                   -0.8353443703856307    0.39028039830418493    0.20535254037844552
 brownsville        17 D1                   0.30618621784789823    0.18371173070875102    0.06123724356958071
 brownsville        18 D1                  -0.04829629131445401   -0.15782982619839458   -0.07071067811865106
 brownsville        19 D1               -1.7763568394002505E-15    0.14488887394344374  -0.035355339059327306
 brownsville        20 D1                   -0.0258819045102463   -0.16129734390449357  -0.035355339059327306
 brownsville        21 D1                   -0.9176295349746191   0.012940952255235061     -0.434666621830079
 brownsville        22 D1                  -0.27429839649462195     0.2414814565722736    0.15782982619848696
 brownsville        23 D1                    0.4122522350258855    0.20012020066997138    0.18371173070874036
 brownsville        24 D1                   0.46401604404638874    -0.2932452655926454    0.01294095225512848
 brownsville        25 D1                     0.721905970579833    -0.1483563916492585   -0.03882285676537478
 brownsville        26 D1                   -0.3889087296525986  -0.048296291314329665   -0.22854050431713624
 brownsville        27 D1                  -0.14835639164940417     0.1224744871391863    0.08711914807983412
 brownsville        28 D1                  -0.07417819582470386    0.06123724356967841    0.04829629131445756
 brownsville        29 D1                   -0.4898979485566315  -0.061237243569564725  -0.061237243569578936
 greenville          1 A2                     69.44943103339882     2040.9103765877376      57.52410254037846
 greenville          2 A2                      69.3081565234204     2040.7686297632104      57.50017603887906
 greenville          3 A2                     65.97596189432336      2040.112804908283      55.98851769641199
 greenville          4 A2                     65.39235895419392     2042.8979323898257      55.91860617847186
 greenville          5 A2                     84.70646688884312      2041.583188740772     58.804094370385656
 greenville          6 A2                     90.78851538331027     2038.6858017328102       57.5340626156551
 greenville          7 A2                       77.102980021921     2041.5559215069807      58.24375000000002
 greenville          8 A2                     71.71322142074253      2041.902900635095     58.305024047358096
 greenville          9 D2                   0.17419055981796916    0.15523230358826368   0.044855715851504385
 greenville         10 D2                   0.04922277716885759  -0.015280398304184928   0.059150635094614046
 greenville         11 D2                   -1.1057368354874413   -0.35891016151367694    -0.5941665124598821
 greenville         12 D2                   -0.7072354790610724     1.4748153285080434     0.7068747686898327
 greenville         13 D2                     3.309646534380864    -1.1898874705821072   -0.10612976320958012
 greenville         14 D2                   -0.7654564371832393     0.2081329386824109   -0.06617785792574793
 greenville         15 D2                   -1.3807368354874399    0.06540063509464744  0.0016746824526947535
 greenville         16 D2                   -0.8836696879329367    0.38403039830450325    0.20535254037844552
 greenville         17 D1                   0.30618621784789823     0.2449489742783726    0.06123724356958249
 greenville         18 D1                  -0.04829629131445756   -0.13541543939425082   -0.07071067811865284
 greenville         19 D1                1.7763568394002505E-15    0.10953353488406492   -0.07071067811865106
 greenville         20 D1                 -0.025881904510249854   -0.07764571353072824   -0.03535533905932553
 greenville         21 D1                   -0.9176295349746102    0.03535533905932198     -0.434666621830079
 greenville         22 D1                  -0.23894305743528932    0.20612611751295162    0.19318516525781604
 greenville         23 D1                   0.36395594371142437    0.23547553972929336    0.10006010033496082
 greenville         24 D1                    0.5606086266752932   -0.24494897427831575   0.025881904510255183
 greenville         25 D1                    0.7443203573840336    -0.1612973439043799   0.009473434549077453
 greenville         26 D1                   -0.4242640687119277  -0.012940952255007687    -0.2061261175129392
 greenville         27 D1                  -0.14835639164940417     0.1224744871391863    0.08711914807983412
 greenville         28 D1                  -0.12247448713915432  -0.022414386804086917    0.04829629131445401
 greenville         29 D1                   -0.4286607049870561 1.1368683772161603E-13   -0.06123724356957716
 knoxville           1 A2                     69.18358166849342     2040.5103765877373       57.1878284930204
 knoxville           2 A2                     69.00803628662999     2040.3549038105682     57.089230484541346
 knoxville           3 A2                     65.39928721187067      2039.775304908283      55.28394237886469
 knoxville           4 A2                     64.80853681211968     2042.5192545318996     55.304311259228754
 knoxville           5 A2                     83.99887069941079     2041.3682368354878     57.849967782649124
 knoxville           6 A2                     89.79075903236415     2038.5978530029997       56.6828366630132
 knoxville           7 A2                     76.46135343418449     2041.3780208718854      57.44375000000002
 knoxville           8 A2                     71.18197142074253     2041.7545753175477      57.50502404735809
 knoxville           9 D2                    0.2016424651017985       0.15523230358815    0.10768420887186991
 knoxville          10 D2                  0.012171506979655078   -0.06650635094570134  -0.011602540378440551
 knoxville          11 D2                   -1.1119868354874285    -0.3805607966081652     -0.567940559817977
 knoxville          12 D2                   -0.6468588913245519     1.5226919162445256     0.6419228634059948
 knoxville          13 D2                     3.226393358907824    -1.2456887407713566    -0.2331329386826333
 knoxville          14 D2                   -0.7887817547305538    0.16650635094629251   -0.05245190528383503
 knoxville          15 D2                   -1.3561855652982153    0.07287658773657313  0.0016746824526983062
 knoxville          16 D2                    -0.813693735291011    0.39028039830418493    0.20535254037845618
 knoxville          17 D1                    0.3674234614174736     0.2449489742783726    0.18371173070874036
 knoxville          18 D1                 -0.012940952255121374   -0.13541543939430767   -0.04829629131445046
 knoxville          19 D1                  -0.01294095225512848    0.06123724356967841 1.7763568394002505E-15
 knoxville          20 D1                 -0.025881904510249854     -0.100060100334872   -0.01294095225512848
 knoxville          21 D1                   -0.9176295349746155    0.03535533905932198    -0.4346666218300772
 knoxville          22 D1                  -0.23894305743529287    0.24148145657233044    0.20612611751294097
 knoxville          23 D1                   0.41225223502588193     0.2354755397293502    0.17077077845361543
 knoxville          24 D1                    0.5123123353608392   -0.24494897427820206    0.01294095225512848
 knoxville          25 D1                    0.7443203573840336   -0.12594200484511475   -0.08711914807983057
 knoxville          26 D1                  -0.38890872965260215  -0.048296291314329665    -0.2155995520620131
 knoxville          27 D1                  -0.11300105259007864     0.1224744871391863    0.08711914807983412
 knoxville          28 D1                  -0.03882285676537833    0.06123724356967841   0.048296291314455786
 knoxville          29 D1                   -0.4898979485566315  -0.061237243569564725  -0.061237243569578936
 nasheville          1 A2                     69.64943103339883     2040.5774519052848      57.79117785792577
 nasheville          2 A2                     69.51228311115692      2040.440729128116     57.696826673973675
 nasheville          3 A2                     66.11011252941796     2039.8265308609248      56.15056896660121
 nasheville          4 A2                     65.51924189287656     2042.5329804845417     56.092588482059895
 nasheville          5 A2                     84.43257260469463     2041.3206887407719      58.49661841774373
 nasheville          6 A2                     90.12446093764801     2038.4478530029992      57.33149047358085
 nasheville          7 A2                     76.68467875173178     2041.2417468245278      57.86707531754732
 nasheville          8 A2                     71.37572142074254     2041.5750000000007     57.898774047358096
 nasheville          9 D2                   0.17419055981797626     0.1256569860407808    0.10188293868263898
 nasheville         10 D2                     0.064623412263483   -0.09440698604032605  -0.014951905283837164
 nasheville         11 D2                   -1.0715862003928258   -0.39428674924999996    -0.5741905598179535
 nasheville         12 D2                     -0.60020825622993     1.5739178688862694     0.6731728634059841
 nasheville         13 D2                    3.1722667711712944    -1.1682368354875052    -0.1773316684934052
 nasheville         14 D2                   -0.7813058020886388    0.09820508075677026   -0.07577722283113175
 nasheville         15 D2                   -1.3624355652982132    0.05580127018924941  -0.004575317547303115
 nasheville         16 D2                   -0.8370190528383219     0.4665063509463607     0.1820272228311346
 nasheville         17 D1                    0.3061862178479018    0.18371173070875102    0.18371173070873859
 nasheville         18 D1                  -0.04829629131445401   -0.15782982619845143  -0.012940952255121374
 nasheville         19 D1                  -0.08365163037377776    0.14488887394344374   -0.04829629131445223
 nasheville         20 D1                  -0.09659258262890269   -0.11300105258999338   -0.03535533905932553
 nasheville         21 D1                    -0.856392291405033    0.03535533905932198    -0.4346666218300772
 nasheville         22 D1                   -0.2518840096904178    0.24148145657233044    0.12247448713916143
 nasheville         23 D1                   0.37689689596655285     0.2837718310438504    0.14835639164941128
 nasheville         24 D1                    0.4286607049870561   -0.22253458747400146   -0.03535533905932553
 nasheville         25 D1                     0.673609679265379   -0.16129734390455042   -0.10953353488403117
 nasheville         26 D1                  -0.32767148608302676   -0.04829629131444335   -0.16730326074755908
 nasheville         27 D1                  -0.12594200484521068    0.07417819582485663    0.07417819582470564
 nasheville         28 D1                  -0.03882285676537833    0.03882285676530728    0.04829629131445401
 nasheville         29 D1                  -0.48989794855663504   -0.06123724356950788   -0.06123724356957716
SELECT * FROM dwt_meta_table ORDER BY city;
 city        meta            content                                                                          
 ----------- --------------- -------------------------------------------------------------------------------- 
 asheville   waveletname     db2                                                                             
 asheville   highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 asheville   ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 asheville   ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 asheville   extensionmode   sym                                                                             
 asheville   level           2                                                                               
 asheville   lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 asheville   length          24                                                                              
 asheville   blocklength     8,8,13                                                                          
 brownsville waveletname     db2                                                                             
 brownsville highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 brownsville ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 brownsville ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 brownsville extensionmode   sym                                                                             
 brownsville level           2                                                                               
 brownsville lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 brownsville length          24                                                                              
 brownsville blocklength     8,8,13                                                                          
 greenville  waveletname     db2                                                                             
 greenville  highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 greenville  ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 greenville  ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 greenville  extensionmode   sym                                                                             
 greenville  level           2                                                                               
 greenville  lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 greenville  length          24                                                                              
 greenville  blocklength     8,8,13                                                                          
 knoxville   waveletname     db2                                                                             
 knoxville   highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 knoxville   ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 knoxville   ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 knoxville   extensionmode   sym                                                                             
 knoxville   level           2                                                                               
 knoxville   lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 knoxville   length          24                                                                              
 knoxville   blocklength     8,8,13                                                                          
 nasheville  waveletname     db2                                                                             
 nasheville  highpassfilter  -0.4829629131445342, 0.836516303737808, -0.2241438680420134, -0.1294095225512604
 nasheville  ilowpassfilter  0.4829629131445342, 0.836516303737808, 0.2241438680420134, -0.1294095225512604  
 nasheville  ihighpassfilter -0.1294095225512604, -0.2241438680420134, 0.836516303737808, -0.4829629131445342
 nasheville  extensionmode   sym                                                                             
 nasheville  level           2                                                                               
 nasheville  lowpassfilter   -0.1294095225512604, 0.2241438680420134, 0.836516303737808, 0.4829629131445342  
 nasheville  length          24                                                                              
 nasheville  blocklength     8,8,13

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