timeSeriesMadToGrid

Introduced in: v26.10.0 Aggregate function that takes time series data as pairs of timestamps and values and calculates the PromQL mad_over_time function on a regular time grid described by start timestamp, end timestamp and step. For each point on the grid the result is the median absolute deviation median(|x - median(x)|) of the sample values within the specified time window. Both medians are computed using the R-7 (inclusive) method, like quantileExactInclusive and timeSeriesQuantileToGrid. The samples can be passed in one of three forms:

as two arguments timestamp and value, where each row holds a single sample;
as two arrays of timestamps and values, where each row holds a whole time series;
as a single array of (timestamp, value) tuples, where each row holds a whole time series.

Like in Prometheus, the result is NaN if any sample in the window is NaN. It is NaN as well if the median of the window is infinite, because the deviations of the samples equal to it are Inf - Inf. If several samples have the same timestamp, only one of them is used: the sample with the greatest value. A NaN value loses to any other value, so a NaN value is used only if all samples at this timestamp are NaN.

This function is in private preview, enable it by setting enable_time_series_aggregate_functions=true.

Syntax

timeSeriesMadToGrid(start_timestamp, end_timestamp, grid_step, staleness)(timestamp, value)
timeSeriesMadToGrid(start_timestamp, end_timestamp, grid_step, staleness)(samples)

Parameters

start_timestamp — Specifies start of the grid. It can also be a fractional number, or a string containing a number or a date-time text. UInt32 or DateTime or DateTime64 or Float* or Decimal* or String
end_timestamp — Specifies end of the grid. It can also be a fractional number, or a string containing a number or a date-time text. UInt32 or DateTime or DateTime64 or Float* or Decimal* or String
grid_step — Specifies step of the grid in seconds. It can also be a fractional number, or a string containing a number or a duration like ’15s’ or ‘1m’. UInt32 or Float* or Decimal* or String
staleness — Specifies the maximum “staleness” in seconds of the considered samples. The staleness window is a left-open and right-closed interval. It can also be a fractional number, or a string containing a number or a duration like ’15s’ or ‘1m’. UInt32 or Float* or Decimal* or String

Arguments

timestamp — Timestamp of the sample. Can be individual values or arrays. UInt32 or DateTime or DateTime64 or Array(UInt32) or Array(DateTime) or Array(DateTime64)
value — Value of the time series corresponding to the timestamp. Can be individual values or arrays. Float* or Array(Float*)
samples — Samples of the time series passed as an array of tuples (timestamp, value), where the tuple elements have the timestamp and value types listed above. An alternative to passing the timestamps and the values as two separate arguments. Array(Tuple(T1, T2))

Returned value Returns the median absolute deviation of values on the specified grid. The returned array contains one value for each time grid point. The value is NULL if there are no samples within the window for a particular grid point. Array(Nullable(Float64)) Examples Calculate mad_over_time values on the grid [90, 105, 120, 135, 150, 165, 180, 195, 210, 225]

Query

SET enable_time_series_aggregate_functions = 1;
WITH
    -- NOTE: the gap between 130 and 190 is to show how values are filled for ts = 180 according to window parameter
    [110, 120, 130, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 3, 2, 6, 6, 4, 2, 0]::Array(Float32) AS values, -- array of values corresponding to timestamps above
    90 AS start_ts,       -- start of timestamp grid
    90 + 135 AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    45 AS window_seconds  -- "staleness" window
SELECT timeSeriesMadToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)
FROM
(
    -- This subquery converts arrays of timestamps and values into rows of `timestamp`, `value`
    SELECT
        arrayJoin(arrayZip(timestamps, values)) AS ts_and_val,
        ts_and_val.1 AS timestamp,
        ts_and_val.2 AS value
);

Response

┌─timeSeriesMadToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)─┐
│ [NULL,NULL,1,1,1,0,NULL,0,0,1]                                                        │
└───────────────────────────────────────────────────────────────────────────────────────┘

Same query with array arguments

Query

SET enable_time_series_aggregate_functions = 1;
WITH
    [110, 120, 130, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 3, 2, 6, 6, 4, 2, 0]::Array(Float32) AS values,
    90 AS start_ts,
    90 + 135 AS end_ts,
    15 AS step_seconds,
    45 AS window_seconds
SELECT timeSeriesMadToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values);

Response

┌─timeSeriesMadToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values)─┐
│ [NULL,NULL,1,1,1,0,NULL,0,0,1]                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

timeSeriesMadToGrid

timeSeriesMadToGrid

​timeSeriesMadToGrid

timeSeriesMadToGrid