Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

nonNegativeDerivative

View as MarkdownEdit this pageReport an issueSet up docs MCP

Computes the non-negative derivative of metric_column with respect to timestamp_column. This is a ClickHouse-specific window function, not part of standard SQL.

For each row, the derivative is computed against the previous row in the window’s evaluation order, which is determined by the window’s ORDER BY clause - not by timestamp_column. The timestamp_column argument is read only to measure the elapsed time between the current row and that previous row; it does not order the rows itself.

The result is the rate of change of the metric per INTERVAL, with any negative value clamped to 0. This is useful for monotonically increasing metrics, such as counters, where a decrease usually indicates a reset rather than a real negative rate.

Syntax

nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]
        [ROWS, RANGE, or GROUPS expression_to_bound_rows_within_the_group]] | [window_name])
FROM table_name
WINDOW window_name AS ([PARTITION BY grouping_column] [ORDER BY sorting_column] [ROWS, RANGE, or GROUPS expression_to_bound_rows_within_the_group])

For more detail on window function syntax see: Window Functions - Syntax.

Arguments

  • metric_column — The column whose derivative is computed. (U)Int* or Float*.
  • timestamp_column — The column used to measure the elapsed time between the current row and the previous row in the window order. It does not order the rows; the window’s ORDER BY does, and should normally use this same column. DateTime or DateTime64.
  • INTERVAL X UNITS — Optional. The time unit the result is scaled to. Defaults to INTERVAL 1 SECOND. Only fixed-length units are supported (NANOSECOND, MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK); variable-length units (MONTH, QUARTER, YEAR) raise an exception.

Returned value

For each row, the value is computed as:

  • 0 for the first row;
  • 0 for any row whose elapsed time since the previous row is non-positive (that is, \text{timestamp}_i - \text{timestamp}_{i-1} \le 0, as happens with descending order or duplicate timestamps); and
  • {\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval} otherwise.

If the computed value would be negative, it is clamped to 0. The return type is Float64.

Example

The following example computes the per-second rate of change of a sensor reading. Note that the third row drops from 110 to 105, so its derivative is clamped to 0.

Querysql
CREATE TABLE sensor_readings
(
    `sensor_id` UInt32,
    `ts`        DateTime,
    `reading`   Float64
)
ENGINE = Memory;

INSERT INTO sensor_readings VALUES
    (1, '2024-01-01 00:00:00', 100),
    (1, '2024-01-01 00:00:10', 110),
    (1, '2024-01-01 00:00:20', 105),
    (1, '2024-01-01 00:00:30', 130);
Querysql
SELECT
    ts,
    reading,
    nonNegativeDerivative(reading, ts) OVER (ORDER BY ts ASC) AS deriv_per_second
FROM sensor_readings
ORDER BY ts ASC;
Responseresponse
   ┌──────────────────ts─┬─reading─┬─deriv_per_second─┐
1. │ 2024-01-01 00:00:00 │     100 │                0 │
2. │ 2024-01-01 00:00:10 │     110 │                1 │
3. │ 2024-01-01 00:00:20 │     105 │                0 │
4. │ 2024-01-01 00:00:30 │     130 │              2.5 │
   └─────────────────────┴─────────┴──────────────────┘
Navigation