Changepoint Detection

Find structural breaks and regime shifts in your time series data using Bayesian Online Changepoint Detection.

---

anofox_fcst_ts_detect_changepoints

Detect structural breaks in a single time series.

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Source table
date_col	VARCHAR	Yes	-	Date column
value_col	VARCHAR	Yes	-	Value column
params	MAP	No	MAP	Configuration

Options MAP:

Option	Type	Default	Description
hazard_lambda	INTEGER	250	Expected spacing between changes

Output

Field	Type	Description
date	DATE	Date of observation
value	DOUBLE	Original value
is_changepoint	BOOLEAN	True if structural break detected
changepoint_probability	DOUBLE	Confidence (0-1)

Example

SELECT
    date,
    value,
    is_changepoint,
    changepoint_probability
FROM anofox_fcst_ts_detect_changepoints(
    'sales_data',
    date,
    sales,
    MAP{'hazard_lambda': 250}
);

Parameter Tuning

hazard_lambda	Sensitivity	Use Case
50	High	Detect frequent changes
250	Medium	General purpose (default)
500	Low	Only major structural breaks

Use when:

• Data has regime shifts
• Marketing campaign launches
• Policy changes
• Price changes

---

anofox_fcst_ts_detect_changepoints_by

Multi-series changepoint detection with parallelized GROUP BY processing.

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Source table
group_col	VARCHAR	Yes	-	Group column
date_col	VARCHAR	Yes	-	Date column
value_col	VARCHAR	Yes	-	Value column
params	MAP	No	MAP	Configuration

Output

Same as detect_changepoints plus the group column.

Example

SELECT
    product_id,
    date,
    is_changepoint,
    changepoint_probability
FROM anofox_fcst_ts_detect_changepoints_by(
    'sales_by_product',
    product_id,
    date,
    sales,
    MAP{'hazard_lambda': 250}
)
WHERE is_changepoint = true;

---

anofox_fcst_ts_detect_changepoints_agg

Aggregate function for custom grouping scenarios with 2+ group columns.

Parameters

Parameter	Type	Required	Default	Description
date_col	DATE/TIMESTAMP	Yes	-	Date values
value_col	DOUBLE	Yes	-	Value column
params	MAP	No	MAP	Configuration

Output

Returns LIST<STRUCT> with changepoint detection results per group.

Example

SELECT
    region,
    product_category,
    anofox_fcst_ts_detect_changepoints_agg(
        date,
        sales,
        MAP{'hazard_lambda': 250}
    ) as changepoints
FROM sales_data
GROUP BY region, product_category;

---

Handling Changepoints in Forecasting

When changepoints are detected, you can use them to improve forecast accuracy.

Option 1: Filter to Recent Regime

-- Find last changepoint
WITH last_change AS (
    SELECT MAX(date) as change_date
    FROM anofox_fcst_ts_detect_changepoints('sales_data', date, sales, MAP{})
    WHERE is_changepoint = true
)
-- Forecast using only data after last changepoint
SELECT * FROM anofox_fcst_ts_forecast(
    (SELECT * FROM sales_data WHERE date > (SELECT change_date FROM last_change)),
    date,
    sales,
    'AutoETS',
    30,
    MAP{}
);

Option 2: Segment and Forecast by Regime

-- Identify regimes
WITH changes AS (
    SELECT date
    FROM anofox_fcst_ts_detect_changepoints('sales_data', date, sales, MAP{})
    WHERE is_changepoint = true
),
regimes AS (
    SELECT
        s.*,
        SUM(CASE WHEN s.date IN (SELECT date FROM changes) THEN 1 ELSE 0 END)
            OVER (ORDER BY s.date) as regime
    FROM sales_data s
)
-- Forecast each regime separately
SELECT regime, *
FROM anofox_fcst_ts_forecast_by(
    (SELECT * FROM regimes),
    regime,
    date,
    sales,
    'AutoETS',
    30,
    MAP{}
);

---

Complete Changepoint Analysis

-- Comprehensive changepoint analysis
WITH changepoints AS (
    SELECT
        date,
        value,
        is_changepoint,
        changepoint_probability
    FROM anofox_fcst_ts_detect_changepoints(
        'sales_data',
        date,
        sales,
        MAP{'hazard_lambda': 250}
    )
)
SELECT
    'Total Changepoints' as metric,
    COUNT(*)::VARCHAR as value
FROM changepoints
WHERE is_changepoint = true
UNION ALL
SELECT
    'First Changepoint',
    MIN(date)::VARCHAR
FROM changepoints
WHERE is_changepoint = true
UNION ALL
SELECT
    'Last Changepoint',
    MAX(date)::VARCHAR
FROM changepoints
WHERE is_changepoint = true
UNION ALL
SELECT
    'Avg Probability',
    ROUND(AVG(changepoint_probability), 3)::VARCHAR
FROM changepoints
WHERE is_changepoint = true;

---