Hi Xinqi, Thanks for the detailed write-up -- Prometheus-style rate/increase/irate/delta on the table model would fill a real gap for counter and gauge analytics, and passing the window bounds explicitly (the 4-argument form) makes sense: an aggregate only sees its argument columns, not the nominal window, and extrapolation needs those exact boundaries. (Prometheus rate() is 1-arg only because its range-vector semantics carry both the samples and the window; SQL has no range vector, so explicit window arguments are the right adaptation.) +1 on the direction.
Two things I ran into while working on adjacent time-bucketed aggregation (date_bin) that might be worth pinning down: 1. Accumulation/return type for INT64 inputs. Prometheus computes rate/increase/delta in float64 throughout, which silently loses integer exactness above 2^53; and for delta() on large-magnitude INT64 values the subtraction can overflow 2^63. Since IoTDB has native INT64, it may be worth stating the intended accumulation/return type and how precision/overflow is handled near the extremes -- i.e. follow Prometheus's float64 (accepting the >2^53 loss), or preserve INT64 exactness where possible. 2. Counter-reset handling across partial aggregation. rate/increase/irate are order-dependent -- reset compensation needs the samples in time order -- and table-model aggregation merges partial results across fragments, so a reset can land on a fragment (or chunk) boundary. How are you thinking about detecting resets across those merged partial states, rather than only within a single pass? Happy to help test against real counter data once there's a branch. Thanks for driving this. Best regards, Zihan Dai On Thu, Jul 09, 2026 05: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
