gp_lttb()
Downsample a time series using the Largest Triangle Three Buckets method, while preserving gaps in original data
Early access 1.11.0
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.
Samples
Section titled “Samples”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 metricsSELECT '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, valueFROM unnest(( SELECT toolkit_experimental.gp_lttb(date, reading, 8) FROM metrics))time | value-----------------------+-------------------2020-01-01 01:00:00+00 | 5.6526309611002572020-01-02 12:00:00+00 | 02020-01-03 23:00:00+00 | 5.6526309611002552020-01-05 01:00:00+00 | 5.6526309611002592020-01-05 13:00:00+00 | 9.9572243068690512020-01-06 12:00:00+00 | 02020-01-07 10:00:00+00 | 9.829629131445342020-01-08 00:00:00+00 | 5.000000000000004Arguments
Section titled “Arguments”The syntax is:
gp_lttb( ts TIMESTAMPTZ, value DOUBLE PRECISION, [gapsize INTERVAL,] resolution INT) RETURNS Timevector| Name | Type | Default | Required | Description |
|---|---|---|---|---|
| ts | TIMESTAMPTZ | - | ✔ | Timestamps for each data point. Must not be NULL. |
| value | DOUBLE PRECISION | - | ✔ | The value at each timestamp. Rows with a NULL value are skipped. |
| gapsize | INTERVAL | - | A gap between consecutive points larger than gapsize splits the input into segments that are downsampled separately. A gap equal to gapsize does not split. If omitted, gapsize is the time range of the input divided by resolution. | |
| resolution | INT | - | ✔ | The target number of points to return. Must be greater than 2. Every segment keeps at least its first and last point, so the result can have more points than resolution. |
Returns
Section titled “Returns”| Column | Type | Description |
|---|---|---|
| gp_lttb | Timevector | A timevector of the selected input points, sorted by time. Unpack it with unnest. For more information, see the documentation on timevectors. |