timeSeriesPredictLinearToGrid

Introduced in: v25.6.0 Aggregate function that takes time series data as pairs of timestamps and values and calculates a PromQL-like linear prediction with a specified prediction timestamp offset from this data on a regular time grid described by start timestamp, end timestamp and step. For each point on the grid the samples for calculating predict_linear are considered within the specified time window. 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 experimental, enable it by setting allow_experimental_time_series_aggregate_functions=true.

Syntax

timeSeriesPredictLinearToGrid(start_timestamp, end_timestamp, grid_step, staleness, predict_offset)(timestamp, value)

Parameters

start_timestamp — Specifies start of the grid. With a DateTime64 timestamp argument 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. With a DateTime64 timestamp argument 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. With a DateTime64 timestamp argument 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. With a DateTime64 timestamp argument 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
predict_offset — Specifies number of seconds of offset to add to prediction time. UInt32 or Float* or Decimal* or String

Arguments

timestamp — Timestamp of the sample. Can be individual values or arrays. - value — Value of the time series corresponding to the timestamp. Can be individual values or arrays.

Returned value predict_linear values on the specified grid as an Array(Nullable(Float64)). The returned array contains one value for each time grid point. The value is NULL if there are not enough samples within the window to calculate the rate value for a particular grid point. Examples Calculate predict_linear values on the grid [90, 105, 120, 135, 150, 165, 180, 195, 210] with a 60 second offset

Query

SET allow_experimental_time_series_aggregate_functions = 1;
WITH
    -- NOTE: the gap between 140 and 190 is to show how values are filled for ts = 150, 165, 180 according to window parameter
    [110, 120, 130, 140, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 1, 3, 4, 5, 5, 8, 12, 13]::Array(Float32) AS values, -- array of values corresponding to timestamps above
    90 AS start_ts,       -- start of timestamp grid
    90 + 120 AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    45 AS window_seconds, -- "staleness" window
    60 AS predict_offset  -- prediction time offset
SELECT timeSeriesPredictLinearToGrid(start_ts, end_ts, step_seconds, window_seconds, predict_offset)(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

┌─timeSeriesPredictLinearToGrid(start_ts, end_ts, step_seconds, window_seconds, predict_offset)(timestamp, value)─┐
│ [NULL,NULL,1,9.166667,11.6,16.916666,NULL,NULL,16.5]                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Same query with array arguments

Query

SET allow_experimental_time_series_aggregate_functions = 1;
WITH
    [110, 120, 130, 140, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 1, 3, 4, 5, 5, 8, 12, 13]::Array(Float32) AS values,
    90 AS start_ts,
    90 + 120 AS end_ts,
    15 AS step_seconds,
    45 AS window_seconds,
    60 AS predict_offset
SELECT timeSeriesPredictLinearToGrid(start_ts, end_ts, step_seconds, window_seconds, predict_offset)(timestamps, values);

Response

┌─timeSeriesPredictLinearToGrid(start_ts, end_ts, step_seconds, window_seconds, predict_offset)(timestamp, value)─┐
│ [NULL,NULL,1,9.166667,11.6,16.916666,NULL,NULL,16.5]                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

timeSeriesPredictLinearToGrid

timeSeriesPredictLinearToGrid

​timeSeriesPredictLinearToGrid

timeSeriesPredictLinearToGrid