ARIMA Example: OrdersTable, PartitionColumns, No Seasonality - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

The input table, milk_3, is partitioned into two time series.

InputTable: milk_3
id_partition id period milkpound
1 1 1962-01 578.3
1 2 1962-02 609.8
1 3 1962-03 628.4
1 4 1962-04 665.6
1 5 1962-05 713.8
1 6 1962-06 707.2
... ... ... ...
1 46 1965-10 609.7
1 47 1965-11 610.8
1 48 1965-12 623.5
2 49 1966-01 664.7
2 50 1966-02 690.3
2 51 1966-03 722.6
2 52 1966-04 766
2 53 1966-05 796.3
2 54 1966-06 809.6
... ... ... ...
2 153 1974-09 826.9
2 154 1974-10 797.3
2 155 1974-11 784.3
2 156 1974-12 798.2
OrdersTable: arimaex1_orders
id_partition p d q include_drift include_mean
1 1 1 1 0 0
2 0 1 2 0 0

SQL Call

SELECT * FROM ARIMA (
  ON milk_3 AS InputTable
  ON arimaex1_orders AS OrdersTable
  OUT TABLE ModelTable (arimaex1_model) 
  OUT TABLE ResidualTable (arimaex1_resid)
  USING
  TimeColumns ('period')
  TargetColumn ('milkpound')
  PartitionColumns ('id_partition')
) AS dt;

Output

 id_partition coef               value_col                               
 ------------ ------------------ --------------------------------------- 
            2 coef               0, 1, 2, 0, 0, 0                       
            2 ar_params          NULL                                   
            2 ma_params          0.8306295578094496 ,0.3998570673162409 
            2 seasonal_ar_params NULL                                   
            2 seasonal_ma_params NULL                                   
            2 period             0                                      
            2 sigma2             643.230614797225                       
            2 aic                6.50388649193462                       
            2 bic                6.553845909401198                      
            2 iterations         33                                     
            2 converged          true                                   
            1 coef               1, 1, 1, 0, 0, 0                       
            1 ar_params          0.34347347277005386                    
            1 ma_params          0.5815242132319824                     
            1 seasonal_ar_params NULL                                   
            1 seasonal_ma_params NULL                                   
            1 period             0                                      
            1 sigma2             591.665550628751                       
            1 aic                6.468047909957085                      
            1 bic                6.546777595136237                      
            1 iterations         33                                     
            1 converged          true
SELECT * FROM arimaex1_resid ORDER BY 1, 2;
 id_partition period  value_col residual            
 ------------ ------- --------- ------------------- 
            1 1962-01     578.3                 0.0
            1 1962-02     609.8                 0.0
            1 1962-03     628.4 -10.537427109064122
            1 1962-04     665.6  36.939162415564915
            1 1962-05     713.8  13.941769451794109
            1 1962-06     707.2 -31.262897899032737
            1 1962-07     628.4  -58.35294297563094
            1 1962-08     588.1   20.69935890795483
            1 1962-09     576.3  -9.995197450721776
            1 1962-10     566.5 0.06543631231600244
            1 1962-11     561.1 -2.0720127668898307
            1 1962-12     571.4   13.35968234703047
            1 1963-01     589.1   6.393244464582409
            1 1963-02     615.3   16.40269307470395
            1 1963-03     641.2   7.362431828271802
            1 1963-04     682.8  28.422604678845616
            1 1963-05     728.5  14.883070708896476
            1 1963-06     726.4 -26.451603690058487
            1 1963-07     648.0  -62.29645768259739
            1 1963-08     605.8  20.955218806184106
            1 1963-09     591.5 -11.991386578473922
            1 1963-10     576.3 -3.3150476937806896
            1 1963-11     573.2   4.048577288057238
            1 1963-12     587.1  12.610422043440755
            1 1964-01     616.6  17.392452971161127
            1 1964-02     648.6  11.753400023054681
            1 1964-03     675.5   9.073962170150615
            1 1964-04     715.3  25.283834870591907
            1 1964-05     756.0  12.326593603143596
            1 1964-06     746.7  -30.44758298863963
            1 1964-07     665.7 -60.099689960954365
            1 1964-08     627.4  24.470776214404353
            1 1964-09     612.8 -15.675314878164338
            1 1964-10     599.9  1.2302878541309212
            1 1964-11     602.7   6.515365622311407
            1 1964-12     622.5  15.049431408810435
            1 1965-01     646.1     8.0476164795558
            1 1965-02     676.1    17.2141422009603
            1 1965-03     696.1 -0.3146446829785212
            1 1965-04     732.5  29.713504046315613
            1 1965-05     767.8   5.518443528270989
            1 1965-06     767.0  -16.13372211982575
            1 1965-07     689.3  -68.04307115954892
            1 1965-08     641.2   18.15658225617777
            1 1965-09     624.0 -11.237418171266038
            1 1965-10     609.7 -1.8574255075507473
            1 1965-11     610.8   7.091808567527128
            1 1965-12     623.5   8.198120782329973
            2 1966-01     664.7                 0.0
            2 1966-02     690.3                 0.0
            2 1966-03     722.6                 0.0
            2 1966-04     766.0   50.82626291175626
            2 1966-05     796.3   -3.41518895279507
            2 1966-06     809.6  -4.186483540843128
            2 1966-07     721.7  -83.05699558869745
            2 1966-08     684.4  33.363590549830334
            2 1966-09     670.6  -8.301857789146538
            2 1966-10     654.9 -22.144899007999328
            2 1966-11     654.4  21.213764179594026
            2 1966-12     675.5  12.334014813382838
            2 1967-01     700.1   5.872529197953845
            2 1967-02     725.1  15.190260677566183
            2 1967-03     748.2   8.134348187560134
            2 1967-04     795.4  34.369436875597934
            2 1967-05     821.8  -5.400846764944703
            2 1967-06     828.9 -2.1567592742246173
            2 1967-07     753.1  -71.84896524929414
            2 1967-08     708.9  16.342269672386216
            2 1967-09     690.9  -2.845055697287684
            2 1967-10     674.5  -20.57138466871015
            2 1967-11     669.6   13.32481577836764
            2 1967-12     685.3  12.857627706386761
            2 1968-01     704.0   2.692052624098224
            2 1968-02     730.5   19.12268821192567
            2 1968-03     760.9  13.439693679067279
            2 1968-04     807.6  27.890251154638488
            2 1968-05     842.4   6.259576516087634
            2 1968-06     838.0  -20.75150330703941
            2 1968-07     768.8  -54.46612389255698
            2 1968-08     726.6  11.338807659225424
            2 1968-09     711.2  -3.039684224307921
            2 1968-10     693.2 -20.009050814363558
            2 1968-11     686.9  11.535548249620746
            2 1968-12     698.1   9.618993036741049
            2 1969-01     720.7   9.997609574341283
            2 1969-02     750.1   17.24946763390674
            2 1969-03     770.8  2.3744674822306884
            2 1969-04     816.7  37.030375584341535
            2 1969-05     855.2   6.792027898978013
            2 1969-06     857.3  -18.34851651313138
            2 1969-07     786.5  -58.27502019905369
            2 1969-08     750.1   19.34173826182107
            2 1969-09     735.6  -7.264140825094238
            2 1969-10     709.9  -27.40012065675655
            2 1969-11     700.1  15.863968151941663
            2 1969-12     720.7  18.379051038771355
            2 1970-01     736.4  -5.909502818525663
            2 1970-02     768.5  29.659614264607065
            2 1970-03     792.4  1.6268041849032997
            2 1970-04     836.0  30.389121981674506
            2 1970-05     869.9   8.007407895671037
            2 1970-06     871.5 -17.202494873488984
            2 1970-07     804.1  -56.31291792798243
            2 1970-08     768.8  18.353713268115776
            2 1970-09     750.8 -10.728018521353214
            2 1970-10     733.4 -15.827852681188727
            2 1970-11     721.4   5.436756297711515
            2 1970-12     737.4   17.81294827552777
            2 1971-01     789.4   35.03011322169974
            2 1971-02     821.8 -3.8196807130638604
            2 1971-03     844.4  11.765701361080806
            2 1971-04     890.8   38.15438700913751
            2 1971-05     924.9 -2.2967604510509503
            2 1971-06     926.3 -11.948544176871943
            2 1971-07     853.2  -62.25681013561294
            2 1971-08     818.9   22.19005650683353
            2 1971-09     801.5 -10.937891302747598
            2 1971-10     785.5  -15.78751510223506
            2 1971-11     774.1   6.087169827220009
            2 1971-12     785.5  12.656566307095773
            2 1972-01     811.0  12.553084049582958
            2 1972-02     838.6  12.112219860901213
            2 1972-03     873.9  20.219792799008225
            2 1972-04     913.1  17.561685736091665
            2 1972-05     943.6   7.827717692288189
            2 1972-06     948.6  -8.524097840965617
            2 1972-07     877.8  -66.84960061985213
            2 1972-08     839.5  20.635674966811337
            2 1972-09     820.8  -9.110316317666044
            2 1972-10     795.3  -26.18402245987319
            2 1972-11     777.2   7.292047362622494
            2 1972-12     790.4  17.612876355006726
            2 1973-01     806.1 -1.8455523716610607
            2 1973-02     840.3  28.690337264070386
            2 1973-03     867.0  3.6069150038522118
            2 1973-04     911.1  29.631955666568945
            2 1973-05     939.6  2.4445713121493284
            2 1973-06     937.5  -15.97908007972341
            2 1973-07     865.0 -60.204782894899004
            2 1973-08     821.8  13.197220293093395
            2 1973-09     795.4  -13.28869332960074
            2 1973-10     776.6 -13.039020338891014
            2 1973-11     771.1  10.644173641600506
            2 1973-12     787.4  12.672379188216105
            2 1974-01     813.0  10.817799222163195
            2 1974-02     845.7  18.647275837503766
            2 1974-03     872.9   7.385448044953257
            2 1974-04     915.2  28.709183526374623
            2 1974-05     951.4    9.40017998634557
            2 1974-06     960.8  -9.887637275287375
            2 1974-07     891.5  -64.84576462383274
            2 1974-08     851.3   17.61645043889294
            2 1974-09     826.9 -13.103707167864707
            2 1974-10     797.3  -25.75973571850968
            2 1974-11     784.3  13.636407808267165
            2 1974-12     798.2   12.87340899135451

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