Time-Series: паттерны аналитических запросов

Module 07 покрывал schema design для time-series: порядок столбцов в ORDER BY, codec chains (Delta+ZSTD, Gorilla+LZ4), PARTITION BY для lifecycle management. Здесь — паттерны запросов: как правильно агрегировать по временным интервалам, заполнять пропуски, вычислять скользящие метрики.

toStartOfInterval: bucketing по фиксированным интервалам

Группировка временных событий по фиксированным интервалам — базовый паттерн time-series аналитики.

-- Агрегация по часам
SELECT
    toStartOfHour(ts)    AS hour,
    count()              AS events,
    avg(response_ms)     AS avg_latency
FROM http_requests
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

Семейство функций toStartOf*:

Функция toStartOfInterval наиболее гибкая — принимает произвольный интервал:

-- 15-минутные bucket'ы
SELECT
    toStartOfInterval(ts, INTERVAL 15 MINUTE) AS bucket,
    count()                                    AS requests
FROM http_requests
GROUP BY bucket
ORDER BY bucket;

WITH FILL: заполнение пропусков в временных рядах

Если в некоторые интервалы не было событий, GROUP BY пропустит их. Для дашбордов и графиков нужны непрерывные ряды с нулями на пустых интервалах.

Синтаксис WITH FILL:

-- Полный пример: почасовые события за 7 дней с gap-filling
SELECT
    toStartOfHour(ts)  AS hour,
    count()            AS events
FROM user_events
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour
WITH FILL
    FROM toStartOfHour(now() - INTERVAL 7 DAY)
    TO   toStartOfHour(now())
    STEP INTERVAL 1 HOUR;

WITH FILL гарантирует, что в результате будут все часовые интервалы от FROM до TO с шагом STEP — даже если в эти часы не было событий. Для пустых интервалов значения агрегатных функций будут NULL (для дашбордов их заменяют через coalesce(events, 0)).

groupArrayMovingSum: скользящие суммы

groupArrayMovingSum(N) вычисляет скользящую сумму за N предыдущих значений. Применяется после агрегации по временному интервалу.

-- 7-дневная скользящая сумма revenue
SELECT
    day,
    daily_revenue,
    groupArrayMovingSum(7)(daily_revenue) AS rolling_7d
FROM (
    SELECT
        toStartOfDay(ts)    AS day,
        sum(revenue)        AS daily_revenue
    FROM orders
    GROUP BY day
    ORDER BY day
)
ORDER BY day;

Функции семейства:

neighbor и runningDifference: дельта-ряды

Для вычисления разницы между соседними точками времени используйте neighbor() или runningDifference().

-- Дельта между соседними значениями (прирост за период)
SELECT
    ts,
    value,
    value - neighbor(value, -1) AS delta
FROM metrics
WHERE metric_name = 'requests_total'
ORDER BY ts;

-- runningDifference: накопленный счётчик -> скорость
SELECT
    ts,
    runningDifference(requests_total) AS requests_per_interval
FROM (
    SELECT
        toStartOfMinute(ts) AS ts,
        max(requests_total) AS requests_total
    FROM counters
    GROUP BY ts
    ORDER BY ts
);

neighbor(col, offset) — обращается к значению col в строке на offset позиций выше (отрицательный) или ниже (положительный) в текущем блоке. runningDifference(col) эквивалентно col - neighbor(col, -1) с нулём для первой строки.

Lifecycle: PARTITION BY + TTL

Для time-series данных с ограниченным горизонтом хранения используйте PARTITION BY по временному ключу в сочетании с TTL-правилами. Подробно см. Module 10 (TTL и Tiered Storage).

-- Оперативные метрики: 90 дней на SSD, 1 год на объектном хранилище
CREATE TABLE metrics_hot (
    ts          DateTime64(3) CODEC(Delta, ZSTD(1)),
    metric_name LowCardinality(String),
    value       Float64 CODEC(Gorilla, LZ4)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (metric_name, ts)
TTL ts + INTERVAL 90 DAY TO VOLUME 'cold';

Ключевые выводы

1. toStartOfInterval(ts, INTERVAL N UNIT) — универсальная функция bucketing, принимающая произвольный интервал. Семейство toStartOfHour/Day/Week — короткие алиасы.
2. WITH FILL FROM ... TO ... STEP INTERVAL ... в ORDER BY заполняет пустые временные интервалы для непрерывных рядов. Без явного FROM/TO пустые интервалы на краях не генерируются.
3. groupArrayMovingSum(N) и groupArrayMovingAvg(N) вычисляют скользящие агрегаты. Требуют предварительной сортировки по времени.
4. neighbor(col, -1) и runningDifference(col) — для delta-вычислений между соседними точками ряда.
5. TTL + PARTITION BY toYYYYMM(ts) — стандартный lifecycle паттерн для time-series (детально в Module 10).