timeSeriesLastTwoSamples

Introduit dans : v25.6.0 Fonction d’agrégation pour le rééchantillonnage de séries temporelles, destinée au calcul de irate et idelta de type PromQL. Fonction d’agrégation qui prend des séries temporelles sous forme de paires d’horodatages et de valeurs, et ne conserve qu’au plus 2 échantillons récents. Cette fonction d’agrégation est conçue pour être utilisée avec une vue matérialisée et une table agrégée qui stocke des séries temporelles rééchantillonnées pour des horodatages alignés sur la grille. La table agrégée ne stocke que les 2 dernières valeurs pour chaque horodatage aligné. Cela permet de calculer irate et idelta de type PromQL en lisant beaucoup moins de données que dans la table brute.

Cette fonction est expérimentale. Activez-la en définissant allow_experimental_ts_to_grid_aggregate_function=true.

Syntaxe

timeSeriesLastTwoSamples(timestamp, value)

Arguments

timestamp — Horodatage de l’échantillon. DateTime ou DateTime64 ou (U)Int* ou Int*
value — Valeur de la série temporelle correspondant à l’horodatage. Float32 ou Float64

Valeur renvoyée Renvoie une paire de tableaux de même longueur, comprise entre 0 et 2. Le premier tableau contient les horodatages des séries temporelles échantillonnées, le second contient les valeurs correspondantes de la série temporelle. Tuple(Array(DateTime), Array(Float64)) Exemples Exemple de table pour les données brutes et de table pour stocker les données rééchantillonnées

Query

-- Table for raw data
CREATE TABLE t_raw_timeseries
(
    metric_id UInt64,
    timestamp DateTime64(3, 'UTC') CODEC(DoubleDelta, ZSTD),
    value Float64 CODEC(DoubleDelta)
)
ENGINE = MergeTree()
ORDER BY (metric_id, timestamp);

-- Table with data re-sampled to bigger (15 sec) time steps
CREATE TABLE t_resampled_timeseries_15_sec
(
    metric_id UInt64,
    grid_timestamp DateTime('UTC') CODEC(DoubleDelta, ZSTD), -- Timestamp aligned to 15 sec
    samples AggregateFunction(timeSeriesLastTwoSamples, DateTime64(3, 'UTC'), Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (metric_id, grid_timestamp);

-- MV for populating re-sampled table
CREATE MATERIALIZED VIEW mv_resampled_timeseries TO t_resampled_timeseries_15_sec
(
    metric_id UInt64,
    grid_timestamp DateTime('UTC') CODEC(DoubleDelta, ZSTD),
    samples AggregateFunction(timeSeriesLastTwoSamples, DateTime64(3, 'UTC'), Float64)
)
AS SELECT
    metric_id,
    ceil(toUnixTimestamp(timestamp + interval 999 millisecond) / 15, 0) * 15 AS grid_timestamp, -- Round timestamp up to the next grid point
    initializeAggregation('timeSeriesLastTwoSamplesState', timestamp, value) AS samples
FROM t_raw_timeseries
ORDER BY metric_id, grid_timestamp;

-- Insert some data
INSERT INTO t_raw_timeseries(metric_id, timestamp, value) SELECT number%10 AS metric_id, '2024-12-12 12:00:00'::DateTime64(3, 'UTC') + interval ((number/10)%100)*900 millisecond as timestamp, number%3+number%29 AS value FROM numbers(1000);

-- Check raw data
SELECT *
FROM t_raw_timeseries
WHERE metric_id = 3 AND timestamp BETWEEN '2024-12-12 12:00:12' AND '2024-12-12 12:00:31'
ORDER BY metric_id, timestamp;

Response

  2024-12-12 12:00:12.870    29
  2024-12-12 12:00:13.770    8
  2024-12-12 12:00:14.670    19
  2024-12-12 12:00:15.570    30
  2024-12-12 12:00:16.470    9
  2024-12-12 12:00:17.370    20
  2024-12-12 12:00:18.270    2
  2024-12-12 12:00:19.170    10
  2024-12-12 12:00:20.070    21
  2024-12-12 12:00:20.970    3
  2024-12-12 12:00:21.870    11
  2024-12-12 12:00:22.770    22
  2024-12-12 12:00:23.670    4
  2024-12-12 12:00:24.570    12
  2024-12-12 12:00:25.470    23
  2024-12-12 12:00:26.370    5
  2024-12-12 12:00:27.270    13
  2024-12-12 12:00:28.170    24
  2024-12-12 12:00:29.069    6
  2024-12-12 12:00:29.969    14
  2024-12-12 12:00:30.869    25

Interroger les 2 derniers échantillons aux horodatages ‘2024-12-12 12:00:15’ et ‘2024-12-12 12:00:30’

Query

-- Check re-sampled data
SELECT metric_id, grid_timestamp, (finalizeAggregation(samples).1 as timestamp, finalizeAggregation(samples).2 as value)
FROM t_resampled_timeseries_15_sec
WHERE metric_id = 3 AND grid_timestamp BETWEEN '2024-12-12 12:00:15' AND '2024-12-12 12:00:30'
ORDER BY metric_id, grid_timestamp;

Response

3    2024-12-12 12:00:15    (['2024-12-12 12:00:14.670','2024-12-12 12:00:13.770'],[19,8])
3    2024-12-12 12:00:30    (['2024-12-12 12:00:29.969','2024-12-12 12:00:29.069'],[14,6])

Calculer idelta et irate à partir des données brutes

Query

-- The aggregated table stores only last 2 values for each 15-second aligned timestamp.
-- This allows to calculate PromQL-like irate and idelta by reading much less data then is stored in the raw table.

WITH
    '2024-12-12 12:00:15'::DateTime64(3,'UTC') AS start_ts,       -- start of timestamp grid
    start_ts + INTERVAL 60 SECOND AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    45 AS window_seconds  -- "staleness" window
SELECT
    metric_id,
    timeSeriesInstantDeltaToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value),
    timeSeriesInstantRateToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)
FROM t_raw_timeseries
WHERE metric_id = 3 AND timestamp BETWEEN start_ts - interval window_seconds seconds AND end_ts
GROUP BY metric_id;

Response

3    [11,8,-18,8,11]    [12.222222222222221,8.88888888888889,1.1111111111111112,8.88888888888889,12.222222222222221]

Calculer idelta et irate à partir des données rééchantillonnées

Query

WITH
    '2024-12-12 12:00:15'::DateTime64(3,'UTC') AS start_ts,       -- start of timestamp grid
    start_ts + INTERVAL 60 SECOND AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    45 AS window_seconds  -- "staleness" window
SELECT
    metric_id,
    timeSeriesInstantDeltaToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values),
    timeSeriesInstantRateToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values)
FROM (
    SELECT
        metric_id,
        finalizeAggregation(samples).1 AS timestamps,
        finalizeAggregation(samples).2 AS values
    FROM t_resampled_timeseries_15_sec
    WHERE metric_id = 3 AND grid_timestamp BETWEEN start_ts - interval window_seconds seconds AND end_ts
)
GROUP BY metric_id;

Response

3    [11,8,-18,8,11]    [12.222222222222221,8.88888888888889,1.1111111111111112,8.88888888888889,12.222222222222221]

​timeSeriesLastTwoSamples

timeSeriesLastTwoSamples