Time series functions

These functions are designed for analyzing and extracting insights from time-ordered data.

seriesOutliersDetectTukey

This function identifies potential outliers within a numerical series using the Tukey Fences method. It calculates an anomaly score for each element, indicating how far it deviates from the typical range.

Syntax

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

Arguments

  • series: Array. An array of numeric values representing the time series. The input series must contain at least four data points.
  • min_percentile: Float. The lower percentile used to define the interquartile range (IQR). This value must be between 0.02 and 0.98. The default is 0.25.
  • max_percentile: Float. The upper percentile used to define the interquartile range (IQR). This value must be between 0.02 and 0.98. The default is 0.75.
  • K: Float. A non-negative constant that determines the sensitivity for detecting outliers. A larger K makes the detection less sensitive. The default value is 1.5.

Returns

An array of Float64 values, with the same length as the input series. Each value represents an anomaly score; a non-zero score indicates a potential outlier.

Example

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

Result:

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

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

Result:

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

seriesPeriodDetectFFT

This function estimates the dominant period of a numerical time series using the Fast Fourier Transform (FFT) algorithm. It helps identify recurring patterns or cycles within the data.

Syntax

seriesPeriodDetectFFT(series)

Arguments

  • series: Array. An array of numeric values.

Returns

A Float64 value representing the detected period of the series. Returns NaN if the series has fewer than four data points.

Example

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

Result:

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

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

Result:

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

seriesDecomposeSTL

This function decomposes a time series into its seasonal, trend, and residual components using the Seasonal-Trend Decomposition Procedure Based on Loess (STL) method. It also provides a baseline component which is the sum of seasonal and trend.

Syntax

seriesDecomposeSTL(series, period)

Arguments

  • series: Array. An array of numeric values representing the time series.
  • period: UInt64. A positive integer indicating the length of the seasonal cycle. The number of data points in series must be at least twice the period value.

Returns

An array of four arrays (Array(Array(Float64))). These inner arrays contain:

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

Example

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

Result:

┌───────────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
    ]]                                                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Updated