Skip to main content

SARIMA / AR(p) / ARIMA Forecasting

TYPE 'ar' (aliases: arima, auto_regressive, auto-regressive) is a univariate time-series forecaster covering AR(p), ARIMA(p,d,q), and full SARIMA(p,d,q)(P,D,Q,s). The fit uses autoregressive estimation with differencing and residual-based moving-average terms. The training SELECT must produce one value column ordered chronologically. At inference, supply an explicit positive forecast horizon: model(1) predicts the next step and model(7) predicts seven steps ahead.

When to use it​

  • Univariate metrics with stable seasonality (daily revenue, hourly traffic, weekly active users).
  • Short-to-medium horizons where a parametric model with explicit lag / differencing / seasonal terms beats a hand-rolled rolling average.
  • Pipelines where you want forecasts callable from SQL alongside other model outputs.

When NOT to use it​

  • Multivariate forecasting (use a regression with lag features or a DNN).
  • Regime changes mid-series — fit on the post-change window only.
  • Highly irregular or event-driven series with no autocorrelation; check the ACF first.

Syntax​

CREATE MODEL <name>(DOUBLE) RETURNS DOUBLE
TYPE { 'ar' | 'arima' | 'auto_regressive' | 'auto-regressive' }
OPTIONS (p = <int>, ...)
AS SELECT <value_col> FROM <source> ORDER BY <time_col>;

The training query supplies the value series in row order. The SQL model call instead takes a numeric horizon, not an observed value or an empty argument list. ORDER BY <time> in the training SELECT is mandatory for correct results.

Options​

OptionDefaultTypeRangeWhat it does
p(required)int>= 1Non-seasonal AR order (lag count)
d0int>= 0Non-seasonal differencing order
q0int>= 0Non-seasonal MA order
seasonal_period0int>= 0Seasonality period in samples (use at least 2 when any seasonal term is enabled; 7 for daily-with-weekly, 24 for hourly-with-daily)
seasonal_p0int>= 0Seasonal AR order
seasonal_d0int0 or 1Seasonal differencing order
seasonal_q0int>= 0Seasonal MA order
forecast_horizon1int>= 1Stored model default; SQL scoring selects the horizon through the function argument

Examples​

These fragments assume the named source tables exist. Match training and inference feature order and preprocessing. For a complete dataset and runnable script, follow the linked tutorial.

Minimal AR(7):

CREATE MODEL daily_traffic(DOUBLE) RETURNS DOUBLE
TYPE 'ar'
OPTIONS (p = 7, forecast_horizon = 7)
AS SELECT visits FROM daily_metrics ORDER BY day;

SELECT daily_traffic(1) AS next_day, daily_traffic(7) AS seven_days_ahead;

Full SARIMA with weekly seasonality and first-order differencing:

CREATE MODEL revenue_forecast(DOUBLE) RETURNS DOUBLE
TYPE 'arima'
OPTIONS (
    p = 7,
    d = 1,
    q = 1,
    seasonal_period = 7,
    seasonal_p = 1,
    seasonal_d = 1,
    seasonal_q = 1,
    forecast_horizon = 14
)
AS SELECT revenue FROM daily_metrics ORDER BY day;

-- 14-day-ahead forecast
SELECT revenue_forecast(14) AS predicted_revenue;

Output shape​

model_name(horizon) returns one DOUBLE at that horizon, counted from the end of the training series. Horizons must be integral numeric values in 1..10000; a NULL horizon produces NULL. Repeating a horizon produces the same forecast value and does not advance a cursor. To obtain several steps, supply distinct horizon values, as the forecasting tutorial does with held-out day indices.

Tuning notes​

  • Pick p from a partial-autocorrelation (PACF) plot of the series before training.
  • Set d = 1 if the series has a non-stationary trend; d = 2 is rarely needed.
  • Set seasonal_period to the structural period (7 for daily-with-weekly, 12 for monthly-with-yearly, 24 for hourly-with-daily). seasonal_p = 1 and seasonal_d = 1 are sensible starting values.
  • Use enough ordered observations to support all differencing and lag terms, ideally several seasonal cycles. Check missing periods and duplicate timestamps before fitting.
  • There is no gradient-descent learning-rate knob. Evaluate the training window, sampling interval, transformations, lag orders, and forecast horizon together.

Convergence and quality​

Hold out future observations chronologically. Compare each prediction with the actual value at the same horizon and compute SQRT(AVG(POWER(actual - prediction, 2))) for RMSE and AVG(ABS(actual - prediction)) for MAE. Compare against a last-value or seasonal baseline. The training summary is not a measure of forecast quality; use rolling-origin backtesting to assess multiple periods.

Last updated on