timeSeriesLastTwoSamples

引入于：v25.6.0 用于对时间序列数据重新采样，以执行类似 PromQL 的 irate 和 idelta 计算的聚合函数。该聚合函数接收由时间戳和值组成的时间序列数据对，并且最多仅保留最近 2 个样本。该聚合函数旨在与 materialized view 和聚合表配合使用，后者用于存储按网格对齐的时间戳对应的重采样时间序列数据。聚合表对于每个对齐后的时间戳仅存储最后 2 个值。这样就可以通过读取远少于原始表中存储的数据，来计算类似 PromQL 的 irate 和 idelta。

此函数为 Experimental，可通过设置 allow_experimental_ts_to_grid_aggregate_function=true 启用。

语法

timeSeriesLastTwoSamples(timestamp, value)

参数

timestamp — 样本的时间戳。DateTime 或 DateTime64 或 (U)Int* 或 Int*
value — 与该时间戳对应的时间序列值。Float32 或 Float64

返回值 返回一对长度相同的数组，每个数组的长度为 0 到 2。第一个数组包含采样后时间序列的时间戳，第二个数组包含对应的时间序列值。Tuple(Array(DateTime), Array(Float64)) 示例 原始数据示例表，以及一个用于存储重采样后数据的表

Query

-- 原始数据表
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);

-- 将数据重采样到更大时间步长（15 秒）的表
CREATE TABLE t_resampled_timeseries_15_sec
(
    metric_id UInt64,
    grid_timestamp DateTime('UTC') CODEC(DoubleDelta, ZSTD), -- 对齐到 15 秒的时间戳
    samples AggregateFunction(timeSeriesLastTwoSamples, DateTime64(3, 'UTC'), Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (metric_id, grid_timestamp);

-- 用于填充重采样表的物化视图
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, -- 将时间戳向上取整到下一个网格点
    initializeAggregation('timeSeriesLastTwoSamplesState', timestamp, value) AS samples
FROM t_raw_timeseries
ORDER BY metric_id, grid_timestamp;

-- 插入一些数据
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);

-- 查看原始数据
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

查询时间戳 ‘2024-12-12 12:00:15’ 和 ‘2024-12-12 12:00:30’ 的最近 2 个样本

Query

-- 检查重采样数据
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])

从原始数据计算 idelta 和 irate

Query

-- 聚合表仅存储每个 15 秒对齐时间戳的最后 2 个值。
-- 这样可以通过读取远少于原始表中存储的数据来计算类似 PromQL 的 irate 和 idelta。

WITH
    '2024-12-12 12:00:15'::DateTime64(3,'UTC') AS start_ts,       -- 时间戳网格的起始点
    start_ts + INTERVAL 60 SECOND AS end_ts,   -- 时间戳网格的结束点
    15 AS step_seconds,   -- 时间戳网格的步长
    45 AS window_seconds  -- "staleness" 窗口
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]

基于重采样后的数据计算 idelta 和 irate

Query

WITH
    '2024-12-12 12:00:15'::DateTime64(3,'UTC') AS start_ts,       -- 时间戳网格的起始点
    start_ts + INTERVAL 60 SECOND AS end_ts,   -- 时间戳网格的结束点
    15 AS step_seconds,   -- 时间戳网格的步长
    45 AS window_seconds  -- "staleness" 窗口
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