nonNegativeDerivative - ClickHouse Documentation

Calcula la derivada no negativa de metric_column con respecto a timestamp_column. Esta es una función de ventana específica de ClickHouse y no forma parte del SQL estándar. Para cada fila, la derivada se calcula con respecto a la fila anterior en el orden de evaluación de la ventana, que está determinado por la cláusula ORDER BY de la ventana, no por timestamp_column. El argumento timestamp_column se lee únicamente para medir el tiempo transcurrido entre la fila actual y la fila anterior; no determina el orden de las filas por sí mismo.

nonNegativeDerivative no ordena las filas por timestamp_column; eso lo hace el ORDER BY de la ventana. Para que la fórmula que se muestra a continuación sea aplicable, timestamp_column debe ser estrictamente creciente en el orden de evaluación de la ventana, por lo que normalmente se debe ordenar la ventana por timestamp_column de forma ascendente (por ejemplo, ... OVER (ORDER BY ts ASC) junto con nonNegativeDerivative(metric, ts)). Cuando el tiempo transcurrido entre la fila actual y la fila anterior sea no positivo —lo cual ocurre con ORDER BY timestamp_column DESC o con timestamps duplicados (iguales)— la función devuelve 0 para esa fila en lugar de aplicar la fórmula.

El resultado es la tasa de cambio de la métrica por INTERVAL, donde cualquier valor negativo se fija en 0. Esto resulta útil para métricas monotónicamente crecientes, como los contadores, donde una disminución generalmente indica un reinicio en lugar de una tasa negativa real. Sintaxis

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

Para más detalles sobre la sintaxis de las funciones de ventana, consulte: Window Functions - Syntax. Argumentos

metric_column — La columna cuya derivada se calcula. (U)Int* o Float*.
timestamp_column — La columna utilizada para medir el tiempo transcurrido entre la fila actual y la fila anterior en el orden de la ventana. No determina el orden de las filas; eso lo hace el ORDER BY de la ventana, que normalmente debería usar esta misma columna. DateTime o DateTime64.
INTERVAL X UNITS — Opcional. La unidad de tiempo a la que se escala el resultado. El valor predeterminado es INTERVAL 1 SECOND. Solo se admiten unidades de longitud fija (NANOSECOND, MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK); las unidades de longitud variable (MONTH, QUARTER, YEAR) generan una excepción.

Valor devuelto Para cada fila, el valor se calcula de la siguiente manera:

0 para la primera fila;
0 para cualquier fila cuyo tiempo transcurrido desde la fila anterior sea no positivo (es decir, $\text{timestamp}_i - \text{timestamp}_{i-1} \le 0$ , como ocurre con el orden descendente o timestamps duplicados); y
${\text{metric}_i - \text{metric}_{i-1} \over \text{timestamp}_i - \text{timestamp}_{i-1}} * \text{interval}$ en caso contrario.

Si el valor calculado resultara negativo, se fija en 0. El tipo de retorno es Float64. Ejemplo El siguiente ejemplo calcula la tasa de cambio por segundo de una lectura de sensor. Nótese que la tercera fila desciende de 110 a 105, por lo que su derivada se fija en 0.

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