Временные ряды в аналитике: как агрегировать без ошибок

Если строить график по сырым событиям, запрос со временем станет тяжелее; если заранее сворачивать данные — можно незаметно исказить средние и потерять опоздавшие события. В PostgreSQL 16 выбор обычно между запросом к исходной таблице, материализованным представлением и отдельной таблицей агрегатов. Ниже — практические примеры и критерии, по которым выбирать.

Сначала договоритесь, что именно измеряете

У каждой записи должны быть как минимум время события, идентификатор объекта и значение. Время события — когда измерение произошло, а не когда его загрузили в базу. Если хранить только время загрузки, задержка доставки данных будет выглядеть как реальное изменение показателя.

Для временных отметок используйте timestamptz: PostgreSQL хранит момент времени и переводит его при отображении в часовую зону сеанса. Зафиксируйте, в какой зоне строятся интервалы отчёта. Например, часовые интервалы ниже привязаны к UTC.

CREATE TABLE measurements (
    device_id bigint NOT NULL,
    observed_at timestamptz NOT NULL,
    value numeric NOT NULL
);

CREATE INDEX measurements_device_time_idx
    ON measurements (device_id, observed_at);

Индекс составной: он помогает запросам, которые выбирают конкретное устройство и диапазон времени. Если запросы чаще охватывают сразу все устройства, проверьте план выполнения на ваших данных: порядок колонок в индексе имеет значение.

Термин. Временной ряд — последовательность наблюдений, упорядоченных по времени; например, температура датчика или число заказов за час.

Три способа получить сводку

Не выбирайте агрегацию «на всякий случай». Сначала измерьте запрос на реалистичном диапазоне: время выполнения, число прочитанных строк и нагрузку на базу. Если сырой запрос укладывается в требования по задержке, отдельная система обновления агрегатов может добавить больше сложностей, чем пользы.

Сырые данные: корректно нарезаем интервалы

В PostgreSQL 16 функция date_bin группирует отметки по равным интервалам относительно заданного начала. Запрос ниже считает почасовую сумму и среднее для одного устройства за сутки. Границы диапазона полуоткрытые: начало включено, конец исключён. Так соседние запросы не задваивают запись на общей границе.

SELECT
    date_bin(
        INTERVAL '1 hour',
        observed_at,
        TIMESTAMPTZ '2000-01-01 00:00:00+00'
    ) AS bucket_start,
    count(*) AS event_count,
    sum(value) AS value_sum,
    avg(value) AS value_avg
FROM measurements
WHERE device_id = 42
  AND observed_at >= TIMESTAMPTZ '2025-02-01 00:00:00+00'
  AND observed_at <  TIMESTAMPTZ '2025-02-02 00:00:00+00'
GROUP BY bucket_start
ORDER BY bucket_start;

Начало интервала — часть определения корзин. Используйте одно и то же значение origin во всех запросах и при заполнении агрегатов; иначе границы часов могут разойтись. Запрос вернёт только интервалы, в которых есть события. Это не ошибка: пустые часы нужно добавлять отдельно, если графику нужна непрерывная шкала.

Для календарных суток в местной зоне не следует считать, что каждый день длится ровно 24 часа: при переходе на летнее или зимнее время местные сутки могут быть короче или длиннее. Группируйте по календарной дате в нужной зоне:

SELECT
    (observed_at AT TIME ZONE 'Europe/Moscow')::date AS local_day,
    count(*) AS event_count,
    avg(value) AS value_avg
FROM measurements
WHERE device_id = 42
GROUP BY local_day
ORDER BY local_day;

Когда сырых запросов уже мало

Если одни и те же почасовые сводки запрашивают постоянно, храните их отдельно. Для среднего сохраняйте сумму и количество, а не только среднее: среднее из средних по часам не даст правильного результата, если в часах разное число событий.

CREATE TABLE measurement_hourly (
    device_id bigint NOT NULL,
    bucket_start timestamptz NOT NULL,
    value_sum numeric NOT NULL,
    event_count bigint NOT NULL,
    PRIMARY KEY (device_id, bucket_start)
);

Пример пересборки одного UTC-часа. Перед запуском задайте начало интервала; границы должны совпадать с часовыми корзинами. Удаление и вставка выполняются одной транзакцией, поэтому повторный запуск не удвоит счётчики.

BEGIN;

DELETE FROM measurement_hourly
WHERE bucket_start = TIMESTAMPTZ '2025-02-01 10:00:00+00';

INSERT INTO measurement_hourly (
    device_id,
    bucket_start,
    value_sum,
    event_count
)
SELECT
    device_id,
    date_bin(
        INTERVAL '1 hour',
        observed_at,
        TIMESTAMPTZ '2000-01-01 00:00:00+00'
    ),
    sum(value),
    count(*)
FROM measurements
WHERE observed_at >= TIMESTAMPTZ '2025-02-01 10:00:00+00'
  AND observed_at <  TIMESTAMPTZ '2025-02-01 11:00:00+00'
GROUP BY device_id, date_bin(
    INTERVAL '1 hour',
    observed_at,
    TIMESTAMPTZ '2000-01-01 00:00:00+00'
);

COMMIT;

Если в выбранный час событий нет, после удаления строка агрегата не появится. Это удобно для отчётов, где отсутствие измерений отличается от нулевого значения. Если по требованиям пустой час должен храниться явно, предусмотрите это отдельно — не подменяйте автоматически отсутствие данных нулём.

Какой вариант выбрать

• Оставьте запрос к сырым событиям, если нужны произвольные диапазоны и запросы укладываются в допустимое время. Это самый простой путь: нет фоновой пересборки и риска устаревших сводок.
• Используйте материализованное представление, если сводка повторяется и приемлемо обновлять её целиком. В PostgreSQL обновление выполняется отдельно от чтения, но данные между обновлениями не отражают новые события.
• Заведите таблицу агрегатов, если отчёты частые, интервалы известны, а пересчёт всего набора слишком дорог. Сразу определите, как повторно обрабатывать изменённые и поздно поступившие события.

Для таблицы агрегатов пересчитывайте затронутую корзину, а не просто прибавляйте к ней новую сумму, если загрузка может повторяться. Повторная доставка события иначе даст дубль. Нужен и способ определить, какие интервалы пересобрать после поступления старых данных.

Что обычно ломается

• Среднее по средним. Суммируйте значения и счётчики отдельно, затем делите общую сумму на общее количество. Иначе час с одним событием получит тот же вес, что и час с тысячей.
• События опаздывают. Если агрегат обновляется один раз и навсегда, поздняя запись останется только в исходной таблице. Задайте окно переобработки или пересобирайте интервал при поступлении события.
• Сдвинуты границы. Разные origin или часовые зоны создают несовпадающие корзины. Зафиксируйте правило для каждого вида отчёта и используйте его и в запросе, и при пересборке.
• График обрывается на пустых часах. Группировка возвращает только интервалы с событиями. Для непрерывной оси сформируйте сетку интервалов и соедините её с агрегатами; отличайте пропуск данных от измеренного нуля.
• Индекс не ускоряет запрос. Проверьте план через EXPLAIN на характерном диапазоне. Если читается большая часть таблицы, последовательное чтение может быть разумнее индекса; оценивать нужно на реальном объёме.

У временного ряда есть два разных вопроса: «когда событие произошло» и «когда мы о нём узнали». Если смешать их, график может быть аккуратным, но показывать не ту историю.

Следующий шаг

Возьмите один отчёт и проверьте его на данных с известными границами: событие ровно в начале интервала, событие ровно в конце, пустой час и поздняя запись. Сравните результат с подсчётом по исходным событиям, затем измерьте время запроса и число прочитанных строк. Только после этого решайте, нужна ли предварительная агрегация. Инвалидация сводок при исправлении исходных событий — отдельная задача, здесь разобрана только пересборка выбранного интервала.