> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-vortex-format.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Агрегатная функция для повторного сэмплирования данных временных рядов при вычислении irate и idelta в стиле PromQL

# timeSeriesLastTwoSamples

<div id="timeSeriesLastTwoSamples">
  ## timeSeriesLastTwoSamples
</div>

Добавленный в: v25.6.0

Агрегатная функция для повторного сэмплирования данных временных рядов при вычислении `irate` и `idelta` в стиле PromQL.

Агрегатная функция, которая принимает данные временных рядов в виде пар временных меток и значений и хранит не более двух последних выборок. Эта агрегатная функция предназначена для использования с materialized view и агрегированной таблицей, в которой хранятся повторно сэмплированные данные временных рядов для временных меток, выровненных по сетке.

Агрегированная таблица хранит только два последних значения для каждой выровненной временной метки. Это позволяет вычислять `irate` и `idelta` в стиле PromQL, считывая гораздо меньше данных, чем хранится в исходной таблице.

<Warning>
  Эта функция экспериментальная; включите её, установив `allow_experimental_ts_to_grid_aggregate_function=true`.
</Warning>

**Синтаксис**

```sql theme={null}
timeSeriesLastTwoSamples(timestamp, value)
```

**Аргументы**

* `timestamp` — Временная метка выборки. [`DateTime`](/ru/reference/data-types/datetime) или [`DateTime64`](/ru/reference/data-types/datetime64) или [`(U)Int*`](/ru/reference/data-types/int-uint) или [`Int*`](/ru/reference/data-types/int-uint)
* `value` — Значение временного ряда, соответствующее этой временной метке. [`Float32`](/ru/reference/data-types/float) или [`Float64`](/ru/reference/data-types/float)

**Возвращаемое значение**

Возвращает пару массивов одинаковой длины — от 0 до 2 элементов. Первый массив содержит временные метки выборок временного ряда, второй — соответствующие значения временного ряда. [`Tuple(Array(DateTime), Array(Float64))`](/ru/reference/data-types/tuple)

**Примеры**

**Пример таблицы для исходных данных и таблицы для хранения повторно сэмплированных данных**

```sql title=Query theme={null}
SET allow_experimental_time_series_aggregate_functions = 1;
-- Table for raw data
DROP TABLE IF EXISTS t_raw_timeseries;

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
DROP TABLE IF EXISTS t_resampled_timeseries_15_sec;

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
DROP VIEW IF EXISTS mv_resampled_timeseries;

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 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
```

**Запросить 2 последние выборки для временных меток '2024-12-12 12:00:15' и '2024-12-12 12:00:30'**

```sql title=Query theme={null}
SET allow_experimental_time_series_aggregate_functions = 1;
-- Table for raw data
DROP TABLE IF EXISTS t_raw_timeseries;

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
DROP TABLE IF EXISTS t_resampled_timeseries_15_sec;

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
DROP VIEW IF EXISTS mv_resampled_timeseries;

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 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 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}
SET allow_experimental_time_series_aggregate_functions = 1;
-- Table for raw data
DROP TABLE IF EXISTS t_raw_timeseries;

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);

-- 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);

-- 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 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}
SET allow_experimental_time_series_aggregate_functions = 1;
-- Table for raw data
DROP TABLE IF EXISTS t_raw_timeseries;

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
DROP TABLE IF EXISTS t_resampled_timeseries_15_sec;

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
DROP VIEW IF EXISTS mv_resampled_timeseries;

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);

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 title=Response theme={null}
3	[11,8,-18,8,11]	[12.222222222222221,8.88888888888889,1.1111111111111112,8.88888888888889,12.222222222222221]
```