timeSeriesGroupArray

Introduced in: v25.8.0

Sorts time series data by timestamp in ascending order.

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. This form has the same type as the result, which allows using the function in the SimpleAggregateFunction data type.

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.

Syntax

timeSeriesGroupArray(timestamp, value)
timeSeriesGroupArray(samples)

Arguments

timestamp — Timestamp of the sample. Can be an individual value or an array. 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 an individual value or an array. 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. Array(Tuple(T1, T2))

Returned value

Returns an array of tuples (timestamp, value) sorted by timestamp in ascending order. Array(Tuple(T1, T2))

Examples

Basic usage with individual values

SET allow_experimental_time_series_aggregate_functions = 1;
WITH
    [110, 120, 130, 140, 140, 100]::Array(UInt32) AS timestamps,
    [1, 6, 8, 17, 19, 5]::Array(Float32) AS values
SELECT timeSeriesGroupArray(timestamp, value)
FROM
(
    SELECT
        arrayJoin(arrayZip(timestamps, values)) AS ts_and_val,
        ts_and_val.1 AS timestamp,
        ts_and_val.2 AS value
);

┌─timeSeriesGroupArray(timestamp, value)─────┐
│ [(100,5),(110,1),(120,6),(130,8),(140,19)] │
└────────────────────────────────────────────┘

Passing multiple samples of timestamps and values as arrays of equal size

SET allow_experimental_time_series_aggregate_functions = 1;
WITH
    [110, 120, 130, 140, 140, 100]::Array(UInt32) AS timestamps,
    [1, 6, 8, 17, 19, 5]::Array(Float32) AS values
SELECT timeSeriesGroupArray(timestamps, values);

┌─timeSeriesGroupArray(timestamps, values)───┐
│ [(100,5),(110,1),(120,6),(130,8),(140,19)] │
└────────────────────────────────────────────┘