Hi xinqi, I've reviewed the requirements specification, and looks good to me, you can proceed.
On Fri, Jul 17, 2026 at 10:59 AM Xinqi Zhao <[email protected]> wrote: > Subject: Re: [DISCUSS] Add Prometheus-like rate/irate/increase/delta > aggregate functions for table model > > Hi Yuan and all, > > Thank you for the detailed feedback. I have revised the proposal > accordingly. The updated proposal is available here: > > https://github.com/apache/iotdb/issues/17976 > > The main updates are summarized below. > > Function signatures > > The functions use the following signatures: > > rate(value_col, time_col, window_start, window_end) > > increase(value_col, time_col, window_start, window_end) > > irate(value_col, time_col) > > delta(value_col, time_col, window_start, window_end) > > Since irate() only uses the last two valid samples and does not perform > boundary extrapolation, window_start and window_end have been removed from > its signature. > > All functions return DOUBLE. The value column supports INT32, INT64, > FLOAT, and DOUBLE. Time-related arguments support both TIMESTAMP and INT64. > INT64 values are interpreted according to the current timestamp_precision, > while the results of rate() and irate() are always expressed per second. > > Function semantics > > The functions follow the corresponding Prometheus algorithms for > counter-reset handling and boundary extrapolation, while retaining IoTDB's > [window_start, window_end) window semantics. > > rate() uses all valid samples, compensates for counter resets, performs > boundary extrapolation, and returns the average per-second growth rate. > > increase() applies the same reset compensation and extrapolation rules as > rate(), but returns the total extrapolated increase. > > irate() uses only the last two valid samples, handles a reset between > those samples, performs no extrapolation, and returns the instantaneous > per-second rate. > > delta() uses all valid samples and performs boundary extrapolation, but > treats decreases as normal gauge changes rather than counter resets. > Negative input values and negative results are allowed. > > For boundary extrapolation, the proposal now explicitly defines the > average sample interval, the 1.1-times extrapolation threshold, the > half-interval limit when a boundary is too far away, and the counter > zero-point protection used by rate() and increase(). The zero-point > protection is not applied to delta(). > > Validation and exceptional cases > > The validation rules are now documented separately for each function. > > In particular: > > NULL value_col rows are ignored. > > Fewer than two valid samples produce NULL. > > Duplicate time_col values within an aggregation group cause an exception > for all four functions. > > Non-finite values such as NaN and Infinity are rejected. > > rate(), increase(), and irate() require non-negative counter values, while > delta() allows negative gauge values. > > window_start must be less than window_end. > > All valid samples must satisfy window_start <= time_col < window_end. > > The window boundaries must remain consistent within the same aggregation > group. > > When value_col is not NULL, its required time and window arguments must > not be NULL. > > Counter-reset detection is performed between adjacent valid samples after > ordering them by time_col. NULL samples do not update the previous counter > value. > > Ordering and complexity > > The complexity analysis now distinguishes two execution paths: > > When the planner can guarantee ascending time_col order within a group, an > ordered implementation performs one sequential scan with O(n) time and O(1) > space. > > Otherwise, a naive implementation buffers and sorts the samples before > calculation, requiring O(n log n) time and O(n) space. > > SQL usage and examples > > The proposal includes examples for date_bin(), date_bin_gapfill(), HOP(), > and TUMBLE() windows. > > I also added worked examples and expected results covering counter resets, > full and limited boundary extrapolation, zero-point protection, reset-aware > zero-point protection, unordered input, duplicate timestamps, insufficient > samples, multiple resets, empty gap-fill windows, timestamp precisions of > ms/us/ns, invalid argument types, invalid boundaries, and other edge cases. > > The user manual will also be updated under “Table Model -> SQL Manual > -> Functions and Operators” to document these four functions. > > Please let me know if there are any remaining concerns or suggestions. I > would appreciate another review of the updated proposal. > > Best regards, > > Xinqi Zhao > > > > > 原始邮件 > > > 发件人：Yuan Tian <[email protected]> > 发件时间：2026年7月10日 14:55 > 收件人：dev <[email protected]> > 主题：Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate > functions for table model > > > > Hi Xinqi and all, > > > Thanks for starting this discussion. I reviewed the proposal for > > https://github.com/apache/iotdb/issues/17976. I think the following points > should be clarified before implementation > <https://github.com/apache/iotdb/issues/17976. I think the following pointsshould be clarified before implementation> > . > > 1. Function signatures and parameter validation > > > The proposal currently gives all four functions the same signature: > > > rate/increase/irate/delta(value_col, time_col, window_start, window_end) > > > Please document each function’s signature, accepted types, and validation > > rules in its own subsection, even where some rules are duplicated. > > > For rate, increase, and delta, the window boundaries are needed for > > boundary extrapolation, especially with HOP windows. However, irate only > > uses the last two valid samples and performs no boundary extrapolation. I > therefore suggest using: > > irate(value_col, time_col) > > > If window_start and window_end are still required for irate, their semantic > purpose should be explained explicitly. > > > The time-related arguments—time_col, window_start, and window_end—should > accept both TIMESTAMP and INT64. > > 2. Duplicate timestamps > > > Any duplicate time_col value within a group should cause an exception for > all four functions. > > > For rate in particular, different values at the same timestamp have no > > well-defined order, and their order can affect counter-reset detection. > > Therefore, duplicate timestamps should be treated as invalid input rather > than as a case that returns NULL. > > > The current rule stating that NULL is returned when the first and last > > valid samples have the same timestamp should be revised accordingly. > > Returning NULL when there are fewer than two valid samples can remain > unchanged. > > 3. Worked examples > > > Please add concrete examples with input samples, window boundaries, SQL, > > and expected results for the important algorithm branches: > > > - For rate, add one counter-reset compensation example and three > boundary-extrapolation examples. > > - For increase, add examples for every documented normal and exceptional > case, analogous to those for rate. > > - For irate, add an example showing reset handling between the last two > valid samples. > > - For delta, add examples covering its rules, including boundary > extrapolation without counter-reset compensation. > > > These examples are important because describing the behavior only as > > “Prometheus-compatible” is not precise enough for implementation and > testing. > > 4. Complexity and input ordering > > > The stated O(n) time and O(1) space complexity is valid only if samples > > within each group are guaranteed to be ordered by time_col. > > > Without that guarantee, the implementation must retain and sort the > > samples, resulting in O(n log n) time and O(n) space. Please either > > document the ordering guarantee or update the complexity analysis > accordingly. > > > One minor wording correction: in the example, it would be clearer to say > > that “the window starts at 08:00:00” rather than “the window is 08:00:00.” > > > > You can update all the contents in the issue, and then send the issue link > in this thread, I'll review ASAP. > > Best, > Yuan > > > On Thu, Jul 9, 2026 at 3:31 > PM Xinqi Zhao < > [email protected]> wrote: > > > > Subject: [DISCUSS] Add Prometheus-like rate/irate/increase/delta aggregate > > functions for table model > > Hi IoTDB community, > > > I would like to start a discussion about adding four Prometheus-like > > functions to IoTDB table model SQL: > > rate() > > irate() > > increase() > > delta() > > Related issue: > > https://github.com/apache/iotdb/issues/17976 > > > > Background > > > In IoT and observability scenarios, metrics are often collected as > > > counters or gauges. Systems such as Prometheus commonly provide rate(), > > > irate(), increase(), and delta() to calculate rates, increments, and value > > changes over a time window. > > > Currently, IoTDB table model SQL does not provide these functions > > > directly. Users need to write complex SQL manually or export raw data to > > external systems for further calculation. > > > > Proposal > > > I propose to add these four functions as built-in aggregate functions for > > the IoTDB table model. > > The proposed signatures are: > > > rate(value_col, time_col, window_start, window_end) > > > increase(value_col, time_col, window_start, window_end) > > > irate(value_col, time_col, window_start, window_end) > > > delta(value_col, time_col, window_start, window_end) > > > The reason for using four arguments is that the function needs to know > > > both the sample timestamp and the exact aggregation window boundary. This > > > is especially important for sliding windows, such as HOP windows, where one > > > data point may belong to multiple overlapping windows. > > > > Parameter constraints > > value_col: > > > Supported types: INT32, INT64, FLOAT, DOUBLE. > > time_col: > > > TIMESTAMP expression or column, usually the table model time column. > > window_start and window_end: > > > TIMESTAMP expressions representing the current aggregation window boundary. > > Other constraints: > > The number of arguments must be 4. > > > window_start must be less than window_end. > > > If there are fewer than two valid non-null samples in the current > > group/window, the function returns NULL. > > > If the first and last valid samples used for calculation have the same > > timestamp, the function returns NULL. > > > > Function semantics > > rate(): > > > Uses all valid samples in the current aggregation window. > > Handles counter reset. > > > Applies Prometheus-compatible boundary extrapolation. > > Returns the average per-second rate. > > increase(): > > > Uses all valid samples in the current aggregation window. > > Handles counter reset. > > > Applies Prometheus-compatible boundary extrapolation. > > > Returns the total increase in the window. > > > It is semantically close to rate() multiplied by the window duration in > > seconds. > > irate(): > > > Uses only the last two valid samples in the current aggregation window. > > > Handles counter reset between the last two samples. > > Does not apply boundary extrapolation. > > Returns the instant per-second rate. > > delta(): > > > Uses all valid samples in the current aggregation window. > > Does not handle counter reset. > > > Applies Prometheus-compatible boundary extrapolation. > > > Returns the extrapolated value change over the whole window. > > Negative results are allowed. > > > > SQL usage > > > For fixed non-overlapping windows, users can use date_bin/date_bin_gapfill > > with GROUP BY: > > SELECT > > device_id, > > date_bin(5m, time) AS window_start, > > > rate(s1, time, window_start, window_start + 5m) AS s1_rate > > FROM table1 > > WHERE time >= '1970-01-01T00:00:00' > > AND time < '1970-01-01T01:00:00' > > GROUP BY device_id, window_start; > > > For window table functions, users can directly use window_start and > > window_end generated by TUMBLE/HOP: > > SELECT > > device_id, > > window_start, > > window_end, > > > rate(s1, time, window_start, window_end) AS s1_rate > > FROM HOP( > > DATA => ( > > SELECT time, device_id, s1 > > FROM table1 > > > WHERE time >= '1970-01-01T00:00:00' > > > AND time < '1970-01-01T01:00:00' > > ), > > TIMECOL => 'time', > > SLIDE => 1m, > > SIZE => 5m > > ) > > GROUP BY device_id, window_start, window_end; > > > > Best regards, > > Xinqi Zhao
