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
