Versions:

Introduction

Downsample your data to visualize trends while preserving fewer data points. Downsampling replaces a set of values with a much smaller set that is highly representative of the original data. This is particularly useful for graphing applications.

warning

This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.

Function

asap_smooth
Downsample a time series using the ASAP smoothing algorithm
gp_lttb
ExperimentalDownsample a time series using the Largest Triangle Three Buckets method, while preserving gaps in original data
lttb
Downsample a time series using the Largest Triangle Three Buckets method
asap_smooth(
ts TIMESTAMPTZ,
value DOUBLE PRECISION,
resolution INT
) RETURNS Timevector

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.

Required arguments
NameTypeDescription
tsTIMESTAMPTZTimestamps for each data point
valueDOUBLE PRECISIONThe value at each timestamp
resolutionINTThe approximate number of points to return. Determines the horizontal resolution of the resulting graph.
Returns
ColumnTypeDescription
asap_smoothTimevectorAn object representing a series of values occurring at set intervals from a starting time. It can be unpacked with unnest. For more information, see the documentation on timevectors.
Examples

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 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 unnest(
(SELECT asap_smooth(date, reading, 8)
FROM metrics)
);
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
gp_lttb(
ts TIMESTAMPTZ,
value DOUBLE PRECISION,
resolution INT
[, gapsize INTERVAL]
) RETURNS Timevector

Downsample your data with the Largest Triangle Three Buckets algorithm, while preserving gaps in the underlying data. This method is a specialization of the LTTB algorithm.

Required arguments
NameTypeDescription
tsTIMESTAMPTZTimestamps for each data point
valueDOUBLE PRECISIONThe value at each timestamp
resolutionINTThe approximate number of points to return. Determines the horizontal resolution of the resulting graph.
Optional arguments
NameTypeDescription
gapsizeINTERVALMinimum gap size to divide input on
Returns
ColumnTypeDescription
gp_lttbTimevectorAn object representing a series of values occurring at set intervals from a starting time. It can be unpacked with unnest. For more information, see the documentation on timevectors.
Examples

This example uses a table with raw data generated as a sine wave, and removes a day from the middle of the data. You can use gap preserving LTTB to downsample the data while keeping the bounds of the missing region:

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 / 24.0 * PI()))
FROM generate_series(1,168) foo;
DELETE FROM metrics WHERE date BETWEEN '2020-1-4 UTC' AND '2020-1-5 UTC';
SELECT time, value
FROM unnest((
SELECT toolkit_experimental.gp_lttb(date, reading, 8)
FROM metrics))
time | value
-----------------------+-------------------
2020-01-01 01:00:00+00 | 5.652630961100257
2020-01-02 12:00:00+00 | 0
2020-01-03 23:00:00+00 | 5.652630961100255
2020-01-05 01:00:00+00 | 5.652630961100259
2020-01-05 13:00:00+00 | 9.957224306869051
2020-01-06 12:00:00+00 | 0
2020-01-07 10:00:00+00 | 9.82962913144534
2020-01-08 00:00:00+00 | 5.000000000000004
lttb(
ts TIMESTAMPTZ,
value DOUBLE PRECISION,
resolution INT
) RETURNS Timevector

Downsample your data with the Largest Triangle Three Buckets algorithm. This algorithm tries to retain visual similarity between the downsampled data and the original dataset.

Required arguments
NameTypeDescription
tsTIMESTAMPTZTimestamps for each data point
valueDOUBLE PRECISIONThe value at each timestamp
resolutionINTThe approximate number of points to return. Determines the horizontal resolution of the resulting graph.
Returns
ColumnTypeDescription
lttbTimevectorAn object representing a series of values occurring at set intervals from a starting time. It can be unpacked with unnest. For more information, see the documentation on timevectors.
Examples

This example uses a table with raw data generated as a sine wave. You can use LTTB to dramatically reduce the number of points while still capturing the peaks and valleys in the data:

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 / 24.0 * PI()))
FROM generate_series(1,168) foo;
SELECT time, value
FROM unnest((
SELECT lttb(date, reading, 8)
FROM metrics))
time | value
------------------------+---------------------
2020-01-01 01:00:00+00 | 5.652630961100257
2020-01-01 13:00:00+00 | 9.957224306869053
2020-01-02 11:00:00+00 | 0.04277569313094798
2020-01-03 11:00:00+00 | 9.957224306869051
2020-01-04 13:00:00+00 | 0.04277569313094709
2020-01-05 16:00:00+00 | 9.330127018922191
2020-01-06 20:00:00+00 | 2.4999999999999996
2020-01-08 00:00:00+00 | 5.000000000000004

Found an issue on this page?

Report an issue!

Keywords

Related Content