Background The table model already provides aggregate functions such as sum, avg, rate, increase, irate, and delta, and table functions such as tumble, hop, session, lowpass, highpass, m4, fft, and xcorr. These cover counters, windowed aggregation, and filtering, but they do not cover three common requirements: the area under a curve over time, the rate of change between adjacent samples of a signed field, and a bounded uniform sample of a table. The tree model has comparable capability through its derivative and sample UDTFs; the table model currently has no built-in equivalent. Typical scenarios are computing energy or volume from a signed power or flow signal, comparing the rate of change of two sensors in a dashboard, and drawing a fixed-size sample from a large table without loading all rows. Problem Description Users who need these quantities must either write a UDF or move the data out of the database. For signed signals, rate() and increase() are not applicable because they assume a monotonic counter. A plain LIMIT is not a uniform sample and does not preserve the per-partition semantics used elsewhere in the table model. The three functions below address these cases and follow the existing conventions: explicit PARTITION BY, required ORDER BY where order matters, and per-partition isolation. Proposed Implementation The implementation is in PR #19227: https://github.com/apache/iotdb/pull/19227. The behavior of the three functions is as follows. - integral(value [, unit [, time]]) is an aggregate function. It applies the trapezoidal rule to adjacent distinct timestamps within each group. Null values are skipped, fewer than two effective points yield 0, negative values are allowed, and only numeric input types are accepted. The optional unit defaults to 1s and accepts a duration literal or a positive integer number of ticks. The result is the signed area divided by unit, so integral(x, 10s) is one tenth of integral(x, 1s). The optional third argument names the actual time column. The function works with GROUP BY, date_bin windows, and COLUMNS regular expressions. When the planner proves that input is time-ascending, OrderedIntegralAccumulator computes the result in a single pass; otherwise NaiveIntegralAccumulator buffers and sorts the samples. Grouped variants exist for both, and partial and final aggregation are supported through IntegralFunctionIntermediateStateCodec. - derivative is a table function: DERIVATIVE(DATA => table PARTITION BY ... ORDER BY time, unit => 1s). For each adjacent pair of points it emits (v2 - v1) * unit / (t2 - t1), using the time of the second point as the output time. The output contains the partition columns, the time column, and one DOUBLE column per numeric input column, named derivative(column). ORDER BY is mandatory and must contain exactly one timestamp column. Time must be monotonic within each partition; rows with the same timestamp collapse and the later row wins, and a null value nulls only that output column for that pair. - sample is a table function: SAMPLE(DATA => table PARTITION BY ... ORDER BY time, N => k). It performs reservoir sampling over whole rows, per partition, with k between 1 and 65536. All columns are preserved, partition columns come first, and the sampled rows are emitted in input order so that ORDER BY still determines their relative order. Impact and Scope The change is limited to the table model. It adds built-in functions and does not change the tree model, existing aggregate functions, or existing table functions. No external UDF deployment is required. derivative streams one partition at a time, sample buffers at most N rows per partition, and integral buffers samples only when the input order cannot be proven. Integration tests cover basic aggregation and multiple groups, date_bin windows, nulls and insufficient points, negative values, COLUMNS expansion, and invalid arguments for integral, and empty tables, partitions, duplicate timestamps, null timestamps, all numeric types, and invalid arguments for sample and derivative. Known Limitation and Open Question In a plan that is split across data regions, integral() currently carries every raw sample in its intermediate state, whose size is bounded by max_tsblock_size_in_bytes. A single group with more than about 8,191 points in one data region therefore fails with error 701. A constant-size mergeable state, carrying the first point, the last point, and the partial area, would avoid this. I would like to hear whether reviewers prefer to address this in PR #19227 or in a follow-up. Comments on the function semantics are also welcome. --- 背景 表模型已经提供 sum、avg、rate、increase、irate、delta 等聚合函数，以及 tumble、hop、session、lowpass、highpass、m4、fft、xcorr 等表函数。它们覆盖了计数器、窗口聚合和过滤，但没有覆盖三类常见需求：一段时间内曲线下的面积、有符号字段相邻采样点之间的变化率，以及从一张表中取出大小固定且均匀的样本。树模型通过 derivative 和 sample UDTF 已经具备相近能力；表模型目前没有内置的对应实现。典型场景包括：从有符号的功率或流量信号计算电量或流量体积；在仪表盘中比较两个传感器的变化率；在不加载全部行的情况下从大表抽取固定大小的样本。 问题描述 需要上述结果的用户，要么自己写 UDF，要么把数据搬到数据库之外处理。对于有符号信号，rate() 和 increase() 不适用，因为它们假设输入是单调递增的计数器。普通的 LIMIT 不是均匀采样，也不保留表模型其他位置使用的按分区语义。下面三个函数覆盖这些情况，并沿用现有约定：显式 PARTITION BY、顺序相关的场景必须写 ORDER BY、以及按分区隔离。 实现方案 实现位于 PR #19227：https://github.com/apache/iotdb/pull/19227。三个函数的行为如下。 - integral(value [, unit [, time]]) 是聚合函数。它在每个分组内，对相邻且时间戳不同的点应用梯形法则。空值会被跳过，有效点少于两个时结果为 0，允许负值，只接受数值类型的输入。可选的 unit 默认是 1s，接受时长字面量或正整数个 tick。结果是带符号面积除以 unit，因此 integral(x, 10s) 是 integral(x, 1s) 的十分之一。可选的第三个参数用于指定实际的时间列。该函数支持 GROUP BY、date_bin 窗口和 COLUMNS 正则。当计划器能证明输入按时间升序时，OrderedIntegralAccumulator 单遍计算；否则 NaiveIntegralAccumulator 缓存样本后排序。两种实现都有对应的分组版本，partial 和 final 聚合通过 IntegralFunctionIntermediateStateCodec 支持。 - derivative 是表函数：DERIVATIVE(DATA => table PARTITION BY ... ORDER BY time, unit => 1s)。对每一对相邻点，它输出 (v2 - v1) * unit / (t2 - t1)，输出时间取这对点中第二个点的时间。输出包含分区列、时间列，以及每个数值输入列对应的一列 DOUBLE，列名为 derivative(列名)。ORDER BY 必填，且必须恰好包含一个时间戳列。每个分区内的时间必须单调；时间戳相同的行会合并，后出现的行生效；某一侧为空值时，只会让该对点对应的这一列输出为空。 - sample 是表函数：SAMPLE(DATA => table PARTITION BY ... ORDER BY time, N => k)。它按分区对整行做蓄水池采样，k 的取值是 1 到 65536。所有列都会保留，分区列排在最前面，采样行按输入顺序输出，因此 ORDER BY 仍然决定它们的相对顺序。
Best wishes, Yaobin Chen
