nonNegativeDerivative - ClickHouse Documentation

Вычисляет неотрицательную производную metric_column по timestamp_column. Это оконная функция, специфичная для ClickHouse и не входящая в стандарт SQL. Для каждой строки производная вычисляется относительно предыдущей строки в порядке вычисления окна, который определяется секцией ORDER BY окна, а не timestamp_column. Аргумент timestamp_column используется только для измерения времени, прошедшего между текущей и предыдущей строкой; он не задаёт порядок строк.

nonNegativeDerivative не упорядочивает строки по timestamp_column — это делает ORDER BY окна. Чтобы приведённая ниже формула применялась корректно, timestamp_column должен строго возрастать в порядке вычисления окна, поэтому окно следует упорядочивать по timestamp_column по возрастанию (например, ... OVER (ORDER BY ts ASC) совместно с nonNegativeDerivative(metric, ts)). Если время, прошедшее между текущей и предыдущей строкой, неположительно — что происходит при ORDER BY timestamp_column DESC или при совпадающих временных метках — функция возвращает 0 для этой строки вместо применения формулы.

Результатом является скорость изменения метрики за INTERVAL, при этом отрицательные значения обнуляются. Это полезно для монотонно возрастающих метрик, таких как Counter, где уменьшение значения обычно означает сброс, а не реальную отрицательную скорость изменения. Синтаксис

nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]
        [ROWS or RANGE 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 or RANGE expression_to_bound_rows_within_the_group])

Подробнее о синтаксисе оконных функций см.: Оконные функции — Синтаксис. Аргументы

metric_column — столбец, производная которого вычисляется. (U)Int* или Float*.
timestamp_column — столбец, используемый для измерения времени, прошедшего между текущей и предыдущей строкой в порядке окна. Не задаёт порядок строк — это делает ORDER BY окна; как правило, в нём следует использовать этот же столбец. DateTime или DateTime64.
INTERVAL X UNITS — необязательный параметр. Единица времени, к которой масштабируется результат. По умолчанию INTERVAL 1 SECOND. Поддерживаются только единицы фиксированной длины (NANOSECOND, MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK); единицы переменной длины (MONTH, QUARTER, YEAR) приводят к генерации исключения.

Возвращаемое значение Для каждой строки значение вычисляется следующим образом:

0 для первой строки;
0 для любой строки, у которой время, прошедшее с момента предыдущей строки, неположительно (то есть $\text{timestamp}_i - \text{timestamp}_{i-1} \le 0$ , что происходит при убывающем порядке или совпадающих временных метках);
${\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval}$ во всех остальных случаях.

Если вычисленное значение отрицательно, оно обнуляется. Тип возвращаемого значения — Float64. Пример В следующем примере вычисляется скорость изменения показаний датчика в секунду. Обратите внимание, что в третьей строке значение уменьшается с 110 до 105, поэтому производная для неё обнуляется.

Query

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);

Query

SELECT
    ts,
    reading,
    nonNegativeDerivative(reading, ts) OVER (ORDER BY ts ASC) AS deriv_per_second
FROM sensor_readings
ORDER BY ts ASC;

Response

   ┌──────────────────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 │
   └─────────────────────┴─────────┴──────────────────┘