Largest-Triangle-Three-Buckets and related time-series downsampling aggregate functions.
Installing and Loading
INSTALL lttb FROM community;
LOAD lttb;
Example
SELECT lttb(x, y, 4)
FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);
About lttb
The lttb extension provides aggregate functions for time-series downsampling directly in SQL.
It includes exact Largest-Triangle-Three-Buckets sampling with lttb, a ClickHouse-compatible
alias largestTriangleThreeBuckets, a sorted-input fast path with lttb_sorted, selected
index output with lttb_indices, approximate MinMax preselection with minmax_lttb and
minmax_lttb_sorted, and per-bucket summaries with bucket_stats.
Inputs may use numeric, decimal, date, and timestamp types. The sampled output preserves
the input x and y types as STRUCT(x, y)[].
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| lttb | aggregate | Downsample points to n representative points using Largest-Triangle-Three-Buckets | Sorts input by x before sampling. | [SELECT lttb(x, y, 4) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
| largestTriangleThreeBuckets | aggregate | Alias for lttb with ClickHouse-compatible naming | Sorts input by x before sampling. | [SELECT largestTriangleThreeBuckets(x, y, 4) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
| lttb_sorted | aggregate | Downsample already-sorted points to n representative points | Caller must guarantee input is ordered by x. | [SELECT lttb_sorted(x, y, 4) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
| lttb_indices | aggregate | Return selected sorted-position indices from LTTB sampling | Indices are returned as BIGINT[]. | [SELECT lttb_indices(x, y, 4) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
| minmax_lttb | aggregate | Downsample using MinMax preselection followed by LTTB | Approximate method for large inputs. Pass NULL for the default minmax_ratio. | [SELECT minmax_lttb(x, y, 4, NULL) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
| minmax_lttb_sorted | aggregate | Downsample already-sorted points using MinMax preselection followed by LTTB | Approximate method for large inputs. Caller must guarantee input is ordered by x. | [SELECT minmax_lttb_sorted(x, y, 4, NULL) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
| bucket_stats | aggregate | Return per-bucket statistical summaries over y with x range boundaries | Returns STRUCT(bucket_start, bucket_end, count, min, max, mean, std, first, last)[]. | [SELECT bucket_stats(x, y, 3) FROM (VALUES (1, 0), (2, 10), (3, -5), (4, 8), (5, 0)) AS t(x, y);] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
This extension does not add any settings.