Versions:

asap_smooth()

The ASAP smoothing alogrithm is designed to create human-readable graphs that preserve the rough shape and larger trends of the input data, while minimizing the local variance between points. The asap_smooth hyperfunction provides an implementation of this algorithm that takes (timestamptz, double precision) data and returns an ASAP smoothed timevector line.

Required arguments

NameTypeDescription
tsTIMESTAMPTZColumn of timestamps corresponding to the values to aggregate
valueDOUBLE PRECISIONColumn to aggregate
resolutionINTApproximate number of points to return. Intended to represent the horizontal resolution in which the aggregate is graphed.

Returns

ColumnTypeDescription
normalizedtimevectorNormalizedTimevectorAn object representing a series of values occurring at set intervals from a starting time. It can be unpacked via unnest.

Sample usage

This example uses a table called metrics, with columns for date and reading that contain measurements that have been accumulated over a large interval of time. This example takes that data and provides a smoothed representation of approximately 10 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 toolkit_experimental.unnest(
    (SELECT toolkit_experimental.asap_smooth(date, reading, 8)
     FROM metrics));

The output for this query:

time          |        value
------------------------+---------------------
 2020-01-01 01:00:00+00 | 5.3664814565722665
 2020-01-01 21:00:00+00 |  5.949469264090644
 2020-01-02 17:00:00+00 |  5.582987807518377
 2020-01-03 13:00:00+00 |  4.633518543427733
 2020-01-04 09:00:00+00 |  4.050530735909357
 2020-01-05 05:00:00+00 |  4.417012192481623
 2020-01-06 01:00:00+00 |  5.366481456572268
 2020-01-06 21:00:00+00 |  5.949469264090643

Found an issue on this page?

Report an issue!

Related Content