asap_smooth()

The ASAP smoothing algorithm 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

Name Type Description
ts TIMESTAMPTZ Column of timestamps corresponding to the values to aggregate
value DOUBLE PRECISION Column to aggregate
resolution INT Approximate number of points to return. Intended to represent the horizontal resolution in which the aggregate is graphed.

Returns

Column Type Description
timevector Timevector An object representing a series of values occurring at set intervals from a starting time. It can be unpacked with 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:

2. SET TIME ZONE 'UTC';

4. CREATE TABLE metrics(date TIMESTAMPTZ, reading DOUBLE PRECISION);

6. INSERT INTO metrics

8. SELECT

10. '2020-1-1 UTC'::timestamptz + make_interval(hours=>foo),

12. (5 + 5 * sin(foo / 12.0 * PI()))

14. FROM generate_series(1,168) foo;

16. SELECT * FROM unnest(

18. (SELECT asap_smooth(date, reading, 8)

20. FROM metrics));

The output for this query:

2. time          |        value

4. ------------------------+---------------------

6. 2020-01-01 01:00:00+00 | 5.3664814565722665

8. 2020-01-01 21:00:00+00 |  5.949469264090644

10. 2020-01-02 17:00:00+00 |  5.582987807518377

12. 2020-01-03 13:00:00+00 |  4.633518543427733

14. 2020-01-04 09:00:00+00 |  4.050530735909357

16. 2020-01-05 05:00:00+00 |  4.417012192481623

18. 2020-01-06 01:00:00+00 |  5.366481456572268

20. 2020-01-06 21:00:00+00 |  5.949469264090643