如何用VIEW补全追加型数据场景下MATERIALIZED VIEW的缺失数据？

这种方案完全可行，核心是通过普通视图将物化视图缓存的历史OHLC数据与实时计算的最新未完成时段数据合并，既利用了物化视图的查询性能，又保证了最新数据的实时性。

核心思路 #

• 物化视图ohlc存储已完成小时的OHLC数据，按小时定时刷新
• 上层普通视图合并两组数据：
  1. 从ohlc读取所有已完成小时的历史缓存数据
  2. 实时从price表计算当前未完成小时的OHLC数据
  3. 用UNION ALL合并两组数据（确保时间范围无重叠）

具体实现（以PostgreSQL为例） #

1. 创建物化视图ohlc #

定义存储历史小时OHLC的物化视图，按股票和小时分组计算：

CREATE MATERIALIZED VIEW ohlc AS
SELECT
    stock_id,
    DATE_TRUNC('hour', trade_time) AS hour_start,
    -- 开盘价：小时内第一笔交易价格
    FIRST_VALUE(price) OVER (
        PARTITION BY stock_id, DATE_TRUNC('hour', trade_time) 
        ORDER BY trade_time
    ) AS open,
    -- 最高价：小时内最高价格
    MAX(price) AS high,
    -- 最低价：小时内最低价格
    MIN(price) AS low,
    -- 收盘价：小时内最后一笔交易价格
    LAST_VALUE(price) OVER (
        PARTITION BY stock_id, DATE_TRUNC('hour', trade_time) 
        ORDER BY trade_time 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS close
FROM price
GROUP BY stock_id, DATE_TRUNC('hour', trade_time);

2. 设置物化视图定时刷新 #

配置每小时整点刷新物化视图，确保数据为截至上一小时的完整数据：

-- 需先安装pg_cron扩展实现定时任务
SELECT cron.schedule(
    'refresh-ohlc-hourly',
    '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY ohlc;'
);

注：使用CONCURRENTLY可避免刷新时锁表，但需先给物化视图创建唯一索引：

CREATE UNIQUE INDEX idx_ohlc_stock_hour ON ohlc(stock_id, hour_start);

3. 创建上层合并视图ohlc_combined #

该视图优先返回物化视图的历史数据，同时实时计算当前小时的最新OHLC：

CREATE VIEW ohlc_combined AS
-- 第一部分：已完成小时的缓存数据
SELECT stock_id, hour_start, open, high, low, close
FROM ohlc
WHERE hour_start < DATE_TRUNC('hour', CURRENT_TIMESTAMP)

UNION ALL

-- 第二部分：当前未完成小时的实时计算数据
SELECT
    stock_id,
    DATE_TRUNC('hour', trade_time) AS hour_start,
    FIRST_VALUE(price) OVER (PARTITION BY stock_id ORDER BY trade_time) AS open,
    MAX(price) AS high,
    MIN(price) AS low,
    LAST_VALUE(price) OVER (
        PARTITION BY stock_id 
        ORDER BY trade_time 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS close
FROM price
WHERE trade_time >= DATE_TRUNC('hour', CURRENT_TIMESTAMP)
GROUP BY stock_id, DATE_TRUNC('hour', trade_time);

关键注意事项 #

• 时间边界控制：用DATE_TRUNC('hour', CURRENT_TIMESTAMP)划分已完成小时和当前小时，确保两组数据无重叠，避免重复
• 性能优化：给price表的stock_id和trade_time创建联合索引，加速当前小时数据的实时计算：

  CREATE INDEX idx_price_stock_time ON price(stock_id, trade_time);
  

• 数据库兼容性：若使用MySQL等其他数据库，物化视图的实现语法会有差异，但核心合并逻辑一致
• 数据一致性：使用CONCURRENTLY刷新物化视图时，必须保证物化视图有唯一索引，否则会执行失败

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