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

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
timevectorTimevectorAn 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