Time series functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/time-series-functions.md)

The functions below are designed to be used with `timeSeries*()` aggregate functions like
[`timeSeriesInstantRateToGrid`](/docs/sql-reference/aggregate-functions/reference/timeSeriesInstantRateToGrid),
[`timeSeriesLastToGrid`](/docs/sql-reference/aggregate-functions/reference/timeSeriesResampleToGridWithStaleness),
and so on.

## seriesDecomposeSTL[​](#seriesDecomposeSTL "Direct link to seriesDecomposeSTL")

Introduced in: v24.1.0

Decomposes a series data using STL [(Seasonal-Trend Decomposition Procedure Based on Loess)](https://www.wessa.net/download/stl.pdf) into a season, a trend and a residual component.

**Syntax**

```
seriesDecomposeSTL(series, period)
```

**Arguments**

- `series` — An array of numeric values [`Array((U)Int8/16/32/64)`](/docs/sql-reference/data-types/array) or [`Array(Float*)`](/docs/sql-reference/data-types/array)
- `period` — A positive integer [`UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of four arrays where the first array includes seasonal components, the second array - trend, the third array - residue component, and the fourth array - baseline(seasonal + trend) component. [`Array(Array(Float32), Array(Float32), Array(Float32), Array(Float32))`](/docs/sql-reference/data-types/array)

**Examples**

**Decompose series data using STL**

```
SELECT seriesDecomposeSTL([10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34], 3) AS print_0
```

```
┌───────────print_0──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [[
        -13.529999, -3.1799996, 16.71,      -13.53,     -3.1799996, 16.71,      -13.53,     -3.1799996,
        16.71,      -13.530001, -3.18,      16.710001,  -13.530001, -3.1800003, 16.710001,  -13.530001,
        -3.1800003, 16.710001,  -13.530001, -3.1799994, 16.71,      -13.529999, -3.1799994, 16.709997
    ],
    [
        23.63,     23.63,     23.630003, 23.630001, 23.630001, 23.630001, 23.630001, 23.630001,
        23.630001, 23.630001, 23.630001, 23.63,     23.630001, 23.630001, 23.63,     23.630001,
        23.630001, 23.63,     23.630001, 23.630001, 23.630001, 23.630001, 23.630001, 23.630003
    ],
    [
        0, 0.0000019073486, -0.0000019073486, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -0.0000019073486, 0,
        0
    ],
    [
        10.1, 20.449999, 40.340004, 10.100001, 20.45, 40.34, 10.100001, 20.45, 40.34, 10.1, 20.45, 40.34,
        10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.100002, 20.45, 40.34
    ]]                                                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

## seriesOutliersDetectTukey[​](#seriesOutliersDetectTukey "Direct link to seriesOutliersDetectTukey")

Introduced in: v24.2.0

Detects outliers in series data using [Tukey Fences](https://en.wikipedia.org/wiki/Outlier#Tukey%27s_fences).

**Syntax**

```
seriesOutliersDetectTukey(series[, min_percentile, max_percentile, K])
```

**Arguments**

- `series` — An array of numeric values. [`Array((UInt8/16/32/64))`](/docs/sql-reference/data-types/array) or [`Array(Float*)`](/docs/sql-reference/data-types/array)
- `min_percentile` — Optional. The minimum percentile to be used to calculate inter-quantile range [(IQR)](https://en.wikipedia.org/wiki/Interquartile_range). The value must be in range [0.02,0.98]. The default is 0.25. [`Float*`](/docs/sql-reference/data-types/float)
- `max_percentile` — Optional. The maximum percentile to be used to calculate inter-quantile range (IQR). The value must be in range [0.02,0.98]. The default is 0.75. [`Float*`](/docs/sql-reference/data-types/float)
- `K` — Optional. Non-negative constant value to detect mild or stronger outliers. The default value is 1.5. [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns an array of the same length as the input array where each value represents score of possible anomaly of corresponding element in the series. A non-zero score indicates a possible anomaly. [`Array(Float32)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic outlier detection**

```
SELECT seriesOutliersDetectTukey([-3, 2, 15, 3, 5, 6, 4, 5, 12, 45, 12, 3, 3, 4, 5, 6]) AS print_0
```

```
┌───────────print_0─────────────────┐
│[0,0,0,0,0,0,0,0,0,27,0,0,0,0,0,0] │
└───────────────────────────────────┘
```

**Custom parameters outlier detection**

```
SELECT seriesOutliersDetectTukey([-3, 2, 15, 3, 5, 6, 4.50, 5, 12, 45, 12, 3.40, 3, 4, 5, 6], 0.2, 0.8, 1.5) AS print_0
```

```
┌─print_0──────────────────────────────┐
│ [0,0,0,0,0,0,0,0,0,19.5,0,0,0,0,0,0] │
└──────────────────────────────────────┘
```

## seriesPeriodDetectFFT[​](#seriesPeriodDetectFFT "Direct link to seriesPeriodDetectFFT")

Introduced in: v23.12.0

Finds the period of the given series data using FFT - [Fast Fourier transform](https://en.wikipedia.org/wiki/Fast_Fourier_transform)

**Syntax**

```
seriesPeriodDetectFFT(series)
```

**Arguments**

- `series` — An array of numeric values. [`Array((U)Int8/16/32/64)`](/docs/sql-reference/data-types/array) or [`Array(Float*)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a real value equal to the period of series data. NaN when number of data points are less than four. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Period detection with simple pattern**

```
SELECT seriesPeriodDetectFFT([1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6]) AS print_0
```

```
┌───────────print_0──────┐
│                      3 │
└────────────────────────┘
```

**Period detection with complex pattern**

```
SELECT seriesPeriodDetectFFT(arrayMap(x -> abs((x % 6) - 3), range(1000))) AS print_0
```

```
┌─print_0─┐
│       6 │
└─────────┘
```

## timeSeriesCopyTag[​](#timeSeriesCopyTag "Direct link to timeSeriesCopyTag")

Introduced in: v26.1.0

Copies a specified tag from one group of tags (`src_group`) to another (`dest_group`).
The function replaces any previous values of the copied tag in `dest_group`.
If the copied tag is not present in `src_group`, then the function will remove it from `dest_group` as well.
The function mimics the copying logic of the prometheus
[group left/group right](https://prometheus.io/docs/prometheus/latest/querying/operators/#group-modifiers) modifiers.

**Syntax**

```
timeSeriesCopyTag(dest_group, src_group, tag_to_copy)
```

**Arguments**

- `dest_group` — The destination group of tags. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `src_group` — The source group of tags. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `tag_to_copy` — The name of a tag to copy. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a group of tags containing the tags from `dest_group` along with the copied tags from `src_group`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Example**

```
SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS dest_group,
       timeSeriesTagsToGroup([('code', '404'), ('message', 'Page not found')], '__name__', 'http_codes') AS src_group,
       timeSeriesCopyTag(dest_group, src_group, '__name__') AS result_group,
       timeSeriesGroupToTags(result_group)
```

```
┌─dest_group─┬─src_group─┬─result_group─┬─timeSeriesGroupToTags(result_group)────────────────────────┐
│          1 │         2 │            3 │ [('__name__','http_codes'),('code','404'),('region','eu')] │
└────────────┴───────────┴──────────────┴────────────────────────────────────────────────────────────┘
```

## timeSeriesCopyTags[​](#timeSeriesCopyTags "Direct link to timeSeriesCopyTags")

Introduced in: v26.1.0