JackieTien97 opened a new issue, #18532: URL: https://github.com/apache/iotdb/issues/18532
### Search before asking - [x] I searched in the [issues](https://github.com/apache/iotdb/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](https://hdl.handle.net/1946/15343) - [Reference LTTB implementation](https://github.com/sveinn-steinarsson/flot-downsample) ## 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: ```sql 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`: ```text 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: ```sql SELECT * FROM LTTB( DATA => TABLE( SELECT time, temperature, pressure FROM sensor_data ), TIMECOL => DESCRIPTOR(time), SIZE => 100, SLIDE => 100 ); ``` Example using time-based buckets: ```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' ); ``` 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`: ```text 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`: ```text 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: ```sql 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! -- 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]
