> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > 用于对时间序列数据重新采样，以计算类似 PromQL 的 irate 和 idelta 的聚合函数 # timeSeriesLastTwoSamples
## timeSeriesLastTwoSamples
引入于：v25.6.0 用于对时间序列数据重新采样，以执行类似 PromQL 的 irate 和 idelta 计算的聚合函数。 该聚合函数接收由时间戳和值组成的时间序列数据对，并且最多仅保留最近 2 个样本。该聚合函数旨在与 materialized view 和聚合表配合使用，后者用于存储按网格对齐的时间戳对应的重采样时间序列数据。 聚合表对于每个对齐后的时间戳仅存储最后 2 个值。这样就可以通过读取远少于原始表中存储的数据，来计算类似 PromQL 的 `irate` 和 `idelta`。 此函数为 Experimental，可通过设置 `allow_experimental_ts_to_grid_aggregate_function=true` 启用。 **语法** ```sql theme={null} timeSeriesLastTwoSamples(timestamp, value) ``` **参数** * `timestamp` — 样本的时间戳。[`DateTime`](/zh/reference/data-types/datetime) 或 [`DateTime64`](/zh/reference/data-types/datetime64) 或 [`(U)Int*`](/zh/reference/data-types/int-uint) 或 [`Int*`](/zh/reference/data-types/int-uint) * `value` — 与该时间戳对应的时间序列值。[`Float32`](/zh/reference/data-types/float) 或 [`Float64`](/zh/reference/data-types/float) **返回值** 返回一对长度相同的数组，每个数组的长度为 0 到 2。第一个数组包含采样后时间序列的时间戳，第二个数组包含对应的时间序列值。[`Tuple(Array(DateTime), Array(Float64))`](/zh/reference/data-types/tuple) **示例** **原始数据示例表，以及一个用于存储重采样后数据的表** ```sql title=Query theme={null} -- 原始数据表 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 title=Response theme={null} 3 2024-12-12 12:00:12.870 29 3 2024-12-12 12:00:13.770 8 3 2024-12-12 12:00:14.670 19 3 2024-12-12 12:00:15.570 30 3 2024-12-12 12:00:16.470 9 3 2024-12-12 12:00:17.370 20 3 2024-12-12 12:00:18.270 2 3 2024-12-12 12:00:19.170 10 3 2024-12-12 12:00:20.070 21 3 2024-12-12 12:00:20.970 3 3 2024-12-12 12:00:21.870 11 3 2024-12-12 12:00:22.770 22 3 2024-12-12 12:00:23.670 4 3 2024-12-12 12:00:24.570 12 3 2024-12-12 12:00:25.470 23 3 2024-12-12 12:00:26.370 5 3 2024-12-12 12:00:27.270 13 3 2024-12-12 12:00:28.170 24 3 2024-12-12 12:00:29.069 6 3 2024-12-12 12:00:29.969 14 3 2024-12-12 12:00:30.869 25 ``` **查询时间戳 '2024-12-12 12:00:15' 和 '2024-12-12 12:00:30' 的最近 2 个样本** ```sql title=Query theme={null} -- 检查重采样数据 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 title=Response theme={null} 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** ```sql title=Query theme={null} -- 聚合表仅存储每个 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 title=Response theme={null} 3 [11,8,-18,8,11] [12.222222222222221,8.88888888888889,1.1111111111111112,8.88888888888889,12.222222222222221] ``` **基于重采样后的数据计算 idelta 和 irate** ```sql title=Query theme={null} 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 title=Response theme={null} 3 [11,8,-18,8,11] [12.222222222222221,8.88888888888889,1.1111111111111112,8.88888888888889,12.222222222222221] ```