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
