DaZuiZui commented on issue #18532:
URL: https://github.com/apache/iotdb/issues/18532#issuecomment-5437228680
## Proposed functional definition
I suggest defining LTTB as a built-in Table Model table function, with
parameter conventions aligned with the existing M4 table function.
### 1. SQL syntax
~~~sql
SELECT *
FROM LTTB(
DATA => TABLE(<query>),
TIMECOL => DESCRIPTOR(<time_column>),
N => <target_count>
);
~~~
or:
~~~sql
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 execution mode must be selected:
* Target-count mode: specify N; N is mutually exclusive with SIZE, SLIDE,
and ORIGIN.
* Window/bucket mode: specify SIZE; SLIDE defaults to SIZE, and ORIGIN is
valid only for time-based windows.
### 2. Parameter semantics
* DATA: the input table, using the same table-argument semantics as M4.
* TIMECOL: a descriptor identifying exactly one input time column. The
column must have type TIMESTAMP. Input rows must be ordered by this column in
ascending order within each partition; the function should enforce or establish
this ordering before sampling.
* N: a positive integer target number of points for each partition and each
participant column. The minimum valid value is 3, because LTTB keeps the first
point, the last point, and at least one intermediate point.
* SIZE: the bucket/window size. A duration value selects time-window mode;
an integer value selects count-window mode.
* SLIDE: the window step, defaulting to SIZE. It is valid only with SIZE.
* ORIGIN: the origin of a time window. It is valid only with duration-based
SIZE, not with count windows.
No explicit COL parameter is needed. As with M4, participant columns should
be inferred from the input table: every supported numeric column other than the
time column and partition columns is processed independently. Partition columns
are preserved and define independent series.
### 3. Supported data types
For the first implementation, participant columns should support INT32,
INT64, FLOAT, and DOUBLE. The time column must be TIMESTAMP. Partition columns
may retain the data types already supported by Table Model grouping.
BOOLEAN, TEXT/STRING, binary types, and complex types should be rejected as
participant columns in the first version, because triangle-area calculation
requires an ordered numeric value. They can be added later if a well-defined
conversion policy is agreed.
### 4. Target-count mode
For each partition and participant column:
1. Build the ordered sequence of eligible (timestamp, value) points.
2. Ignore rows where that participant value is NULL; NULL rows must not be
converted to zero and must not affect averages or triangle areas.
3. If the number of eligible points is less than or equal to N, return all
eligible points without interpolation.
4. Otherwise apply the standard LTTB algorithm and return exactly N points.
5. Always preserve the first and last eligible points and return the
selected points in ascending timestamp order.
LTTB is applied independently to every participant column. Consequently,
different columns may select different timestamps. The target-count output
should follow the count-window shape used by M4:
~~~text
window_index, <partition_columns>,
<column_1>_time, <column_1>,
<column_2>_time, <column_2>, ...
~~~
For target-count mode, window_index is fixed to 0. Selected values are
aligned by output position; if columns have different numbers of eligible
points, shorter sequences are padded with NULL. Therefore each partition
produces at most N rows (and exactly N rows when every participant column has
more than N eligible points).
### 5. Window/bucket mode
Window construction must follow the same boundary, inclusiveness,
timestamp-origin, and count-window rules as M4.
For each bucket and participant column, select one representative point
using the LTTB triangle:
* A: the previously selected point (the anchor);
* B: each non-NULL candidate point in the current bucket;
* C: the average point of the next bucket (average timestamp and average
numeric value over eligible points).
Select the candidate with the largest triangle area. Ties should be resolved
deterministically, for example by the earliest timestamp. Empty buckets produce
no participant point. The implementation should define how the first bucket is
anchored (the first eligible point in the partition is the initial anchor) and
how the final bucket is handled when no next bucket exists (the last eligible
point should be retained).
The output schema follows M4:
* Time-window mode:
~~~text
window_start, window_end, <partition_columns>,
<column_1>_time, <column_1>,
<column_2>_time, <column_2>, ...
~~~
* Count-window mode:
~~~text
window_index, <partition_columns>,
<column_1>_time, <column_1>,
<column_2>_time, <column_2>, ...
~~~
With SLIDE < SIZE, overlapping windows are expected to follow M4's set
semantics. LTTB state must be computed per partition/window; results must not
depend on fragment-local input boundaries.
### 6. NULL and edge-case behavior
* NULL participant values are ignored independently per column.
* A partition with no eligible points for a participant column returns NULL
for that column.
* A partition with one eligible point returns that point; a partition with
two eligible points returns both points. This short-series behavior applies
even though N >= 3 is required for downsampling.
* Duplicate timestamps should be handled consistently with the rest of the
Table Model (prefer stable input order after sorting); implementations should
document this behavior.
* Numeric overflow in average/area calculations must be avoided by using a
sufficiently wide intermediate type, for example DOUBLE.
### 7. Validation errors
The function should reject at least:
* neither N nor SIZE is specified;
* both N and SIZE are specified;
* N < 3 or a non-positive/non-integer N;
* N combined with SLIDE or ORIGIN;
* SLIDE or ORIGIN without SIZE;
* ORIGIN with count-window mode;
* invalid or non-TIMESTAMP TIMECOL;
* zero/negative/invalid SIZE or SLIDE;
* unsupported participant data types.
Example:
~~~sql
SELECT *
FROM LTTB(
DATA => TABLE(sensor_data),
TIMECOL => DESCRIPTOR(time),
N => 500,
SIZE => 1m
);
~~~
This must fail because N and SIZE select mutually exclusive modes.
### 8. Execution and distributed semantics
The function should have set semantics, consistent with M4. In target-count
mode, bucket boundaries depend on the total number of eligible points, so the
implementation may need to buffer each partition, with memory accounting and
spill support where required. Window mode can use bounded state by retaining
the previous selected point plus current and next buckets.
LTTB is not generally mergeable. In distributed execution, all rows for a
partition must be gathered and ordered before sampling; fragment-local LTTB
results cannot simply be concatenated or merged into a globally correct result.
### 9. Examples
Target-count mode:
~~~sql
SELECT *
FROM LTTB(
DATA => TABLE(
SELECT time, temperature, pressure
FROM sensor_data
WHERE device_id = 'd1'
),
TIMECOL => DESCRIPTOR(time),
N => 500
);
~~~
Count-window mode:
~~~sql
SELECT *
FROM LTTB(
DATA => TABLE(SELECT time, temperature, pressure FROM sensor_data),
TIMECOL => DESCRIPTOR(time),
SIZE => 100,
SLIDE => 100
);
~~~
Time-window mode:
~~~sql
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 output for a known LTTB data set;
* preservation of first/last points;
* exactly N points when input has more than N eligible points;
* returning all points when input has no more than N points;
* fixed window_index = 0 in target-count mode;
* multiple participant columns selecting different timestamps;
* different NULL distributions and empty participant series;
* partitioned input;
* count-based and time-based SIZE;
* default and explicit SLIDE;
* ORIGIN alignment;
* overlapping windows;
* invalid parameter combinations and unsupported types;
* consistent standalone and distributed results.
### 11. Scope for the first version
The first version should keep the behavior deterministic and aligned with
M4, while explicitly documenting the final-bucket/short-series rules and the
treatment of overlapping windows. Follow-up work can add more participant types
or alternative output layouts if there is a concrete use case.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
