Autoregressive model example

Autoregressive models predict future values of a time series based on the preceding values. More specifically, the user-specified lag determines how many previous timesteps it takes into account during computation, and predicted values are linear combinations of the values at each lag.

Use the following functions when training and predicting with autoregressive models. Note that these functions require datasets with consistent timesteps.

• AUTOREGRESSOR: trains an autoregressive model

• PREDICT_AUTOREGRESSOR: applies the model to a dataset to make predictions

To normalize datasets with inconsistent timesteps, see Gap filling and interpolation (GFI).

Example

1. Load the datasets from the Machine-Learning-Examples repository.

   This example uses the daily-min-temperatures dataset, which contains data on the daily minimum temperature in Melbourne, Australia from 1981 through 1990:

   => SELECT * FROM temp_data;
           time         | Temperature
   ---------------------+-------------
    1981-01-01 00:00:00 |        20.7
    1981-01-02 00:00:00 |        17.9
    1981-01-03 00:00:00 |        18.8
    1981-01-04 00:00:00 |        14.6
    1981-01-05 00:00:00 |        15.8
   ...
    1990-12-27 00:00:00 |          14
    1990-12-28 00:00:00 |        13.6
    1990-12-29 00:00:00 |        13.5
    1990-12-30 00:00:00 |        15.7
    1990-12-31 00:00:00 |          13
   (3650 rows)
   

2. Use AUTOREGRESSOR to create the autoregressive model AR_temperature from the temp_data dataset. In this case, the model is trained with a lag of p=3, taking the previous 3 entries into account for each estimation:

   => SELECT AUTOREGRESSOR('AR_temperature', 'temp_data', 'Temperature', 'time' USING PARAMETERS p=3);
                       AUTOREGRESSOR
   ---------------------------------------------------------
    Finished. 3650 elements accepted, 0 elements rejected.
   (1 row)
   

   You can view a summary of the model with GET_MODEL_SUMMARY:

   => SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='AR_temperature');
   
    GET_MODEL_SUMMARY
   -------------------
   
   ============
   coefficients
   ============
   parameter| value
   ---------+--------
     alpha  | 1.88817
   phi_(t-1)| 0.70004
   phi_(t-2)|-0.05940
   phi_(t-3)| 0.19018
   
   
   ==================
   mean_squared_error
   ==================
   not evaluated
   
   ===========
   call_string
   ===========
   autoregressor('public.AR_temperature', 'temp_data', 'temperature', 'time'
   USING PARAMETERS p=3, missing=linear_interpolation, regularization='none', lambda=1, compute_mse=false);
   
   ===============
   Additional Info
   ===============
          Name       |Value
   ------------------+-----
       lag_order     |  3
   rejected_row_count|  0
   accepted_row_count|3650
   (1 row)
   

3. Use PREDICT_AUTOREGRESSOR to predict future temperatures. The following query starts the prediction at the end of the dataset and returns 10 predictions.

   => SELECT PREDICT_AUTOREGRESSOR(Temperature USING PARAMETERS model_name='AR_temperature', npredictions=10) OVER(ORDER BY time) FROM temp_data;
   
    index |    prediction
   -------+------------------
         1 | 12.6235419917807
         2 | 12.9387860506032
         3 | 12.6683380680058
         4 | 12.3886937385419
         5 | 12.2689506237424
         6 | 12.1503023330142
         7 | 12.0211734746741
         8 | 11.9150531529328
         9 | 11.825870404008
        10 | 11.7451846722395
   (10 rows)