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.
Samples
Section titled “Samples”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 metricsSELECT '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.1806712012148852020-01-01 22:00:00+00 | 5.6045376217285832020-01-02 19:00:00+00 | 5.6742741023984522020-01-03 16:00:00+00 | 5.34902995864025052020-01-04 13:00:00+00 | 4.8193287987851122020-01-05 10:00:00+00 | 4.395462378271412020-01-06 07:00:00+00 | 4.3257258976015422020-01-07 04:00:00+00 | 4.6509700413597415Arguments
Section titled “Arguments”The syntax is:
asap_smooth( ts TIMESTAMPTZ, value DOUBLE PRECISION, resolution INT) RETURNS Timevector| Name | Type | Default | Required | Description |
|---|---|---|---|---|
| ts | TIMESTAMPTZ | - | ✔ | Timestamps for each data point. Rows where ts or value is NULL are skipped. |
| value | DOUBLE PRECISION | - | ✔ | The value at each timestamp. Rows where ts or value is NULL are skipped. |
| resolution | INT | - | ✔ | The approximate number of points to return. Must be greater than 0. |
Returns
Section titled “Returns”| Column | Type | Description |
|---|---|---|
| asap_smooth | Timevector | A 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. |