第十二章时间序列数据处理 - 第三节实战：物联网设备监控数据存储与分析 - B40yd

第三节实战：物联网设备监控数据存储与分析

目标：设计一个完整的物联网（IoT）设备监控系统的数据层，综合运用 TimescaleDB 的 Hypertable、空间分区、压缩和时序分析函数，以解决真实世界中的海量时序数据挑战。

场景描述

我们是一家智能农业公司，在全国各地的农场部署了大量的环境传感器。每个传感器每分钟都会上报一次**温度（temperature）和湿度（humidity）**数据。

数据层需求：

高效写入：能够承受每秒数千次的数据点写入。
高效查询：
- 快速查询某个特定设备在最近一小时的详细数据。
- 快速分析某个农场（location）所有设备的平均温度。
存储优化：数据需要长期保存，但只有最近7天的数据需要频繁访问和修改。超过7天的数据应被压缩以节省成本。
时序分析：提供专门的函数来简化时序分析，例如计算每小时的平均温度。

🏛️ 第一步：设计数据表并创建 Hypertable

我们创建一个 sensor_readings 表，并将其配置为同时按时间和空间（农场位置）分区的 Hypertable。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 确保 TimescaleDB 扩展已启用
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 1. 创建标准表
CREATE TABLE sensor_readings (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    location    TEXT NOT NULL, -- 农场位置
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION
);

-- 2. 转换为 Hypertable
SELECT create_hypertable(
    'sensor_readings',
    'time',
    'location',           -- 按 location 进行空间分区
    number_partitions => 4,  -- 假设我们有4个数据中心或区域
    chunk_time_interval => INTERVAL '1 day' -- 每天创建一个新的时间 Chunk
);

✍️ 第二步：设置数据生命周期管理策略

我们为 Hypertable 设置压缩和数据保留策略。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 1. 配置压缩
-- 我们希望按 location 和 device_id 进行分段压缩，以优化按设备的查询
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'location, device_id'
);

-- 2. 添加压缩策略：自动压缩超过 7 天的数据
SELECT add_compression_policy('sensor_readings', compress_after => INTERVAL '7 days');

-- 3. (可选) 添加数据保留策略：自动删除超过 1 年的数据
-- SELECT add_retention_policy('sensor_readings', drop_after => INTERVAL '1 year');

模拟数据写入

我们写入一些模拟数据来测试系统。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
INSERT INTO sensor_readings (time, device_id, location, temperature, humidity)
SELECT
    ts,
    'device-' || (s % 10),
    'farm-' || (s % 2),
    20 + random() * 10, -- 20-30 度的温度
    60 + random() * 20  -- 60-80% 的湿度
FROM
    generate_series(1, 100000) AS s,
    generate_series(NOW() - INTERVAL '10 days', NOW(), INTERVAL '1 minute') AS ts;

🔍 第四步：执行高效的时序查询

查询 1：获取 ‘device-3’ 在 ‘farm-1’ 最近一小时的读数

这个查询会高效地利用时间和空间分区裁剪，只访问极少数相关的 Chunks。

1
2
3
4
5
6
SELECT * FROM sensor_readings
WHERE
    device_id = 'device-3'
    AND location = 'farm-1'
    AND time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC;

查询 2：使用 TimescaleDB 的时序函数进行分析

TimescaleDB 提供了许多强大的时序分析函数，time_bucket() 是其中最常用的一个。它类似于 date_trunc，但功能更强大，可以将任意时间间隔“分桶”。

需求：计算 ‘farm-0’ 每小时的平均、最高、最低温度

1
2
3
4
5
6
7
8
9
SELECT
    time_bucket(INTERVAL '1 hour', time) AS hour_bucket,
    avg(temperature) AS avg_temp,
    max(temperature) AS max_temp,
    min(temperature) AS min_temp
FROM sensor_readings
WHERE location = 'farm-0' AND time > NOW() - INTERVAL '3 days'
GROUP BY hour_bucket
ORDER BY hour_bucket DESC;

time_bucket() 极大地简化了按时间窗口进行聚合的查询。

查询 3：`FIRST` 和 `LAST` 函数

FIRST 和 LAST 是 TimescaleDB 提供的另外两个非常有用的聚合函数，可以高效地获取每个时间桶内的第一个和最后一个值。

需求：获取每个设备每天的开盘（第一条）和收盘（最后一条）温度

1
2
3
4
5
6
7
8
9
SELECT
    time_bucket(INTERVAL '1 day', time) AS day_bucket,
    device_id,
    FIRST(temperature, time) AS opening_temp,
    LAST(temperature, time) AS closing_temp
FROM sensor_readings
WHERE time > NOW() - INTERVAL '5 days'
GROUP BY day_bucket, device_id
ORDER BY day_bucket DESC, device_id;

与使用窗口函数或自连接的复杂 SQL 相比，FIRST/LAST 的语法极其简洁且性能更优。

📌 小结

本实战案例展示了 TimescaleDB 如何将 PostgreSQL 打造成一个功能完备、性能卓越的物联网时序数据平台。

Hypertable 提供了可扩展的、自动化的分区存储。
空间分区 进一步优化了多维查询。
压缩和保留策略 实现了数据的全生命周期自动化管理，有效控制了存储成本。
专用的时序函数（如 time_bucket, FIRST, LAST）极大地简化了复杂的时序分析查询，提升了开发效率和查询性能。

对于任何需要处理时间序列数据的场景，无论是物联网、金融还是系统监控，TimescaleDB 都提供了一个建立在 PostgreSQL 坚实基础之上的、无与伦比的解决方案。

文章目录

第三节 实战：物联网设备监控数据存储与分析