Skip to content
ClickHouse Docs
49.6kGet Started

timeSeriesLinearRegressionToGrid

View as MarkdownEdit this pageReport an issueSet up docs MCP

Autogenerated from ClickHouse system tables

Introduced in: v26.9.0

Aggregate function that takes time series data as pairs of timestamps and values and fits a line to the values on a regular time grid described by start timestamp, end timestamp and step. For each point on the grid the samples within the specified time window are fitted by a line, and the function returns the tuple (intercept, slope): intercept is the value of that line at the grid point’s timestamp and slope is its slope per second. Both are NULL if there are not enough samples in the window.

The result of timeSeriesPredictLinearToGrid with the offset t equals intercept + slope * t, and the result of timeSeriesDerivToGrid equals slope. The PromQL function predict_linear is implemented with this function, which allows the prediction offset to differ between grid points, like in predict_linear(v, time()).

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.

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.

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

Syntax

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

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

The tuple (intercept, slope) for each grid point: the value of the fitted line at the grid point’s timestamp and its slope per second. Both elements are NULL if there are not enough samples within the window for a particular grid point. Array(Tuple(intercept Nullable(Float64), slope Nullable(Float64)))

Examples

Fit a line on the grid [100, 110, 120] and compare with timeSeriesPredictLinearToGrid and timeSeriesDerivToGrid

SET enable_time_series_aggregate_functions = 1;
WITH
    [100, 110, 120]::Array(DateTime) AS timestamps,
    [10, 20, 30]::Array(Float64) AS values,
    timeSeriesLinearRegressionToGrid(100, 120, 10, 30)(timestamps, values) AS regression
SELECT
    regression,
    arrayMap(r -> r.intercept + r.slope * 60, regression) AS predict_linear_60,
    arrayMap(r -> r.slope, regression) AS deriv;
┌─regression──────────────────┬─predict_linear_60─┬─deriv──────┐
│ [(NULL,NULL),(20,1),(30,1)] │ [NULL,80,90]      │ [NULL,1,1] │
└─────────────────────────────┴───────────────────┴────────────┘
Navigation