Exogenous Variables

Incorporate external predictors like temperature, promotions, or competitor prices into your forecasts using exogenous variable support.

Function	Description
`ts_forecast_exog`	Single series forecast with exogenous variables
`ts_forecast_exog_by`	Multi-series forecast with exogenous variables

Showing 2 of 2

Supported Models

Not all forecasting models support exogenous variables. The following models have exogenous variants:

Base Model	Exogenous Variant	Description
`ARIMA`	`ARIMAX`	ARIMA with exogenous regressors
`AutoARIMA`	`ARIMAX`	Auto-selected ARIMA with exogenous
`OptimizedTheta`	`ThetaX`	Theta method with exogenous
`MFLES`	`MFLESX`	MFLES with exogenous regressors

ts_forecast_exog

Single-series forecasting with external variables.

ts_forecast_exog(
    table_name VARCHAR,
    date_col IDENTIFIER,
    target_col IDENTIFIER,
    x_cols VARCHAR,
    future_table VARCHAR,
    model VARCHAR,
    horizon INTEGER,
    params MAP
) → TABLE

Parameters:

Parameter	Type	Description
`table_name`	VARCHAR	Source table name (quoted string)
`date_col`	IDENTIFIER	Date/timestamp column (unquoted)
`target_col`	IDENTIFIER	Target value column (unquoted)
`x_cols`	VARCHAR	Comma-separated list of exogenous column names
`future_table`	VARCHAR	Table containing future exogenous values
`model`	VARCHAR	Forecasting method (ARIMAX, ThetaX, MFLESX)
`horizon`	INTEGER	Number of periods to forecast
`params`	MAP	Model parameters (use `MAP{}` for defaults)

Returns: A table with columns:

ds - Forecast timestamp
forecast - Point forecast value
lower - Lower prediction interval bound
upper - Upper prediction interval bound

Example:

-- Create historical data with exogenous variables
CREATE TABLE sales AS
SELECT * FROM (VALUES
    ('2024-01-01'::DATE, 100.0, 20.0, 0),
    ('2024-01-02'::DATE, 120.0, 22.0, 1),
    ('2024-01-03'::DATE, 110.0, 19.0, 0),
    ('2024-01-04'::DATE, 130.0, 23.0, 1),
    ('2024-01-05'::DATE, 125.0, 21.0, 0),
    ('2024-01-06'::DATE, 140.0, 24.0, 1)
) AS t(date, amount, temperature, promotion);

-- Create future exogenous values
CREATE TABLE future_exog AS
SELECT * FROM (VALUES
    ('2024-01-07'::DATE, 22.0, 1),
    ('2024-01-08'::DATE, 20.0, 0),
    ('2024-01-09'::DATE, 21.0, 1)
) AS t(date, temperature, promotion);

-- Forecast with exogenous variables
SELECT * FROM ts_forecast_exog(
    'sales', date, amount,
    'temperature,promotion',
    'future_exog',
    'ARIMAX', 3, MAP{}
);

ts_forecast_exog_by

Multi-series forecasting with external variables, grouped by an identifier.

ts_forecast_exog_by(
    table_name VARCHAR,
    group_col IDENTIFIER,
    date_col IDENTIFIER,
    target_col IDENTIFIER,
    x_cols VARCHAR,
    future_table VARCHAR,
    model VARCHAR,
    horizon INTEGER,
    params MAP
) → TABLE

Parameters:

Parameter	Type	Description
`table_name`	VARCHAR	Source table name (quoted string)
`group_col`	IDENTIFIER	Column for grouping series (unquoted)
`date_col`	IDENTIFIER	Date/timestamp column (unquoted)
`target_col`	IDENTIFIER	Target value column (unquoted)
`x_cols`	VARCHAR	Comma-separated list of exogenous column names
`future_table`	VARCHAR	Table containing future exogenous values (must include group column)
`model`	VARCHAR	Forecasting method (ARIMAX, ThetaX, MFLESX)
`horizon`	INTEGER	Number of periods to forecast
`params`	MAP	Model parameters (use `MAP{}` for defaults)

Returns: A table with columns:

group_col - The series identifier
ds - Forecast timestamp
forecast - Point forecast value
lower - Lower prediction interval bound
upper - Upper prediction interval bound

Example:

-- Create historical data by product
CREATE TABLE sales_by_product AS
SELECT * FROM (VALUES
    ('A', '2024-01-01'::DATE, 100.0, 20.0),
    ('A', '2024-01-02'::DATE, 120.0, 22.0),
    ('A', '2024-01-03'::DATE, 110.0, 19.0),
    ('A', '2024-01-04'::DATE, 130.0, 23.0),
    ('B', '2024-01-01'::DATE, 200.0, 20.0),
    ('B', '2024-01-02'::DATE, 210.0, 22.0),
    ('B', '2024-01-03'::DATE, 205.0, 19.0),
    ('B', '2024-01-04'::DATE, 220.0, 23.0)
) AS t(product_id, date, amount, temperature);

-- Create future exogenous values per product
CREATE TABLE future_exog_by_product AS
SELECT * FROM (VALUES
    ('A', '2024-01-05'::DATE, 21.0),
    ('A', '2024-01-06'::DATE, 23.0),
    ('B', '2024-01-05'::DATE, 21.0),
    ('B', '2024-01-06'::DATE, 23.0)
) AS t(product_id, date, temperature);

-- Forecast each product with temperature as exogenous
SELECT * FROM ts_forecast_exog_by(
    'sales_by_product', product_id, date, amount,
    'temperature',
    'future_exog_by_product',
    'ARIMAX', 2, MAP{}
);

Requirements

Future Exogenous Table

The future_table must contain:

Date column with the same name as in the source table
All exogenous columns listed in x_cols
Group column (for ts_forecast_exog_by) with matching values
Exactly horizon rows per group for the forecast period

-- Correct: 3 rows for horizon=3
CREATE TABLE future_exog AS
SELECT * FROM (VALUES
    ('2024-01-07'::DATE, 22.0),
    ('2024-01-08'::DATE, 20.0),
    ('2024-01-09'::DATE, 21.0)
) AS t(date, temperature);

-- Incorrect: Only 2 rows for horizon=3
CREATE TABLE future_exog AS
SELECT * FROM (VALUES
    ('2024-01-07'::DATE, 22.0),
    ('2024-01-08'::DATE, 20.0)
) AS t(date, temperature);

Column Alignment

Exogenous column names must match exactly between historical and future tables:

-- Historical data
CREATE TABLE sales AS
SELECT date, amount, temp AS temperature, promo AS promotion
FROM ...;

-- Future data must use same column names
CREATE TABLE future AS
SELECT date, temperature, promotion  -- Must match exactly
FROM ...;

-- This will work
SELECT * FROM ts_forecast_exog(
    'sales', date, amount,
    'temperature,promotion',  -- Must exist in both tables
    'future',
    'ARIMAX', 3, MAP{}
);

Use Cases

Weather-Adjusted Demand

-- Forecast sales accounting for temperature
SELECT * FROM ts_forecast_exog(
    'daily_sales', date, units_sold,
    'temperature,humidity',
    'weather_forecast',
    'ARIMAX', 14, MAP{}
);

Promotion Planning

-- Forecast with planned promotions
SELECT * FROM ts_forecast_exog_by(
    'store_sales', store_id, date, revenue,
    'is_promotion,discount_pct',
    'promotion_calendar',
    'ARIMAX', 28, MAP{}
);

Economic Indicators

-- Forecast with macroeconomic variables
SELECT * FROM ts_forecast_exog(
    'monthly_revenue', month, revenue,
    'gdp_growth,unemployment_rate,consumer_confidence',
    'economic_forecast',
    'MFLESX', 12, MAP{}
);

Model Selection Guide

Model	Best For
`ARIMAX`	Complex patterns with external drivers, automatic order selection
`ThetaX`	Trending data with external influences
`MFLESX`	Multiple seasonal patterns plus exogenous factors

Comparison with Base Models

Aspect	Base Model	Exogenous Variant
Data requirement	Historical only	Historical + future exogenous
Captures external effects	No	Yes
Forecast accuracy	Good	Better (if variables are predictive)
Complexity	Simple	Requires future value preparation

Supported Models​

ts_forecast_exog​

ts_forecast_exog_by​

Requirements​

Future Exogenous Table​

Column Alignment​

Use Cases​

Weather-Adjusted Demand​

Promotion Planning​

Economic Indicators​

Model Selection Guide​

Comparison with Base Models​