Hi all,
Based on the requirements in Apache IoTDB Issue #18532
<https://github.com/apache/iotdb/issues/18532>, I propose the following
functional definition for adding LTTB as a built-in Table Model table
function.
1. SQL syntax
Target-count mode:
SELECT *
FROM LTTB(
DATA => TABLE(<query>),
TIMECOL => DESCRIPTOR(<time_column>),
N => <target_count>
);
Window/bucket mode:
SELECT *
FROM LTTB(
DATA => TABLE(<query>),
TIMECOL => DESCRIPTOR(<time_column>),
SIZE => <window_size>,
SLIDE => <window_step>,
ORIGIN => <time_origin>
);
DATA and TIMECOL are required. Exactly one of N and SIZE must be specified.
- N selects target-count mode and is mutually exclusive with SIZE, SLIDE,
and ORIGIN.
- SIZE selects window/bucket mode.
- SLIDE defaults to SIZE.
- ORIGIN is valid only for time-based windows.
2. Parameter semantics
- DATA: input table, following the same table-argument semantics as M4.
- TIMECOL: descriptor identifying exactly one input time column. The
column must be of type TIMESTAMP.
- N: positive integer target point count for each partition and
participant column. The minimum value should be 3.
- SIZE: bucket size. A duration value selects time-window mode; an
integer selects count-window mode.
- SLIDE: window step, valid only with SIZE.
- ORIGIN: time-window origin, valid only with duration-based SIZE.
No explicit COL parameter is required. Participant columns should be
inferred automatically: every supported numeric column other than the time
and partition columns is processed independently.
3. Supported data types
For the initial implementation, participant columns should support:
- INT32
- INT64
- FLOAT
- DOUBLE
The time column must be TIMESTAMP. BOOLEAN, TEXT/STRING, binary, and
complex types should be rejected as participant columns initially, since
LTTB requires numeric values for triangle-area calculations.
4. Target-count mode
For each partition and participant column:
1. Sort points by timestamp in ascending order.
2. Ignore rows where the participant value is NULL.
3. If the number of eligible points is less than or equal to N, return
all eligible points.
4. Otherwise, apply the standard LTTB algorithm and return exactly N
points.
5. Preserve the first and last eligible points.
6. Return selected points in ascending timestamp order.
LTTB is applied independently to each participant column, so different
columns may select different timestamps.
The output schema should follow the count-window shape of M4:
window_index,
<partition_columns>,
<column_1>_time, <column_1>,
<column_2>_time, <column_2>, ...
For target-count mode, window_index is fixed to 0. Results are aligned by
output position, with NULL padding where participant columns produce
shorter sequences.
5. Window/bucket mode
Window construction should follow the same boundary, inclusiveness, origin,
and count-window rules as M4.
For each bucket and participant column, the LTTB triangle consists of:
- A: the previously selected point;
- B: each non-NULL candidate point in the current bucket;
- C: the average point of the next bucket.
The candidate producing the largest triangle area is selected. Ties should
be resolved deterministically, for example by choosing the earliest
timestamp.
The implementation should define:
- the initial anchor for the first bucket;
- the behavior of the final bucket when no next bucket exists;
- the behavior of empty buckets;
- the semantics of overlapping windows when SLIDE < SIZE.
Recommended behavior is to use the first eligible point as the initial
anchor, retain the last eligible point for the final bucket, and produce no
participant point for an empty bucket.
Output schemas should match M4:
Time-window mode:
window_start, window_end,
<partition_columns>,
<column_1>_time, <column_1>,
<column_2>_time, <column_2>, ...
Count-window mode:
window_index,
<partition_columns>,
<column_1>_time, <column_1>,
<column_2>_time, <column_2>, ...
6. NULL and edge-case behavior
- NULL values are ignored independently for each participant column.
- NULL values must not be converted to zero or included in averages and
area calculations.
- A participant column with no eligible points returns NULL.
- A series with one eligible point returns that point.
- A series with two eligible points returns both points.
- Duplicate timestamps should use stable ordering consistent with the
rest of the Table Model.
- Average and triangle-area calculations should use sufficiently wide
intermediate types, such as DOUBLE, to avoid overflow.
7. Validation
The function should reject at least the following cases:
- neither N nor SIZE is specified;
- both N and SIZE are specified;
- N < 3 or N is not an integer;
- N is combined with SLIDE or ORIGIN;
- SLIDE or ORIGIN is specified without SIZE;
- ORIGIN is used with count-window mode;
- invalid or non-TIMESTAMP TIMECOL;
- zero, negative, or invalid SIZE/SLIDE;
- unsupported participant data types.
Example of an invalid invocation:
SELECT *
FROM LTTB(
DATA => TABLE(sensor_data),
TIMECOL => DESCRIPTOR(time),
N => 500,
SIZE => 1m
);
This should fail because N and SIZE select mutually exclusive modes.
8. Execution and distributed semantics
Target-count mode requires the total number of eligible points in each
partition before bucket boundaries can be determined. Therefore, buffering
may be required, together with memory accounting and spill support where
appropriate.
Window mode can use bounded state by retaining the previous selected point
and the current and next buckets.
LTTB is generally not mergeable. In distributed execution, all rows
belonging to the same partition should be gathered and ordered before
sampling. Fragment-local LTTB results cannot simply be concatenated to
produce a globally correct result.
9. Examples
Target-count mode:
SELECT *
FROM LTTB(
DATA => TABLE(
SELECT time, temperature, pressure
FROM sensor_data
WHERE device_id = 'd1'
),
TIMECOL => DESCRIPTOR(time),
N => 500
);
Count-window mode:
SELECT *
FROM LTTB(
DATA => TABLE(
SELECT time, temperature, pressure
FROM sensor_data
),
TIMECOL => DESCRIPTOR(time),
SIZE => 100,
SLIDE => 100
);
Time-window mode:
SELECT *
FROM LTTB(
DATA => TABLE(
SELECT time, temperature, pressure
FROM sensor_data
),
TIMECOL => DESCRIPTOR(time),
SIZE => 1m,
SLIDE => 1m,
ORIGIN => TIMESTAMP '2026-01-01 00:00:00'
);
10. Suggested tests
Tests should cover:
- deterministic results for a known LTTB data set;
- preservation of the first and last points;
- exact target count when the input contains more than N points;
- returning all points when the input contains no more than N points;
- fixed window_index = 0 in target-count mode;
- multiple participant columns selecting different timestamps;
- different NULL distributions and empty series;
- partitioned input;
- count-based and time-based windows;
- default and explicit SLIDE;
- ORIGIN alignment;
- overlapping windows;
- invalid parameter combinations;
- unsupported data types;
- consistency between standalone and distributed execution.
The first version should prioritize deterministic behavior and alignment
with M4 semantics. In particular, the final-bucket behavior, short-series
handling, and overlapping-window semantics should be explicitly documented
before implementation.
Best regards,
Bryan Yang(DaZuiZui)
