如何在TimescaleDB的OHLC物化视图中添加成交量(Volume)计算

已从MongoDB迁移交易数据到TimescaleDB，用于生成OHLCV数据，但现有连续聚合物化视图仅包含OHLC字段，导致TradingView图表无成交量显示。以下是修改方案：

1. 修改时间桶查询（验证用） #

在原有OHLC查询基础上，添加成交量求和逻辑，先验证计算结果：

SELECT
  time_bucket('1 min', "timestamp") AS bucket,
  market,
  max(price) AS high,
  first(price, timestamp) AS open,
  last(price, timestamp) AS close,
  min(price) AS low,
  sum(volume) AS volume  -- 新增：统计时间桶内的总成交量
FROM trades srt
GROUP BY bucket, market
ORDER BY bucket DESC, market;

2. 重建连续聚合物化视图 #

由于连续聚合物化视图无法直接修改字段，需先删除原有视图（若已创建），再创建包含成交量的新视图：

-- 先删除原有物化视图（如果存在）
DROP MATERIALIZED VIEW IF EXISTS candlestick_one_minute;

-- 创建包含OHLCV的连续聚合物化视图
CREATE MATERIALIZED VIEW candlestick_one_minute
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 min', "timestamp") AS bucket,
  market,
  max(price) AS high,
  first(price, timestamp) AS open,
  last(price, timestamp) AS close,
  min(price) AS low,
  sum(volume) AS volume  -- 新增：计算时间桶内总成交量
FROM trades srt
GROUP BY bucket, market;

注意：移除了原语句中的ORDER BY，因为连续聚合物化视图的定义中不允许包含ORDER BY子句，排序可在查询视图时按需添加。

3. 重新添加连续聚合策略（删除视图后策略会被同步移除） #

SELECT add_continuous_aggregate_policy('candlestick_one_minute',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 day');

关键说明 #

• 成交量计算使用sum(volume)，因为trades表中每条记录的volume是单笔交易的成交量，时间桶内的总成交量即为该时间段所有单笔成交量之和。
• 若不想删除原有视图，也可创建全新的包含成交量的连续聚合视图；TimescaleDB对连续聚合视图的ALTER操作支持有限，重建是更稳妥的方案。

内容的提问来源于stack exchange，提问作者Gladiator9120