Time series functions analyze and manipulate time-based data, enabling:

  • Outlier detection
  • Trend analysis
  • Data aggregation over time intervals

These functions extract insights from temporal datasets like financial data, IoT sensor readings, and other time-stamped information.

Time Series API

Explore our Time Series API for easily fetching ready-to-chart time series data.

​
ClickHouse function reference

​
seriesOutliersDetectTukey

Detects outliers in series data using Tukey’s fences method.

Syntax:

seriesOutliersDetectTukey(series)
seriesOutliersDetectTukey(series, min_percentile, max_percentile, K)

Arguments:

  • series (Array of numeric values): The input series data.
  • min_percentile (Float64, optional): The minimum percentile for calculating the interquartile range (IQR). Default: 0.25. Must be in range [0.02, 0.98].
  • max_percentile (Float64, optional): The maximum percentile for calculating the IQR. Default: 0.75. Must be in range [0.02, 0.98].
  • K (Float64, optional): Non-negative constant to detect mild or stronger outliers. Default: 1.5.

Returns:

An array of the same length as the input, where each value represents the anomaly score of the corresponding element. A non-zero score indicates a possible anomaly. [Array(Float64)]

  • At least four data points are required in the series to detect outliers.
  • The function uses the interquartile range (IQR) method to identify outliers.

Example:

SELECT
	seriesOutliersDetectTukey([1, 2, 3, 4, 5, 100, 6, 7, 8, 9, 10]) AS outlier_scores
FROM
	taco_sales;

Result:

| outlier_scores                              |
|---------------------------------------------|
| [0, 0, 0, 0, 0, 67.5, 0, 0, 0, 0, 0]        |

In this example, we analyze taco sales data to detect any unusual spikes. The value 100 is identified as an outlier with a score of 67.5, while all other values have a score of 0, indicating they are within the expected range.

​
seriesPeriodDetectFFT

Finds the period of the given series data using Fast Fourier Transform (FFT).

Syntax:

seriesPeriodDetectFFT(series)

Arguments:

  • series (Array(numeric)): An array of numeric values representing the time series data.

Returns:

A real value equal to the period of the series data. Returns NaN when the number of data points is less than four. [Float64]

Example:

SELECT
	seriesPeriodDetectFFT([1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5]) AS taco_sales_period;

Result:

| taco_sales_period |
|-------------------|
| 5                 |

In this example, we analyze the periodicity of taco sales data. The function detects a period of 5, indicating that the sales pattern repeats every 5 data points.

  • The function requires at least four data points to detect a period.
  • FFT is particularly useful for detecting regular patterns or cycles in time series data, which can be valuable for forecasting or identifying seasonal trends in taco sales or production.

​
seriesDecomposeSTL

Decomposes a time series into seasonal, trend, and residual components using the STL (Seasonal-Trend Decomposition Procedure Based on Loess) algorithm.

Syntax:

seriesDecomposeSTL(series, period)

Arguments:

  • series (Array(numeric)): An array of numeric values representing the time series data.
  • period (UInt32): A positive integer representing the expected periodicity of the seasonal component.

Returns:

An array containing four arrays, each representing a component of the decomposition:

  1. Seasonal component
  2. Trend component
  3. Residual component
  4. Baseline component (seasonal + trend)

The number of data points in the input series should be at least twice the value of the period argument.

Example:

SELECT
	seriesDecomposeSTL(
		[10, 20, 30, 15, 25, 35, 12, 22, 32, 18, 28, 38],
		3
	) AS decomposed_taco_sales;

Result:

| decomposed_taco_sales                                                                     |
|-------------------------------------------------------------------------------------------|
| [                                                                                         |
|   [-5.0, 5.0, 10.0, -5.0, 5.0, 10.0, -5.0, 5.0, 10.0, -5.0, 5.0, 10.0],                   |
|   [15.0, 15.0, 15.0, 20.0, 20.0, 20.0, 25.0, 25.0, 25.0, 23.0, 23.0, 23.0],               |
|   [0.0, 0.0, 5.0, 0.0, 0.0, 5.0, -8.0, -8.0, -3.0, 0.0, 0.0, 5.0],                        |
|   [10.0, 20.0, 25.0, 15.0, 25.0, 30.0, 20.0, 30.0, 35.0, 18.0, 28.0, 33.0]                |
| ]                                                                                         |

In this example, we decompose a series of taco sales data with a period of 3. The result shows:

  1. The seasonal pattern of sales fluctuations
  2. The overall trend of increasing sales
  3. The residual (unexplained) variations
  4. The baseline sales (seasonal + trend)

This decomposition can help identify patterns, trends, and anomalies in your taco sales data.

Was this page helpful?

StringsTuples