普通视图在亿级时序数据上无法快速响应，因其每次查询都触发全表扫描与实时聚合；应改用TimescaleDB连续聚合或自建汇总表+定时任务实现物化预计算。
直接建普通视图对亿级时序数据做 GROUP BY DATE_TRUNC('hour', ts)，查一次就卡死——视图不缓存结果，每次都是全表扫描+实时聚合。真要“快速”，必须绕过视图的纯逻辑层，用物化手段固化聚合结果。
为什么普通视图在亿级时序上根本跑不动
视图只是保存了一条 SELECT 语句文本，查的时候数据库原样展开、连同所有 WHERE / GROUP BY 一起执行。亿级表上跑 GROUP BY DATE_TRUNC('hour', ts)，意味着：
- 每次查询都要读取全部原始时间戳并计算截断
- 若没索引或索引被 DATE_TRUNC(ts) 破坏，就是全表扫描
- 聚合过程需在内存维护大量分组状态，容易 OOM
- 多个用户同时查同一视图，各自触发一遍完整计算，毫无复用
PostgreSQL + TimescaleDB：用连续聚合替代视图
这不是“建个视图”的事，而是换用 continuous aggregate —— 它是 TimescaleDB 对超表（hypertable）的增量物化机制，本质是带自动刷新的物化视图，但关键区别在于：
- 不重算全量，只追加处理新写入的数据块
- 支持按策略自动压缩、保留、下采样
- 查询时直接走预聚合结果，毫秒级响应
实操步骤：
- 确保你的时序表已是 TimescaleDB 超表（create_hypertable()）
- 创建连续聚合：
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
device_id,
AVG(value) AS avg_val,
COUNT(*) AS cnt
FROM metrics
GROUP BY 1, 2;
- 后续查 metrics_hourly 就是查物化结果，不是实时算出来的
MySQL 或纯 PostgreSQL：用汇总表 + 定时任务模拟
没有 TimescaleDB？那就自己造“轻量级连续聚合”：
- 单独建一张汇总表，结构和你想要的聚合结果一致（如含 hour_start, device_id, avg_value）
- 用定时任务（如 cron + INSERT ... ON DUPLICATE KEY UPDATE）每小时跑一次增量聚合：
INSERT INTO metrics_hourly_summary (hour_start, device_id, avg_value, cnt)
SELECT FLOOR(UNIX_TIMESTAMP(ts) / 3600) * 3600 AS h,
device_id,
AVG(value),
COUNT(*)
FROM metrics
WHERE ts >= FROM_UNIXTIME(%s) AND ts < FROM_UNIXTIME(%s + 3600)
GROUP BY h, device_id
ON DUPLICATE KEY UPDATE
avg_value = (avg_value * cnt + VALUES(avg_value) * VALUES(cnt)) / (cnt + VALUES(cnt)),
cnt = cnt + VALUES(cnt);
- 注意点：
- WHERE 条件必须用范围，别写 DATE_TRUNC 或 FROM_UNIXTIME(FLOOR(...))，否则索引失效
- 汇总表主键设为 (hour_start, device_id)，支撑幂等更新
- 初始可先用大时间窗口补全历史，后续只增量
真正难的从来不是语法，而是时间上下文与索引存活
哪怕你写对了 DATE_TRUNC 或 FLOOR(UNIX_TIMESTAMP())，只要底层 ts 字段没索引、或是 TIMESTAMP WITHOUT TIME ZONE 却跨时区查询，结果就可能错位一小时；只要在 WHERE 里对 ts 套函数，B-tree 索引就彻底失效。所谓“快速聚合”，七分靠设计（分区、索引、时区对齐），三分靠语法。别指望一个 CREATE VIEW 语句能扛住亿级流量。
