Exogenous Variables

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

Function	Description
ts_forecast_exog_by	Multi-series forecast with exogenous variables

Showing 1 of 1

---

Supported Models

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

Base Model	Exogenous Variant	Description
ARIMA / AutoARIMA	ARIMAX	ARIMA with exogenous regressors
OptimizedTheta	ThetaX	Theta method with exogenous
MFLES	MFLESX	MFLES with exogenous regressors

---

ts_forecast_exog_by

Multi-series forecasting with external variables.

ts_forecast_exog_by(
    table_name VARCHAR,
    group_col COLUMN,
    date_col COLUMN,
    target_col COLUMN,
    x_cols VARCHAR[],
    future_table VARCHAR,
    future_date_col COLUMN,
    future_x_cols VARCHAR[],
    model VARCHAR,
    horizon INTEGER,
    params MAP,
    frequency VARCHAR
) → TABLE

Parameters:

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table name (quoted string)
group_col	COLUMN	Yes	Column for grouping series (unquoted)
date_col	COLUMN	Yes	Date/timestamp column (unquoted)
target_col	COLUMN	Yes	Target value column (unquoted)
x_cols	VARCHAR[]	Yes	Exogenous column names (array)
future_table	VARCHAR	Yes	Table containing future exogenous values
future_date_col	COLUMN	Yes	Date column in future table (unquoted)
future_x_cols	VARCHAR[]	Yes	Exogenous column names in future table
model	VARCHAR	Yes	Forecasting method ('ARIMAX', 'ThetaX', 'MFLESX')
horizon	INTEGER	Yes	Number of periods to forecast
params	MAP	No	Model parameters (use MAP{} for defaults)
frequency	VARCHAR	Yes	Data frequency ('1d', '1w', etc.)

Returns:

Column	Type	Description
group_col	(input)	Series identifier
date_col	TIMESTAMP	Forecast timestamp
yhat	DOUBLE	Point forecast
yhat_lower	DOUBLE	Lower prediction interval
yhat_upper	DOUBLE	Upper prediction interval

Example:

-- Create historical data with exogenous variables
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_weather 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_weather', date, ['temperature'],
    'ARIMAX', 2, MAP{}, '1d'
);

---

Requirements

Future Exogenous Table

The future_table must contain:

1. Date column matching future_date_col
2. All exogenous columns listed in future_x_cols
3. Group column with matching values (for grouped forecasts)
4. Exactly horizon rows per group for the forecast period

Column Alignment

Exogenous column names in the future table must match the historical table:

-- Historical: x_cols = ['temperature']
-- Future: future_x_cols = ['temperature']
-- Column names must match

SELECT * FROM ts_forecast_exog_by(
    'sales', product_id, date, amount,
    ['temperature', 'promotion'],
    'future_data', date, ['temperature', 'promotion'],
    'ARIMAX', 7, MAP{}, '1d'
);

---

Use Cases

Weather-Adjusted Demand

SELECT * FROM ts_forecast_exog_by(
    'store_sales', store_id, date, units_sold,
    ['temperature', 'humidity'],
    'weather_forecast', date, ['temperature', 'humidity'],
    'ARIMAX', 14, MAP{}, '1d'
);

Promotion Planning

SELECT * FROM ts_forecast_exog_by(
    'store_sales', store_id, date, revenue,
    ['is_promotion', 'discount_pct'],
    'promotion_calendar', date, ['is_promotion', 'discount_pct'],
    'ARIMAX', 28, MAP{}, '1d'
);

Economic Indicators

SELECT * FROM ts_forecast_exog_by(
    'monthly_revenue', region, month, revenue,
    ['gdp_growth', 'unemployment_rate'],
    'economic_forecast', month, ['gdp_growth', 'unemployment_rate'],
    'MFLESX', 12, MAP{}, '1mo'
);

---

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