Find where your time series change, in SQL.

cpt is a DuckDB extension for offline change point detection. It finds the points where the mean, the variance, a count rate or the median of a series shifts, with the methods statisticians use, inside DuckDB and next to your data.

View on GitHub Examples

Open source under MIT, written by Örjan Lundberg. Installs from the DuckDB community repository.

DuckDB 1.5.6 and 2.0

INSTALL cpt FROM community;
LOAD cpt;

-- where does each sensor’s level shift?
SELECT sensor_id,
       cpt_pelt(list(value ORDER BY ts), 'l2') AS bkps
FROM readings
GROUP BY sensor_id;
-- [250, 500, 750, 1000]

WHAT YOU GET

The methods from the textbooks, as SQL functions.

Four methods

Exact PELT, binary segmentation for long series with few changes, dynamic programming for a known number of change points, and CROPS to see every answer across a range of penalties.

Four costs

Changes in the mean, in the mean and variance, in the rate of counts, or in the median, which stays robust when the data has outliers.

Penalties you know

BIC by default, MBIC and AIC as R’s changepoint package defines them, or any number you choose.

Checked against ruptures and R

Results agree with Python’s ruptures on random series and with R’s changepoint on its reference datasets. With a minimum segment length, PELT here stays exact.

A million points in under a second

PELT with the l2 cost takes 0.84 s on 1,000,000 points on a laptop. Binary segmentation stays fast when a long series changes only a few times.

One binary for 1.5 and 2.0

Written in C against DuckDB’s stable C API, so the same build loads in DuckDB 1.5.6 and later. The community repository serves it for DuckDB 2.0.

IN SQL

Answers you can join back to your rows.

Each function takes one ordered series per row and returns where each segment ends. In the examples, series holds one list(value ORDER BY ts) per sensor, in the column xs.

Mean and variance, per group

BIC penalty by default, with at least 5 points in a segment.

SELECT sensor_id,
       cpt_pelt(list(value ORDER BY ts),
                'normal', NULL, 5)
FROM readings
GROUP BY sensor_id;

Statistics for each segment

Start, end, mean, variance and length of every segment found.

SELECT sensor_id,
       unnest(cpt_segment_stats(
         xs, cpt_pelt(xs, 'l2')))
FROM series;

A known number of changes

The optimal split into exactly four segments.

SELECT cpt_dynp(xs, 3, 'l2') FROM series;

Every answer across penalties

CROPS lists each distinct segmentation PELT gives in a penalty range.

SELECT unnest(cpt_crops(xs, 'l2',
         2 * ln(len(xs)), 50 * ln(len(xs))))
FROM series;

Find the change points in your data.

The README covers every function, cost and penalty, with more examples. Questions and ideas are welcome as issues.

View on GitHub Write to us