nonNegativeDerivative

Вычисляет неотрицательную производную столбца 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, при этом любые отрицательные значения обнуляются. Это полезно для монотонно возрастающих метрик, таких как счетчики, где уменьшение значения обычно означает сброс, а не реальную отрицательную скорость. Синтаксис

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

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

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

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes