大数据

TimescaleDB 连续聚合（Continuous Aggregates）

2025年4月23日 作者 unix2go

在时间序列数据的分析中，聚合操作（如求平均值、最大值、最小值等）经常用到，特别是在需要对大规模数据进行汇总时。为了高效处理这些操作，TimescaleDB 提供了**连续聚合（Continuous Aggregates）**功能，它可以在后台自动维护聚合结果，从而避免每次查询时都重新计算。

以下是一个完整的 连续聚合 示例，包括创建超表、插入数据、创建连续聚合视图以及查询聚合结果。

1. 数据背景

假设我们有一个 IoT（物联网）场景，记录设备的温度传感器数据。数据表包含以下字段：

  • 时间戳（time）
  • 设备 ID（device_id）
  • 温度值（temperature）

2. 创建超表

首先，我们创建一个表用于存储时间序列数据，并将其转换为超表（hypertable）：

-- 创建普通表
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,       -- 时间戳
    device_id TEXT NOT NULL,         -- 设备 ID
    temperature DOUBLE PRECISION     -- 温度值
);

-- 将表转换为超表
SELECT create_hypertable('sensor_data', 'time');

3. 插入数据

向超表中插入一些示例数据：

INSERT INTO sensor_data (time, device_id, temperature)
VALUES
    ('2025-04-23 10:00:00', 'device_1', 22.5),
    ('2025-04-23 10:05:00', 'device_1', 23.0),
    ('2025-04-23 10:10:00', 'device_1', 21.8),
    ('2025-04-23 10:15:00', 'device_1', 22.2),
    ('2025-04-23 10:20:00', 'device_1', 23.1),
    ('2025-04-23 10:25:00', 'device_2', 30.5),
    ('2025-04-23 10:30:00', 'device_2', 29.8),
    ('2025-04-23 10:35:00', 'device_2', 31.0);

4. 创建连续聚合视图

假设我们希望按每 10 分钟计算每个设备的平均温度和最大温度，可以通过以下 SQL 创建连续聚合视图：

CREATE MATERIALIZED VIEW sensor_data_aggregates
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('10 minutes', time) AS bucket,  -- 时间分组，每 10 分钟一个桶
    device_id,                                -- 设备 ID
    AVG(temperature) AS avg_temperature,      -- 平均温度
    MAX(temperature) AS max_temperature       -- 最大温度
FROM sensor_data
GROUP BY bucket, device_id;

5. 查询连续聚合视图

连续聚合视图会在后台维护聚合结果，你可以像查询普通物化视图那样查询它：

SELECT * FROM sensor_data_aggregates ORDER BY bucket, device_id;

示例输出：

bucketdevice_idavg_temperaturemax_temperature
2025-04-23 10:00:00device_122.37523.0
2025-04-23 10:10:00device_122.52523.1
2025-04-23 10:20:00device_230.1530.5
2025-04-23 10:30:00device_230.431.0

6. 更新连续聚合视图

当新的数据插入到 sensor_data 表中时，连续聚合视图不会立即包含这些数据。你可以通过以下方式手动刷新视图，或者设置自动刷新策略。

手动刷新聚合视图：

CALL refresh_continuous_aggregate('sensor_data_aggregates', NULL, NULL);
  • NULL, NULL 表示刷新整个时间范围。
  • 你也可以指定时间范围，例如刷新过去 1 天的数据： CALL refresh_continuous_aggregate('sensor_data_aggregates', NOW() - INTERVAL '1 day', NOW());

自动刷新策略：

可以设置策略，让 TimescaleDB 自动刷新连续聚合视图，例如每隔 1 小时刷新一次：

SELECT add_continuous_aggregate_policy(
    'sensor_data_aggregates',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);
  • start_offset：刷新起点距当前时间的偏移量。
  • end_offset：刷新结束点距当前时间的偏移量。
  • schedule_interval：多久执行一次刷新。

7. 优势与注意事项

优势

  • 性能提升：连续聚合将聚合结果存储在物化视图中，避免每次查询时重新计算，极大提升查询性能。
  • 实时更新：使用自动刷新策略，可以让聚合视图接近实时更新。
  • 灵活性：支持复杂的聚合函数和分组逻辑。

注意事项

  • 存储开销：连续聚合视图会占用额外的存储空间。
  • 刷新延迟：由于聚合视图不是实时更新的，最新插入的数据可能需要等待刷新后才能出现在视图中。
  • 限制：连续聚合视图不支持 JOIN 操作。如果需要结合其他表查询，可以在查询时手动加入。

总结

TimescaleDB 的连续聚合功能非常适合需要高效时间序列聚合的场景，尤其是：

  • 设备遥测（IoT 数据）
  • 系统监控（如 CPU 使用率的历史汇总）
  • 金融数据（如股票价格的周期性统计）

通过预计算和维护聚合结果，连续聚合功能在不增加查询复杂度的情况下显著提升了性能，特别是在高频写入和大规模数据分析的场景中。

Print Friendly, PDF & Email