1.1 - 8.10 - VARMAX Example: No Exogenous Model - 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)

Input

The input table, finance_data3, has seasonally adjusted quarterly financial data from West Germany between 1960 and 1982, in billions of Deutsche Marks. The table has three time series—consumer expenditures, disposable income, and fixed investmen—partitioned by the column id, which indicates the decade.

finance_data3
id period expenditure income id investment
1 1960Q1 415 451 1 180
1 1960Q2 421 465 1 179
1 1960Q3 434 485 1 185
1 1960Q4 448 493 1 192
1 1961Q1 459 509 1 211
1 1961Q2 458 520 1 202
1 1961Q3 479 521 1 207
1 1961Q4 487 540 1 214
1 1962Q1 497 548 1 231
1 1962Q2 510 558 1 229
1 1962Q3 516 574 1 234
1 1962Q4 525 583 1 237
1 1963Q1 529 591 1 206
1 1963Q2 538 599 1 250
... ... ... ... ... ...

SQL Call

Three values are predicted (StepAhead (3)) for each time series with PDQ (1, 1, 1).

SELECT * FROM VARMAX (
  ON finance_data3 AS InputTable PARTITION BY id ORDER BY period
  USING
  TargetColumns ('expenditure', 'income', 'investment')
  PartitionColumns ('id')
  PDQ ('1, 1, 1')
  IncludeMean ('false')
  StepAhead (3)
) AS dt ORDER BY id;

Output

Series id = 2 does not converge. You might improve convergence by adding more orders or more models.

 id coef               coef_value                                                                                                                                                                                       stepahead predict_expenditure predict_income     predict_investment 
 -- ------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------- ------------------- ------------------ ------------------ 
  1 ma_params          [[1.248390739091949, -1.265426674704899, 1.8366448514566578],[-0.04357992971257406, -0.8104294171113546, 3.1912081090547098],[0.5282557132572212, -0.8969134194646912, 0.6941781537982473]]      NULL                     NULL               NULL               NULL
  1 aic                25.8626571338923                                                                                                                                                                                 NULL                     NULL               NULL               NULL
  1 converged          false                                                                                                                                                                                            NULL                     NULL               NULL               NULL
  1 seasonal_ar_params NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  1 sigma              [[10937.698750122627, 10097.09183807659, 3520.1342920478005],[10097.09183807659, 14134.11499016197, 2999.6089815260793],[3520.1342920478005, 2999.6089815260793, 2433.4777442199834]]            NULL                     NULL               NULL               NULL
  1 period             0                                                                                                                                                                                                NULL                     NULL               NULL               NULL
  1 bic                26.630454816721365                                                                                                                                                                               NULL                     NULL               NULL               NULL
  1 NULL               NULL                                                                                                                                                                                             3          1753.0780834153084  2213.770938403275  743.8238313279019
  1 NULL               NULL                                                                                                                                                                                             1          1870.2700856952113  2220.305114150415  732.6964571772069
  1 coef               1, 1, 1, 0, 0, 0, 0                                                                                                                                                                              NULL                     NULL               NULL               NULL
  1 seasonal_ma_params NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  1 exogenous_params   NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  1 iterations         100                                                                                                                                                                                              NULL                     NULL               NULL               NULL
  1 NULL               NULL                                                                                                                                                                                             2          1994.0913688056626  2240.692404271746  749.3573689292496
  1 lag                0                                                                                                                                                                                                NULL                     NULL               NULL               NULL
  1 ar_params          [[-2.342501109086588, 1.9423693188736098, 0.5665092606109724],[-0.23679729916046047, 0.463259213827403, -0.4228772439268348],[-0.05041900311204333, 0.3456632515726913, -0.3803950846917263]]    NULL                     NULL               NULL               NULL
  2 ma_params          [[-1.7350580320377782, 2.253563262977889, 1.3630071115741689],[-2.5529132696801202, 2.8604609381524764, 0.8539594322792532],[-1.0527526199458779, 0.9124434365333087, 0.8251886996083113]]       NULL                     NULL               NULL               NULL
  2 seasonal_ar_params NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  2 seasonal_ma_params NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  2 period             0                                                                                                                                                                                                NULL                     NULL               NULL               NULL
  2 sigma              [[13901.648209349993, 14915.088344538926, 3504.4514805844747],[14915.088344538926, 20430.147818275578, 5617.087770107875],[3504.4514805844747, 5617.087770107875, 2572.3863667164956]]           NULL                     NULL               NULL               NULL
  2 aic                25.67191854988387                                                                                                                                                                                NULL                     NULL               NULL               NULL
  2 bic                26.439716232712936                                                                                                                                                                               NULL                     NULL               NULL               NULL
  2 iterations         100                                                                                                                                                                                              NULL                     NULL               NULL               NULL
  2 converged          false                                                                                                                                                                                            NULL                     NULL               NULL               NULL
  2 NULL               NULL                                                                                                                                                                                             1          1610.2071609154068 1959.9484516211046  615.9731282343708
  2 NULL               NULL                                                                                                                                                                                             2          1895.7603142841267 2113.7353384844932  562.4152008833979
  2 NULL               NULL                                                                                                                                                                                             3           965.7973575959059 1398.1791635434158  645.9978584480411
  2 lag                0                                                                                                                                                                                                NULL                     NULL               NULL               NULL
  2 exogenous_params   NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  2 ar_params          [[-4.059896316264422, 2.184439535900078, 1.9900866644404875],[-2.136312518482259, 0.2996326943183063, 2.8306729828361092],[0.48777641750942646, -0.4954211191320447, -0.38249865177522036]]      NULL                     NULL               NULL               NULL
  2 coef               1, 1, 1, 0, 0, 0, 0                                                                                                                                                                              NULL                     NULL               NULL               NULL
  3 NULL               NULL                                                                                                                                                                                             1           2291.751800546244  2771.519391960251  843.1553213951414
  3 converged          false                                                                                                                                                                                            NULL                     NULL               NULL               NULL
  3 iterations         100                                                                                                                                                                                              NULL                     NULL               NULL               NULL
  3 aic                26.01717883870844                                                                                                                                                                                NULL                     NULL               NULL               NULL
  3 sigma              [[4675.639154179282, -271.81096013599335, 500.1394808930519],[-271.81096013599335, 1347.982467607846, 256.8154939947388],[500.1394808930519, 256.8154939947388, 1326.5247222046553]]             NULL                     NULL               NULL               NULL
  3 lag                0                                                                                                                                                                                                NULL                     NULL               NULL               NULL
  3 bic                26.668280194196683                                                                                                                                                                               NULL                     NULL               NULL               NULL
  3 period             0                                                                                                                                                                                                NULL                     NULL               NULL               NULL
  3 seasonal_ma_params NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  3 seasonal_ar_params NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  3 exogenous_params   NULL                                                                                                                                                                                             NULL                     NULL               NULL               NULL
  3 NULL               NULL                                                                                                                                                                                             2          2256.0140486155283  3057.017727562449 1012.7661239880138
  3 ma_params          [[-0.6946133251302785, -0.21908174663171212, 2.0539095733809596],[-5.146412337538272, 1.8914154757069057, -0.023736634213482252],[-1.3446784229340576, -0.10492861623291512, 0.197379047992889]] NULL                     NULL               NULL               NULL
  3 ar_params          [[1.4735147057787517, -0.3714896584284457, -1.637673972878577],[-0.49114758180592777, 2.419163855654859, 0.31427398834899123],[-1.6601973673615065, 1.665300190890637, 0.25555596015840437]]     NULL                     NULL               NULL               NULL
  3 coef               1, 1, 1, 0, 0, 0, 0                                                                                                                                                                              NULL                     NULL               NULL               NULL
  3 NULL               NULL                                                                                                                                                                                             3          1819.5270694940025 3818.5417557387937 1590.8833299459645

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