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. 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
