PostgreSQL结合TimescaleDB预计算分组滚动成交量直方图咨询

完全可以将滑动窗口成交量直方图预计算后存储，避免每次查询实时计算，以下是适配Timescale扩展的两种通用方案：

方案一：Timescale连续聚合（优先推荐，支持增量自动刷新） #

该方案继承Timescale原生时序优化能力，支持配置自动刷新策略，新交易日数据入库后可增量计算对应窗口结果，无需全量重算：

-- 创建近30日滑动成交量直方图连续聚合视图
CREATE MATERIALIZED VIEW volume_profile_rolling_30d
WITH (timescaledb.continuous)
AS
WITH trading_dates AS (
    -- 提取所有有数据的交易日作为滑动窗口的结束日期
    SELECT DISTINCT date AS window_end_date, asset
    FROM volume_profile_daily
),
window_date_range AS (
    -- 为每个窗口结束日匹配其覆盖的近30个交易日
    SELECT 
        td.window_end_date,
        td.asset,
        vpd.date AS trade_date
    FROM trading_dates td
    JOIN LATERAL (
        SELECT date 
        FROM volume_profile_daily
        WHERE asset = td.asset 
          AND date <= td.window_end_date
        ORDER BY date DESC
        LIMIT 30
    ) vpd ON true
)
-- 按窗口结束日、资产、价格桶聚合求和得到滚动成交量
SELECT 
    wr.window_end_date,
    wr.asset,
    vpd.close,
    SUM(vpd.volume) AS rolling_30d_volume
FROM window_date_range wr
JOIN volume_profile_daily vpd 
  ON wr.asset = vpd.asset 
  AND wr.trade_date = vpd.date
GROUP BY wr.window_end_date, wr.asset, vpd.close;

连续聚合刷新配置（可选） #

-- 配置每日自动刷新最近7天的窗口数据，可根据业务需求调整参数
ALTER MATERIALIZED VIEW volume_profile_rolling_30d 
SET (timescaledb.refresh_interval = '1 day', timescaledb.refresh_lag = '7 days');

查询示例 #

-- 查询指定日期、指定资产的近30日成交量直方图
SELECT close, rolling_30d_volume
FROM volume_profile_rolling_30d
WHERE window_end_date = '2021-09-01' 
  AND asset = 'ASSET_NAME'
ORDER BY close;

方案二：普通物化视图/实体表（适合固定历史数据场景） #

如果你的交易数据为不会修改的历史数据，不需要增量更新，可以直接创建普通物化视图存储结果：

-- 创建静态滑动窗口直方图物化视图
CREATE MATERIALIZED VIEW volume_profile_rolling_30d_static AS
WITH trading_dates AS (
    SELECT DISTINCT date AS window_end_date, asset
    FROM volume_profile_daily
),
window_date_range AS (
    SELECT 
        td.window_end_date,
        td.asset,
        vpd.date AS trade_date
    FROM trading_dates td
    JOIN LATERAL (
        SELECT date 
        FROM volume_profile_daily
        WHERE asset = td.asset 
          AND date <= td.window_end_date
        ORDER BY date DESC
        LIMIT 30
    ) vpd ON true
)
SELECT 
    wr.window_end_date,
    wr.asset,
    vpd.close,
    SUM(vpd.volume) AS rolling_30d_volume
FROM window_date_range wr
JOIN volume_profile_daily vpd 
  ON wr.asset = vpd.asset 
  AND wr.trade_date = vpd.date
GROUP BY wr.window_end_date, wr.asset, vpd.close;

-- 需要更新结果时执行全量刷新
REFRESH MATERIALIZED VIEW volume_profile_rolling_30d_static;

注意事项 #

• 如果需要调整滑动窗口长度，只需修改代码中LIMIT 30的参数即可
• 建议给volume_profile_daily表的asset和date字段添加联合索引，进一步提升刷新和查询效率
• 如需支持多窗口长度预计算，可创建多个对应后缀的视图区分存储

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