You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@iotdb.apache.org by Xinqi Zhao <33...@qq.com> on 2026/07/09 07:31:21 UTC

[Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model

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

Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model

Posted by Zh D <dz...@gmail.com>.
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 <33...@qq.com> 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

Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model

Posted by 王旋 <cr...@gmail.com>.
Hi Xinqi,

Thanks for the comprehensive technical design — it's well structured and
covers the key aspects thoroughly. I also appreciate Yuan's detailed
review, which raised important points on correctness and consistency.

A few additional thoughts:

1. The ordered-vs-unordered dual-mode approach is a pragmatic design. The
O(1) streaming path will make a real difference for the common case of
time-ordered table scans, while the buffered fallback ensures correctness
for arbitrary inputs.
2. The inputOrderedByTimeAscending property on AggregationNode — while
initially motivated by these four functions — could potentially benefit
other time-aware aggregations in the future. Worth calling that out in the
design doc so it's not treated as a one-off.
3. Given that counter resets, duplicate timestamps, NaN/Infinity, and
boundary conditions all have subtle interactions, I'd suggest including a
test-case matrix in the implementation plan covering at minimum: counter
wraps, gaps larger than the extrapolation threshold, single-sample windows,
and mixed NULL/non-NULL sequences for each function.

Looking forward to seeing this land — Prometheus-compatible rate/irate is a
valuable addition for observability use cases on IoTDB.

Best,
Xuan Wang

Xinqi Zhao <33...@qq.com> 于2026年7月9日周四 15:32写道:

> 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

Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model

Posted by Yuan Tian <ja...@gmail.com>.
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 <33...@qq.com> 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 -&gt; SQL Manual
> -&gt; 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 <jackietien97@gmail.com&gt;
> 发件时间:2026年7月10日 14:55
> 收件人:dev <dev@iotdb.apache.org&gt;
> 主题:Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate
> functions for table model
>
>
>
>        Hi&nbsp;Xinqi&nbsp;and&nbsp;all,
>
>
> Thanks&nbsp;for&nbsp;starting&nbsp;this&nbsp;discussion.&nbsp;I&nbsp;reviewed&nbsp;the&nbsp;proposal&nbsp;for
>
> https://github.com/apache/iotdb/issues/17976.&nbsp;I&nbsp;think&nbsp;the&nbsp;following&nbsp;points
> should&nbsp;be&nbsp;clarified&nbsp;before&nbsp;implementation
> <https://github.com/apache/iotdb/issues/17976.&nbsp;I&nbsp;think&nbsp;the&nbsp;following&nbsp;pointsshould&nbsp;be&nbsp;clarified&nbsp;before&nbsp;implementation>
> .
>
> 1.&nbsp;Function&nbsp;signatures&nbsp;and&nbsp;parameter&nbsp;validation
>
>
> The&nbsp;proposal&nbsp;currently&nbsp;gives&nbsp;all&nbsp;four&nbsp;functions&nbsp;the&nbsp;same&nbsp;signature:
>
>
> rate/increase/irate/delta(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
>
>
> Please&nbsp;document&nbsp;each&nbsp;function’s&nbsp;signature,&nbsp;accepted&nbsp;types,&nbsp;and&nbsp;validation
>
> rules&nbsp;in&nbsp;its&nbsp;own&nbsp;subsection,&nbsp;even&nbsp;where&nbsp;some&nbsp;rules&nbsp;are&nbsp;duplicated.
>
>
> For&nbsp;rate,&nbsp;increase,&nbsp;and&nbsp;delta,&nbsp;the&nbsp;window&nbsp;boundaries&nbsp;are&nbsp;needed&nbsp;for
>
> boundary&nbsp;extrapolation,&nbsp;especially&nbsp;with&nbsp;HOP&nbsp;windows.&nbsp;However,&nbsp;irate&nbsp;only
>
> uses&nbsp;the&nbsp;last&nbsp;two&nbsp;valid&nbsp;samples&nbsp;and&nbsp;performs&nbsp;no&nbsp;boundary&nbsp;extrapolation.&nbsp;I
> therefore&nbsp;suggest&nbsp;using:
>
> irate(value_col,&nbsp;time_col)
>
>
> If&nbsp;window_start&nbsp;and&nbsp;window_end&nbsp;are&nbsp;still&nbsp;required&nbsp;for&nbsp;irate,&nbsp;their&nbsp;semantic
> purpose&nbsp;should&nbsp;be&nbsp;explained&nbsp;explicitly.
>
>
> The&nbsp;time-related&nbsp;arguments—time_col,&nbsp;window_start,&nbsp;and&nbsp;window_end—should
> accept&nbsp;both&nbsp;TIMESTAMP&nbsp;and&nbsp;INT64.
>
> 2.&nbsp;Duplicate&nbsp;timestamps
>
>
> Any&nbsp;duplicate&nbsp;time_col&nbsp;value&nbsp;within&nbsp;a&nbsp;group&nbsp;should&nbsp;cause&nbsp;an&nbsp;exception&nbsp;for
> all&nbsp;four&nbsp;functions.
>
>
> For&nbsp;rate&nbsp;in&nbsp;particular,&nbsp;different&nbsp;values&nbsp;at&nbsp;the&nbsp;same&nbsp;timestamp&nbsp;have&nbsp;no
>
> well-defined&nbsp;order,&nbsp;and&nbsp;their&nbsp;order&nbsp;can&nbsp;affect&nbsp;counter-reset&nbsp;detection.
>
> Therefore,&nbsp;duplicate&nbsp;timestamps&nbsp;should&nbsp;be&nbsp;treated&nbsp;as&nbsp;invalid&nbsp;input&nbsp;rather
> than&nbsp;as&nbsp;a&nbsp;case&nbsp;that&nbsp;returns&nbsp;NULL.
>
>
> The&nbsp;current&nbsp;rule&nbsp;stating&nbsp;that&nbsp;NULL&nbsp;is&nbsp;returned&nbsp;when&nbsp;the&nbsp;first&nbsp;and&nbsp;last
>
> valid&nbsp;samples&nbsp;have&nbsp;the&nbsp;same&nbsp;timestamp&nbsp;should&nbsp;be&nbsp;revised&nbsp;accordingly.
>
> Returning&nbsp;NULL&nbsp;when&nbsp;there&nbsp;are&nbsp;fewer&nbsp;than&nbsp;two&nbsp;valid&nbsp;samples&nbsp;can&nbsp;remain
> unchanged.
>
> 3.&nbsp;Worked&nbsp;examples
>
>
> Please&nbsp;add&nbsp;concrete&nbsp;examples&nbsp;with&nbsp;input&nbsp;samples,&nbsp;window&nbsp;boundaries,&nbsp;SQL,
>
> and&nbsp;expected&nbsp;results&nbsp;for&nbsp;the&nbsp;important&nbsp;algorithm&nbsp;branches:
>
>
> -&nbsp;For&nbsp;rate,&nbsp;add&nbsp;one&nbsp;counter-reset&nbsp;compensation&nbsp;example&nbsp;and&nbsp;three
> boundary-extrapolation&nbsp;examples.
>
> -&nbsp;For&nbsp;increase,&nbsp;add&nbsp;examples&nbsp;for&nbsp;every&nbsp;documented&nbsp;normal&nbsp;and&nbsp;exceptional
> case,&nbsp;analogous&nbsp;to&nbsp;those&nbsp;for&nbsp;rate.
>
> -&nbsp;For&nbsp;irate,&nbsp;add&nbsp;an&nbsp;example&nbsp;showing&nbsp;reset&nbsp;handling&nbsp;between&nbsp;the&nbsp;last&nbsp;two
> valid&nbsp;samples.
>
> -&nbsp;For&nbsp;delta,&nbsp;add&nbsp;examples&nbsp;covering&nbsp;its&nbsp;rules,&nbsp;including&nbsp;boundary
> extrapolation&nbsp;without&nbsp;counter-reset&nbsp;compensation.
>
>
> These&nbsp;examples&nbsp;are&nbsp;important&nbsp;because&nbsp;describing&nbsp;the&nbsp;behavior&nbsp;only&nbsp;as
>
> “Prometheus-compatible”&nbsp;is&nbsp;not&nbsp;precise&nbsp;enough&nbsp;for&nbsp;implementation&nbsp;and
> testing.
>
> 4.&nbsp;Complexity&nbsp;and&nbsp;input&nbsp;ordering
>
>
> The&nbsp;stated&nbsp;O(n)&nbsp;time&nbsp;and&nbsp;O(1)&nbsp;space&nbsp;complexity&nbsp;is&nbsp;valid&nbsp;only&nbsp;if&nbsp;samples
>
> within&nbsp;each&nbsp;group&nbsp;are&nbsp;guaranteed&nbsp;to&nbsp;be&nbsp;ordered&nbsp;by&nbsp;time_col.
>
>
> Without&nbsp;that&nbsp;guarantee,&nbsp;the&nbsp;implementation&nbsp;must&nbsp;retain&nbsp;and&nbsp;sort&nbsp;the
>
> samples,&nbsp;resulting&nbsp;in&nbsp;O(n&nbsp;log&nbsp;n)&nbsp;time&nbsp;and&nbsp;O(n)&nbsp;space.&nbsp;Please&nbsp;either
>
> document&nbsp;the&nbsp;ordering&nbsp;guarantee&nbsp;or&nbsp;update&nbsp;the&nbsp;complexity&nbsp;analysis
> accordingly.
>
>
> One&nbsp;minor&nbsp;wording&nbsp;correction:&nbsp;in&nbsp;the&nbsp;example,&nbsp;it&nbsp;would&nbsp;be&nbsp;clearer&nbsp;to&nbsp;say
>
> that&nbsp;“the&nbsp;window&nbsp;starts&nbsp;at&nbsp;08:00:00”&nbsp;rather&nbsp;than&nbsp;“the&nbsp;window&nbsp;is&nbsp;08:00:00.”
>
>
>
> You&nbsp;can&nbsp;update&nbsp;all&nbsp;the&nbsp;contents&nbsp;in&nbsp;the&nbsp;issue,&nbsp;and&nbsp;then&nbsp;send&nbsp;the&nbsp;issue&nbsp;link
> in&nbsp;this&nbsp;thread,&nbsp;I'll&nbsp;review&nbsp;ASAP.
>
> Best,
> Yuan
>
>
> On&nbsp;Thu,&nbsp;Jul&nbsp;9,&nbsp;2026&nbsp;at&nbsp;3:31 PM&nbsp;Xinqi&nbsp;Zhao&nbsp;<
> 3322351820@qq.com&gt;&nbsp;wrote:
>
>
> &gt;&nbsp;Subject:&nbsp;[DISCUSS]&nbsp;Add&nbsp;Prometheus-like&nbsp;rate/irate/increase/delta&nbsp;aggregate
> &gt;&nbsp;functions&nbsp;for&nbsp;table&nbsp;model
> &gt;&nbsp;Hi&nbsp;IoTDB&nbsp;community,
>
> &gt;&nbsp;I&nbsp;would&nbsp;like&nbsp;to&nbsp;start&nbsp;a&nbsp;discussion&nbsp;about&nbsp;adding&nbsp;four&nbsp;Prometheus-like
> &gt;&nbsp;functions&nbsp;to&nbsp;IoTDB&nbsp;table&nbsp;model&nbsp;SQL:
> &gt;&nbsp;rate()
> &gt;&nbsp;irate()
> &gt;&nbsp;increase()
> &gt;&nbsp;delta()
> &gt;&nbsp;Related&nbsp;issue:
> &gt;&nbsp;https://github.com/apache/iotdb/issues/17976
> &gt;
> &gt;&nbsp;Background
>
> &gt;&nbsp;In&nbsp;IoT&nbsp;and&nbsp;observability&nbsp;scenarios,&nbsp;metrics&nbsp;are&nbsp;often&nbsp;collected&nbsp;as
>
> &gt;&nbsp;counters&nbsp;or&nbsp;gauges.&nbsp;Systems&nbsp;such&nbsp;as&nbsp;Prometheus&nbsp;commonly&nbsp;provide&nbsp;rate(),
>
> &gt;&nbsp;irate(),&nbsp;increase(),&nbsp;and&nbsp;delta()&nbsp;to&nbsp;calculate&nbsp;rates,&nbsp;increments,&nbsp;and&nbsp;value
> &gt;&nbsp;changes&nbsp;over&nbsp;a&nbsp;time&nbsp;window.
>
> &gt;&nbsp;Currently,&nbsp;IoTDB&nbsp;table&nbsp;model&nbsp;SQL&nbsp;does&nbsp;not&nbsp;provide&nbsp;these&nbsp;functions
>
> &gt;&nbsp;directly.&nbsp;Users&nbsp;need&nbsp;to&nbsp;write&nbsp;complex&nbsp;SQL&nbsp;manually&nbsp;or&nbsp;export&nbsp;raw&nbsp;data&nbsp;to
> &gt;&nbsp;external&nbsp;systems&nbsp;for&nbsp;further&nbsp;calculation.
> &gt;
> &gt;&nbsp;Proposal
>
> &gt;&nbsp;I&nbsp;propose&nbsp;to&nbsp;add&nbsp;these&nbsp;four&nbsp;functions&nbsp;as&nbsp;built-in&nbsp;aggregate&nbsp;functions&nbsp;for
> &gt;&nbsp;the&nbsp;IoTDB&nbsp;table&nbsp;model.
> &gt;&nbsp;The&nbsp;proposed&nbsp;signatures&nbsp;are:
>
> &gt;&nbsp;rate(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
>
> &gt;&nbsp;increase(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
>
> &gt;&nbsp;irate(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
>
> &gt;&nbsp;delta(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
>
> &gt;&nbsp;The&nbsp;reason&nbsp;for&nbsp;using&nbsp;four&nbsp;arguments&nbsp;is&nbsp;that&nbsp;the&nbsp;function&nbsp;needs&nbsp;to&nbsp;know
>
> &gt;&nbsp;both&nbsp;the&nbsp;sample&nbsp;timestamp&nbsp;and&nbsp;the&nbsp;exact&nbsp;aggregation&nbsp;window&nbsp;boundary.&nbsp;This
>
> &gt;&nbsp;is&nbsp;especially&nbsp;important&nbsp;for&nbsp;sliding&nbsp;windows,&nbsp;such&nbsp;as&nbsp;HOP&nbsp;windows,&nbsp;where&nbsp;one
>
> &gt;&nbsp;data&nbsp;point&nbsp;may&nbsp;belong&nbsp;to&nbsp;multiple&nbsp;overlapping&nbsp;windows.
> &gt;
> &gt;&nbsp;Parameter&nbsp;constraints
> &gt;&nbsp;value_col:
>
> &gt;&nbsp;Supported&nbsp;types:&nbsp;INT32,&nbsp;INT64,&nbsp;FLOAT,&nbsp;DOUBLE.
> &gt;&nbsp;time_col:
>
> &gt;&nbsp;TIMESTAMP&nbsp;expression&nbsp;or&nbsp;column,&nbsp;usually&nbsp;the&nbsp;table&nbsp;model&nbsp;time&nbsp;column.
> &gt;&nbsp;window_start&nbsp;and&nbsp;window_end:
>
> &gt;&nbsp;TIMESTAMP&nbsp;expressions&nbsp;representing&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window&nbsp;boundary.
> &gt;&nbsp;Other&nbsp;constraints:
> &gt;&nbsp;The&nbsp;number&nbsp;of&nbsp;arguments&nbsp;must&nbsp;be&nbsp;4.
>
> &gt;&nbsp;window_start&nbsp;must&nbsp;be&nbsp;less&nbsp;than&nbsp;window_end.
>
> &gt;&nbsp;If&nbsp;there&nbsp;are&nbsp;fewer&nbsp;than&nbsp;two&nbsp;valid&nbsp;non-null&nbsp;samples&nbsp;in&nbsp;the&nbsp;current
> &gt;&nbsp;group/window,&nbsp;the&nbsp;function&nbsp;returns&nbsp;NULL.
>
> &gt;&nbsp;If&nbsp;the&nbsp;first&nbsp;and&nbsp;last&nbsp;valid&nbsp;samples&nbsp;used&nbsp;for&nbsp;calculation&nbsp;have&nbsp;the&nbsp;same
> &gt;&nbsp;timestamp,&nbsp;the&nbsp;function&nbsp;returns&nbsp;NULL.
> &gt;
> &gt;&nbsp;Function&nbsp;semantics
> &gt;&nbsp;rate():
>
> &gt;&nbsp;Uses&nbsp;all&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
> &gt;&nbsp;Handles&nbsp;counter&nbsp;reset.
>
> &gt;&nbsp;Applies&nbsp;Prometheus-compatible&nbsp;boundary&nbsp;extrapolation.
> &gt;&nbsp;Returns&nbsp;the&nbsp;average&nbsp;per-second&nbsp;rate.
> &gt;&nbsp;increase():
>
> &gt;&nbsp;Uses&nbsp;all&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
> &gt;&nbsp;Handles&nbsp;counter&nbsp;reset.
>
> &gt;&nbsp;Applies&nbsp;Prometheus-compatible&nbsp;boundary&nbsp;extrapolation.
>
> &gt;&nbsp;Returns&nbsp;the&nbsp;total&nbsp;increase&nbsp;in&nbsp;the&nbsp;window.
>
> &gt;&nbsp;It&nbsp;is&nbsp;semantically&nbsp;close&nbsp;to&nbsp;rate()&nbsp;multiplied&nbsp;by&nbsp;the&nbsp;window&nbsp;duration&nbsp;in
> &gt;&nbsp;seconds.
> &gt;&nbsp;irate():
>
> &gt;&nbsp;Uses&nbsp;only&nbsp;the&nbsp;last&nbsp;two&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
>
> &gt;&nbsp;Handles&nbsp;counter&nbsp;reset&nbsp;between&nbsp;the&nbsp;last&nbsp;two&nbsp;samples.
> &gt;&nbsp;Does&nbsp;not&nbsp;apply&nbsp;boundary&nbsp;extrapolation.
> &gt;&nbsp;Returns&nbsp;the&nbsp;instant&nbsp;per-second&nbsp;rate.
> &gt;&nbsp;delta():
>
> &gt;&nbsp;Uses&nbsp;all&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
> &gt;&nbsp;Does&nbsp;not&nbsp;handle&nbsp;counter&nbsp;reset.
>
> &gt;&nbsp;Applies&nbsp;Prometheus-compatible&nbsp;boundary&nbsp;extrapolation.
>
> &gt;&nbsp;Returns&nbsp;the&nbsp;extrapolated&nbsp;value&nbsp;change&nbsp;over&nbsp;the&nbsp;whole&nbsp;window.
> &gt;&nbsp;Negative&nbsp;results&nbsp;are&nbsp;allowed.
> &gt;
> &gt;&nbsp;SQL&nbsp;usage
>
> &gt;&nbsp;For&nbsp;fixed&nbsp;non-overlapping&nbsp;windows,&nbsp;users&nbsp;can&nbsp;use&nbsp;date_bin/date_bin_gapfill
> &gt;&nbsp;with&nbsp;GROUP&nbsp;BY:
> &gt;&nbsp;SELECT
> &gt;&nbsp;&nbsp;&nbsp;device_id,
> &gt;&nbsp;&nbsp;&nbsp;date_bin(5m,&nbsp;time)&nbsp;AS&nbsp;window_start,
>
> &gt;&nbsp;&nbsp;&nbsp;rate(s1,&nbsp;time,&nbsp;window_start,&nbsp;window_start&nbsp;+&nbsp;5m)&nbsp;AS&nbsp;s1_rate
> &gt;&nbsp;FROM&nbsp;table1
> &gt;&nbsp;WHERE&nbsp;time&nbsp;&gt;=&nbsp;'1970-01-01T00:00:00'
> &gt;&nbsp;&nbsp;&nbsp;AND&nbsp;time&nbsp;<&nbsp;&nbsp;'1970-01-01T01:00:00'
> &gt;&nbsp;GROUP&nbsp;BY&nbsp;device_id,&nbsp;window_start;
>
> &gt;&nbsp;For&nbsp;window&nbsp;table&nbsp;functions,&nbsp;users&nbsp;can&nbsp;directly&nbsp;use&nbsp;window_start&nbsp;and
> &gt;&nbsp;window_end&nbsp;generated&nbsp;by&nbsp;TUMBLE/HOP:
> &gt;&nbsp;SELECT
> &gt;&nbsp;&nbsp;&nbsp;device_id,
> &gt;&nbsp;&nbsp;&nbsp;window_start,
> &gt;&nbsp;&nbsp;&nbsp;window_end,
>
> &gt;&nbsp;&nbsp;&nbsp;rate(s1,&nbsp;time,&nbsp;window_start,&nbsp;window_end)&nbsp;AS&nbsp;s1_rate
> &gt;&nbsp;FROM&nbsp;HOP(
> &gt;&nbsp;&nbsp;&nbsp;DATA&nbsp;=&gt;&nbsp;(
> &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;time,&nbsp;device_id,&nbsp;s1
> &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;table1
>
> &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;time&nbsp;&gt;=&nbsp;'1970-01-01T00:00:00'
>
> &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;time&nbsp;<&nbsp;&nbsp;'1970-01-01T01:00:00'
> &gt;&nbsp;&nbsp;&nbsp;),
> &gt;&nbsp;&nbsp;&nbsp;TIMECOL&nbsp;=&gt;&nbsp;'time',
> &gt;&nbsp;&nbsp;&nbsp;SLIDE&nbsp;=&gt;&nbsp;1m,
> &gt;&nbsp;&nbsp;&nbsp;SIZE&nbsp;=&gt;&nbsp;5m
> &gt;&nbsp;)
> &gt;&nbsp;GROUP&nbsp;BY&nbsp;device_id,&nbsp;window_start,&nbsp;window_end;
> &gt;
> &gt;&nbsp;Best&nbsp;regards,
> &gt;&nbsp;Xinqi&nbsp;Zhao

Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model

Posted by Xinqi Zhao <33...@qq.com>.
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 -&gt; SQL Manual -&gt; 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 <jackietien97@gmail.com&gt;
发件时间:2026年7月10日 14:55
收件人:dev <dev@iotdb.apache.org&gt;
主题:Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model



       Hi&nbsp;Xinqi&nbsp;and&nbsp;all,

Thanks&nbsp;for&nbsp;starting&nbsp;this&nbsp;discussion.&nbsp;I&nbsp;reviewed&nbsp;the&nbsp;proposal&nbsp;for
https://github.com/apache/iotdb/issues/17976.&nbsp;I&nbsp;think&nbsp;the&nbsp;following&nbsp;points
should&nbsp;be&nbsp;clarified&nbsp;before&nbsp;implementation.

1.&nbsp;Function&nbsp;signatures&nbsp;and&nbsp;parameter&nbsp;validation

The&nbsp;proposal&nbsp;currently&nbsp;gives&nbsp;all&nbsp;four&nbsp;functions&nbsp;the&nbsp;same&nbsp;signature:

rate/increase/irate/delta(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)

Please&nbsp;document&nbsp;each&nbsp;function’s&nbsp;signature,&nbsp;accepted&nbsp;types,&nbsp;and&nbsp;validation
rules&nbsp;in&nbsp;its&nbsp;own&nbsp;subsection,&nbsp;even&nbsp;where&nbsp;some&nbsp;rules&nbsp;are&nbsp;duplicated.

For&nbsp;rate,&nbsp;increase,&nbsp;and&nbsp;delta,&nbsp;the&nbsp;window&nbsp;boundaries&nbsp;are&nbsp;needed&nbsp;for
boundary&nbsp;extrapolation,&nbsp;especially&nbsp;with&nbsp;HOP&nbsp;windows.&nbsp;However,&nbsp;irate&nbsp;only
uses&nbsp;the&nbsp;last&nbsp;two&nbsp;valid&nbsp;samples&nbsp;and&nbsp;performs&nbsp;no&nbsp;boundary&nbsp;extrapolation.&nbsp;I
therefore&nbsp;suggest&nbsp;using:

irate(value_col,&nbsp;time_col)

If&nbsp;window_start&nbsp;and&nbsp;window_end&nbsp;are&nbsp;still&nbsp;required&nbsp;for&nbsp;irate,&nbsp;their&nbsp;semantic
purpose&nbsp;should&nbsp;be&nbsp;explained&nbsp;explicitly.

The&nbsp;time-related&nbsp;arguments—time_col,&nbsp;window_start,&nbsp;and&nbsp;window_end—should
accept&nbsp;both&nbsp;TIMESTAMP&nbsp;and&nbsp;INT64.

2.&nbsp;Duplicate&nbsp;timestamps

Any&nbsp;duplicate&nbsp;time_col&nbsp;value&nbsp;within&nbsp;a&nbsp;group&nbsp;should&nbsp;cause&nbsp;an&nbsp;exception&nbsp;for
all&nbsp;four&nbsp;functions.

For&nbsp;rate&nbsp;in&nbsp;particular,&nbsp;different&nbsp;values&nbsp;at&nbsp;the&nbsp;same&nbsp;timestamp&nbsp;have&nbsp;no
well-defined&nbsp;order,&nbsp;and&nbsp;their&nbsp;order&nbsp;can&nbsp;affect&nbsp;counter-reset&nbsp;detection.
Therefore,&nbsp;duplicate&nbsp;timestamps&nbsp;should&nbsp;be&nbsp;treated&nbsp;as&nbsp;invalid&nbsp;input&nbsp;rather
than&nbsp;as&nbsp;a&nbsp;case&nbsp;that&nbsp;returns&nbsp;NULL.

The&nbsp;current&nbsp;rule&nbsp;stating&nbsp;that&nbsp;NULL&nbsp;is&nbsp;returned&nbsp;when&nbsp;the&nbsp;first&nbsp;and&nbsp;last
valid&nbsp;samples&nbsp;have&nbsp;the&nbsp;same&nbsp;timestamp&nbsp;should&nbsp;be&nbsp;revised&nbsp;accordingly.
Returning&nbsp;NULL&nbsp;when&nbsp;there&nbsp;are&nbsp;fewer&nbsp;than&nbsp;two&nbsp;valid&nbsp;samples&nbsp;can&nbsp;remain
unchanged.

3.&nbsp;Worked&nbsp;examples

Please&nbsp;add&nbsp;concrete&nbsp;examples&nbsp;with&nbsp;input&nbsp;samples,&nbsp;window&nbsp;boundaries,&nbsp;SQL,
and&nbsp;expected&nbsp;results&nbsp;for&nbsp;the&nbsp;important&nbsp;algorithm&nbsp;branches:

-&nbsp;For&nbsp;rate,&nbsp;add&nbsp;one&nbsp;counter-reset&nbsp;compensation&nbsp;example&nbsp;and&nbsp;three
boundary-extrapolation&nbsp;examples.
-&nbsp;For&nbsp;increase,&nbsp;add&nbsp;examples&nbsp;for&nbsp;every&nbsp;documented&nbsp;normal&nbsp;and&nbsp;exceptional
case,&nbsp;analogous&nbsp;to&nbsp;those&nbsp;for&nbsp;rate.
-&nbsp;For&nbsp;irate,&nbsp;add&nbsp;an&nbsp;example&nbsp;showing&nbsp;reset&nbsp;handling&nbsp;between&nbsp;the&nbsp;last&nbsp;two
valid&nbsp;samples.
-&nbsp;For&nbsp;delta,&nbsp;add&nbsp;examples&nbsp;covering&nbsp;its&nbsp;rules,&nbsp;including&nbsp;boundary
extrapolation&nbsp;without&nbsp;counter-reset&nbsp;compensation.

These&nbsp;examples&nbsp;are&nbsp;important&nbsp;because&nbsp;describing&nbsp;the&nbsp;behavior&nbsp;only&nbsp;as
“Prometheus-compatible”&nbsp;is&nbsp;not&nbsp;precise&nbsp;enough&nbsp;for&nbsp;implementation&nbsp;and
testing.

4.&nbsp;Complexity&nbsp;and&nbsp;input&nbsp;ordering

The&nbsp;stated&nbsp;O(n)&nbsp;time&nbsp;and&nbsp;O(1)&nbsp;space&nbsp;complexity&nbsp;is&nbsp;valid&nbsp;only&nbsp;if&nbsp;samples
within&nbsp;each&nbsp;group&nbsp;are&nbsp;guaranteed&nbsp;to&nbsp;be&nbsp;ordered&nbsp;by&nbsp;time_col.

Without&nbsp;that&nbsp;guarantee,&nbsp;the&nbsp;implementation&nbsp;must&nbsp;retain&nbsp;and&nbsp;sort&nbsp;the
samples,&nbsp;resulting&nbsp;in&nbsp;O(n&nbsp;log&nbsp;n)&nbsp;time&nbsp;and&nbsp;O(n)&nbsp;space.&nbsp;Please&nbsp;either
document&nbsp;the&nbsp;ordering&nbsp;guarantee&nbsp;or&nbsp;update&nbsp;the&nbsp;complexity&nbsp;analysis
accordingly.

One&nbsp;minor&nbsp;wording&nbsp;correction:&nbsp;in&nbsp;the&nbsp;example,&nbsp;it&nbsp;would&nbsp;be&nbsp;clearer&nbsp;to&nbsp;say
that&nbsp;“the&nbsp;window&nbsp;starts&nbsp;at&nbsp;08:00:00”&nbsp;rather&nbsp;than&nbsp;“the&nbsp;window&nbsp;is&nbsp;08:00:00.”


You&nbsp;can&nbsp;update&nbsp;all&nbsp;the&nbsp;contents&nbsp;in&nbsp;the&nbsp;issue,&nbsp;and&nbsp;then&nbsp;send&nbsp;the&nbsp;issue&nbsp;link
in&nbsp;this&nbsp;thread,&nbsp;I'll&nbsp;review&nbsp;ASAP.

Best,
Yuan

On&nbsp;Thu,&nbsp;Jul&nbsp;9,&nbsp;2026&nbsp;at&nbsp;3:31 PM&nbsp;Xinqi&nbsp;Zhao&nbsp;<3322351820@qq.com&gt;&nbsp;wrote:

&gt;&nbsp;Subject:&nbsp;[DISCUSS]&nbsp;Add&nbsp;Prometheus-like&nbsp;rate/irate/increase/delta&nbsp;aggregate
&gt;&nbsp;functions&nbsp;for&nbsp;table&nbsp;model
&gt;&nbsp;Hi&nbsp;IoTDB&nbsp;community,
&gt;&nbsp;I&nbsp;would&nbsp;like&nbsp;to&nbsp;start&nbsp;a&nbsp;discussion&nbsp;about&nbsp;adding&nbsp;four&nbsp;Prometheus-like
&gt;&nbsp;functions&nbsp;to&nbsp;IoTDB&nbsp;table&nbsp;model&nbsp;SQL:
&gt;&nbsp;rate()
&gt;&nbsp;irate()
&gt;&nbsp;increase()
&gt;&nbsp;delta()
&gt;&nbsp;Related&nbsp;issue:
&gt;&nbsp;https://github.com/apache/iotdb/issues/17976
&gt;
&gt;&nbsp;Background
&gt;&nbsp;In&nbsp;IoT&nbsp;and&nbsp;observability&nbsp;scenarios,&nbsp;metrics&nbsp;are&nbsp;often&nbsp;collected&nbsp;as
&gt;&nbsp;counters&nbsp;or&nbsp;gauges.&nbsp;Systems&nbsp;such&nbsp;as&nbsp;Prometheus&nbsp;commonly&nbsp;provide&nbsp;rate(),
&gt;&nbsp;irate(),&nbsp;increase(),&nbsp;and&nbsp;delta()&nbsp;to&nbsp;calculate&nbsp;rates,&nbsp;increments,&nbsp;and&nbsp;value
&gt;&nbsp;changes&nbsp;over&nbsp;a&nbsp;time&nbsp;window.
&gt;&nbsp;Currently,&nbsp;IoTDB&nbsp;table&nbsp;model&nbsp;SQL&nbsp;does&nbsp;not&nbsp;provide&nbsp;these&nbsp;functions
&gt;&nbsp;directly.&nbsp;Users&nbsp;need&nbsp;to&nbsp;write&nbsp;complex&nbsp;SQL&nbsp;manually&nbsp;or&nbsp;export&nbsp;raw&nbsp;data&nbsp;to
&gt;&nbsp;external&nbsp;systems&nbsp;for&nbsp;further&nbsp;calculation.
&gt;
&gt;&nbsp;Proposal
&gt;&nbsp;I&nbsp;propose&nbsp;to&nbsp;add&nbsp;these&nbsp;four&nbsp;functions&nbsp;as&nbsp;built-in&nbsp;aggregate&nbsp;functions&nbsp;for
&gt;&nbsp;the&nbsp;IoTDB&nbsp;table&nbsp;model.
&gt;&nbsp;The&nbsp;proposed&nbsp;signatures&nbsp;are:
&gt;&nbsp;rate(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
&gt;&nbsp;increase(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
&gt;&nbsp;irate(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
&gt;&nbsp;delta(value_col,&nbsp;time_col,&nbsp;window_start,&nbsp;window_end)
&gt;&nbsp;The&nbsp;reason&nbsp;for&nbsp;using&nbsp;four&nbsp;arguments&nbsp;is&nbsp;that&nbsp;the&nbsp;function&nbsp;needs&nbsp;to&nbsp;know
&gt;&nbsp;both&nbsp;the&nbsp;sample&nbsp;timestamp&nbsp;and&nbsp;the&nbsp;exact&nbsp;aggregation&nbsp;window&nbsp;boundary.&nbsp;This
&gt;&nbsp;is&nbsp;especially&nbsp;important&nbsp;for&nbsp;sliding&nbsp;windows,&nbsp;such&nbsp;as&nbsp;HOP&nbsp;windows,&nbsp;where&nbsp;one
&gt;&nbsp;data&nbsp;point&nbsp;may&nbsp;belong&nbsp;to&nbsp;multiple&nbsp;overlapping&nbsp;windows.
&gt;
&gt;&nbsp;Parameter&nbsp;constraints
&gt;&nbsp;value_col:
&gt;&nbsp;Supported&nbsp;types:&nbsp;INT32,&nbsp;INT64,&nbsp;FLOAT,&nbsp;DOUBLE.
&gt;&nbsp;time_col:
&gt;&nbsp;TIMESTAMP&nbsp;expression&nbsp;or&nbsp;column,&nbsp;usually&nbsp;the&nbsp;table&nbsp;model&nbsp;time&nbsp;column.
&gt;&nbsp;window_start&nbsp;and&nbsp;window_end:
&gt;&nbsp;TIMESTAMP&nbsp;expressions&nbsp;representing&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window&nbsp;boundary.
&gt;&nbsp;Other&nbsp;constraints:
&gt;&nbsp;The&nbsp;number&nbsp;of&nbsp;arguments&nbsp;must&nbsp;be&nbsp;4.
&gt;&nbsp;window_start&nbsp;must&nbsp;be&nbsp;less&nbsp;than&nbsp;window_end.
&gt;&nbsp;If&nbsp;there&nbsp;are&nbsp;fewer&nbsp;than&nbsp;two&nbsp;valid&nbsp;non-null&nbsp;samples&nbsp;in&nbsp;the&nbsp;current
&gt;&nbsp;group/window,&nbsp;the&nbsp;function&nbsp;returns&nbsp;NULL.
&gt;&nbsp;If&nbsp;the&nbsp;first&nbsp;and&nbsp;last&nbsp;valid&nbsp;samples&nbsp;used&nbsp;for&nbsp;calculation&nbsp;have&nbsp;the&nbsp;same
&gt;&nbsp;timestamp,&nbsp;the&nbsp;function&nbsp;returns&nbsp;NULL.
&gt;
&gt;&nbsp;Function&nbsp;semantics
&gt;&nbsp;rate():
&gt;&nbsp;Uses&nbsp;all&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
&gt;&nbsp;Handles&nbsp;counter&nbsp;reset.
&gt;&nbsp;Applies&nbsp;Prometheus-compatible&nbsp;boundary&nbsp;extrapolation.
&gt;&nbsp;Returns&nbsp;the&nbsp;average&nbsp;per-second&nbsp;rate.
&gt;&nbsp;increase():
&gt;&nbsp;Uses&nbsp;all&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
&gt;&nbsp;Handles&nbsp;counter&nbsp;reset.
&gt;&nbsp;Applies&nbsp;Prometheus-compatible&nbsp;boundary&nbsp;extrapolation.
&gt;&nbsp;Returns&nbsp;the&nbsp;total&nbsp;increase&nbsp;in&nbsp;the&nbsp;window.
&gt;&nbsp;It&nbsp;is&nbsp;semantically&nbsp;close&nbsp;to&nbsp;rate()&nbsp;multiplied&nbsp;by&nbsp;the&nbsp;window&nbsp;duration&nbsp;in
&gt;&nbsp;seconds.
&gt;&nbsp;irate():
&gt;&nbsp;Uses&nbsp;only&nbsp;the&nbsp;last&nbsp;two&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
&gt;&nbsp;Handles&nbsp;counter&nbsp;reset&nbsp;between&nbsp;the&nbsp;last&nbsp;two&nbsp;samples.
&gt;&nbsp;Does&nbsp;not&nbsp;apply&nbsp;boundary&nbsp;extrapolation.
&gt;&nbsp;Returns&nbsp;the&nbsp;instant&nbsp;per-second&nbsp;rate.
&gt;&nbsp;delta():
&gt;&nbsp;Uses&nbsp;all&nbsp;valid&nbsp;samples&nbsp;in&nbsp;the&nbsp;current&nbsp;aggregation&nbsp;window.
&gt;&nbsp;Does&nbsp;not&nbsp;handle&nbsp;counter&nbsp;reset.
&gt;&nbsp;Applies&nbsp;Prometheus-compatible&nbsp;boundary&nbsp;extrapolation.
&gt;&nbsp;Returns&nbsp;the&nbsp;extrapolated&nbsp;value&nbsp;change&nbsp;over&nbsp;the&nbsp;whole&nbsp;window.
&gt;&nbsp;Negative&nbsp;results&nbsp;are&nbsp;allowed.
&gt;
&gt;&nbsp;SQL&nbsp;usage
&gt;&nbsp;For&nbsp;fixed&nbsp;non-overlapping&nbsp;windows,&nbsp;users&nbsp;can&nbsp;use&nbsp;date_bin/date_bin_gapfill
&gt;&nbsp;with&nbsp;GROUP&nbsp;BY:
&gt;&nbsp;SELECT
&gt;&nbsp;&nbsp;&nbsp;device_id,
&gt;&nbsp;&nbsp;&nbsp;date_bin(5m,&nbsp;time)&nbsp;AS&nbsp;window_start,
&gt;&nbsp;&nbsp;&nbsp;rate(s1,&nbsp;time,&nbsp;window_start,&nbsp;window_start&nbsp;+&nbsp;5m)&nbsp;AS&nbsp;s1_rate
&gt;&nbsp;FROM&nbsp;table1
&gt;&nbsp;WHERE&nbsp;time&nbsp;&gt;=&nbsp;'1970-01-01T00:00:00'
&gt;&nbsp;&nbsp;&nbsp;AND&nbsp;time&nbsp;<&nbsp;&nbsp;'1970-01-01T01:00:00'
&gt;&nbsp;GROUP&nbsp;BY&nbsp;device_id,&nbsp;window_start;
&gt;&nbsp;For&nbsp;window&nbsp;table&nbsp;functions,&nbsp;users&nbsp;can&nbsp;directly&nbsp;use&nbsp;window_start&nbsp;and
&gt;&nbsp;window_end&nbsp;generated&nbsp;by&nbsp;TUMBLE/HOP:
&gt;&nbsp;SELECT
&gt;&nbsp;&nbsp;&nbsp;device_id,
&gt;&nbsp;&nbsp;&nbsp;window_start,
&gt;&nbsp;&nbsp;&nbsp;window_end,
&gt;&nbsp;&nbsp;&nbsp;rate(s1,&nbsp;time,&nbsp;window_start,&nbsp;window_end)&nbsp;AS&nbsp;s1_rate
&gt;&nbsp;FROM&nbsp;HOP(
&gt;&nbsp;&nbsp;&nbsp;DATA&nbsp;=&gt;&nbsp;(
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;time,&nbsp;device_id,&nbsp;s1
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;table1
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;time&nbsp;&gt;=&nbsp;'1970-01-01T00:00:00'
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;time&nbsp;<&nbsp;&nbsp;'1970-01-01T01:00:00'
&gt;&nbsp;&nbsp;&nbsp;),
&gt;&nbsp;&nbsp;&nbsp;TIMECOL&nbsp;=&gt;&nbsp;'time',
&gt;&nbsp;&nbsp;&nbsp;SLIDE&nbsp;=&gt;&nbsp;1m,
&gt;&nbsp;&nbsp;&nbsp;SIZE&nbsp;=&gt;&nbsp;5m
&gt;&nbsp;)
&gt;&nbsp;GROUP&nbsp;BY&nbsp;device_id,&nbsp;window_start,&nbsp;window_end;
&gt;
&gt;&nbsp;Best&nbsp;regards,
&gt;&nbsp;Xinqi&nbsp;Zhao

Re: [Feature] Add Prometheus-like rate/irate/increase/delta aggregate functions for table model

Posted by Yuan Tian <ja...@gmail.com>.
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 <33...@qq.com> 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