Skip to content

asap_smooth()

Downsample a time series using the ASAP smoothing algorithm

Since 1.11.0

Downsample your data with the ASAP smoothing algorithm. This algorithm preserves the approximate shape and larger trends of the input data, while minimizing the local variance between points.

This example uses a table called metrics, with columns for date and reading. The columns contain measurements that have been accumulated over a large interval of time. This example takes that data and provides a smoothed representation of 8 points, but that still shows any anomalous readings:

SET TIME ZONE 'UTC';
CREATE TABLE metrics(date TIMESTAMPTZ, reading DOUBLE PRECISION);
INSERT INTO metrics
SELECT
'2020-1-1 UTC'::timestamptz + make_interval(hours=>foo),
(5 + 5 * sin(foo / 12.0 * PI()))
FROM generate_series(1,168) foo;
SELECT * FROM unnest(
(SELECT asap_smooth(date, reading, 8)
FROM metrics)
);
time | value
------------------------+---------------------
2020-01-01 01:00:00+00 | 5.180671201214885
2020-01-01 22:00:00+00 | 5.604537621728583
2020-01-02 19:00:00+00 | 5.674274102398452
2020-01-03 16:00:00+00 | 5.3490299586402505
2020-01-04 13:00:00+00 | 4.819328798785112
2020-01-05 10:00:00+00 | 4.39546237827141
2020-01-06 07:00:00+00 | 4.325725897601542
2020-01-07 04:00:00+00 | 4.6509700413597415

The syntax is:

asap_smooth(
ts TIMESTAMPTZ,
value DOUBLE PRECISION,
resolution INT
) RETURNS Timevector
NameTypeDefaultRequiredDescription
tsTIMESTAMPTZ-✔Timestamps for each data point. Rows where ts or value is NULL are skipped.
valueDOUBLE PRECISION-✔The value at each timestamp. Rows where ts or value is NULL are skipped.
resolutionINT-✔The approximate number of points to return. Must be greater than 0.
ColumnTypeDescription
asap_smoothTimevectorA timevector of smoothed values. Each point takes its timestamp from an input point. Unpack it with unnest. For more information, see the documentation on timevectors.