Moving-average model example

Moving average models use the errors of previous predictions to make future predictions. More specifically, the user-specified lag determines how many previous predictions and errors it takes into account during computation.

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

• MOVING_AVERAGE: trains a moving-average model

• PREDICT_MOVING_AVERAGE: 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 MOVING_AVERAGE to create the moving-average model MA_temperature from the temp_data dataset. In this case, the model is trained with a lag of p=3, taking the error of 3 previous predictions into account for each estimation:

   => SELECT MOVING_AVERAGE('MA_temperature', 'temp_data', 'temperature', 'time' USING PARAMETERS q=3, missing='linear_interpolation', regularization='none', lambda=1);
                       MOVING_AVERAGE
   ---------------------------------------------------------
    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='MA_temperature');
   
    GET_MODEL_SUMMARY
   -------------------
   
   ============
   coefficients
   ============
   parameter| value
   ---------+--------
   phi_(t-0)|-0.90051
   phi_(t-1)|-0.10621
   phi_(t-2)| 0.07173
   
   
   ===============
   timeseries_name
   ===============
   temperature
   
   ==============
   timestamp_name
   ==============
   time
   
   ===========
   call_string
   ===========
   moving_average('public.MA_temperature', 'temp_data', 'temperature', 'time'
   USING PARAMETERS q=3, missing=linear_interpolation, regularization='none', lambda=1);
   
   ===============
   Additional Info
   ===============
          Name       | Value
   ------------------+--------
          mean       |11.17780
       lag_order     |   3
         lambda      | 1.00000
   rejected_row_count|   0
   accepted_row_count|  3650
   
   (1 row)
   

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

   => SELECT PREDICT_MOVING_AVERAGE(Temperature USING PARAMETERS model_name='MA_temperature', npredictions=10) OVER(ORDER BY time) FROM temp_data;
   
    index |    prediction
   -------+------------------
        1 | 13.1324365636272
        2 | 12.8071086272833
        3 | 12.7218966671721
        4 | 12.6011086656032
        5 | 12.506624729879
        6 | 12.4148247026733
        7 | 12.3307873804812
        8 | 12.2521385975133
        9 | 12.1789741993396
       10 | 12.1107640076638
   (10 rows)