[Feature request] Add LTTB downsampling as a built-in table function for the Table Model

Search before asking

• I searched in the issues and found nothing similar.

Motivation

Motivation

Large time-series datasets usually need to be downsampled before visualization. Largest-Triangle-Three-Buckets (LTTB) can reduce the number of returned points while preserving the visual characteristics of the original series, including significant peaks and valleys.

IoTDB currently does not provide a native LTTB implementation for the Table Model. Implementing LTTB as a built-in table function would allow users to downsample data directly in SQL without transferring the full dataset to the client.

This feature is specifically for the Table Model and is unrelated to the sample UDF implementations in the Tree Model library-udf module.

References:

• Downsampling Time Series for Visual Representation
• Reference LTTB implementation

Proposed solution

Add a built-in Table Model table function named LTTB.

Its table argument and window-related parameters should follow the conventions already used by the built-in M4 table function.

The function should support two mutually exclusive modes:

1. Target-count mode, selected by N
2. Window/bucket mode, selected by SIZE

Exactly one of N and SIZE must be specified.

Parameters

• DATA

  The input table, using the same table-argument semantics as M4.

• TIMECOL

  A descriptor identifying the input time column.

• N

  A positive integer specifying the target number of sampled points for each partition and each participant column.

  N is mutually exclusive with SIZE, SLIDE, and ORIGIN.

  The minimum valid value should be 3, because LTTB preserves the first point, the last point, and at least one intermediate point.

• SIZE

  Defines the bucket size, using the same conventions as M4:

  • A duration value selects time-window mode.
  • An integer value selects count-window mode.

  SIZE is mutually exclusive with N.

• SLIDE

  Defines the window step and defaults to SIZE.

  It is valid only when SIZE is specified and is mutually exclusive with N.

• ORIGIN

  Defines the time-window origin.

  It is valid only for time-window mode and is mutually exclusive with N.

No COL parameter is required. As with M4, participant columns should be determined from the input table automatically. Each supported numeric column other than the time column and partition columns should be processed independently.

Target-count mode

Example:

SELECT *
FROM LTTB(
  DATA => TABLE(
    SELECT time, temperature, pressure
    FROM sensor_data
  ),
  TIMECOL => DESCRIPTOR(time),
  N => 500
);

For each partition and participant column:

1. Construct an ordered sequence of (time, value) points.
2. Ignore rows where the participant column is NULL.
3. If the number of eligible points is less than or equal to N, return all eligible points.
4. Otherwise, apply LTTB and return exactly N points.
5. Preserve the first and last eligible points.
6. Return sampled points in ascending time order.

Different participant columns may select different timestamps because LTTB is applied independently to each column.

The output should use window_index, consistent with the count-window output of M4:

window_index,
<partition columns>,
<column_1>_time,
<column_1>,
<column_2>_time,
<column_2>,
...

The selected points of each participant column are aligned by their output position. If participant columns produce different numbers of points, for example because of different NULL distributions, the shorter sequences should be padded with NULL.

Therefore, each partition produces at most N output rows.

Window/bucket mode

Example using count-based buckets:

SELECT *
FROM LTTB(
  DATA => TABLE(
    SELECT time, temperature, pressure
    FROM sensor_data
  ),
  TIMECOL => DESCRIPTOR(time),
  SIZE => 100,
  SLIDE => 100
);

Example using time-based buckets:

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'
);

The window construction rules should be consistent with M4.

For each participant column, LTTB should use:

• The previously selected point
• Candidate points in the current bucket
• The average point of the next bucket

The candidate that forms the largest triangle area should be selected.

The time-window output schema should follow M4:

window_start,
window_end,
<partition columns>,
<column_1>_time,
<column_1>,
<column_2>_time,
<column_2>,
...

The count-window output schema should also follow M4:

window_index,
<partition columns>,
<column_1>_time,
<column_1>,
<column_2>_time,
<column_2>,
...

Validation

The function should reject at least the following cases:

• Neither N nor SIZE is specified.
• Both N and SIZE are specified.
• N is used together with SLIDE or ORIGIN.
• N is less than 3.
• SLIDE or ORIGIN is specified without SIZE.
• ORIGIN is specified for count-window mode.
• TIMECOL does not identify a valid time column.
• A participant column has an unsupported data type.

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 different execution modes and are mutually exclusive.

Implementation considerations

Target-count mode needs the total number of eligible points before the LTTB bucket boundaries can be determined. Its implementation may therefore require buffering the input for each partition and participant column, with appropriate memory accounting and spill handling where necessary.

Window/bucket mode may be implemented with bounded state by retaining the previous selected point and the current and next buckets.

The function should have set semantics, consistent with M4. In distributed execution, LTTB must be evaluated only after all rows belonging to the same partition have been gathered and ordered. Fragment-local sampling results cannot generally be merged into a globally correct LTTB result.

Suggested tests

Tests should cover:

• A known LTTB example with deterministic expected points.
• Preservation of the first and last points.
• Returning exactly N points when the eligible input contains more than N points.
• Returning all points when the eligible input contains no more than N points.
• window_index output in target-count mode.
• Multiple participant columns selecting different timestamps.
• Participant columns with different NULL distributions.
• Partitioned input.
• Count-based and time-based SIZE.
• Default and explicit SLIDE.
• Time-based ORIGIN.
• Invalid parameter combinations.
• Consistent results between standalone and distributed execution.

Alternatives considered

• Perform LTTB downsampling on the client after querying all raw data. This requires transferring significantly more data and duplicates the implementation across clients.
• Use M4 for visualization downsampling. M4 and LTTB use different selection strategies and may serve different visualization requirements.
• Use a Tree Model UDF. This does not provide a native Table Model interface or integration with Table Model planning and execution.

Solution

No response

Alternatives

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!