大表分组取最新行的物化视图刷新缓慢，能否实现增量刷新？

Redshift原生的物化视图增量刷新（即REFRESH MATERIALIZED VIEW的增量模式）并不支持这类需要窗口函数取最新记录的场景——原生增量仅适配简单聚合、过滤逻辑，依赖系统自动追踪源表的增量日志，而窗口函数的分组取最新逻辑无法被原生增量机制识别，所以默认只能全量重建。

但你可以通过手动实现逻辑来完成增量刷新，具体步骤如下：

1. 替换原生物化视图为常规表
   先创建一个和原物化视图结构完全一致的常规表，比如命名为order_latest，用来存储最终的最新记录：

   CREATE TABLE order_latest AS
   SELECT *
   FROM (
       SELECT *, pg_catalog.row_number()
               OVER(PARTITION BY flow_id, order_id ORDER BY timestamp desc, msg_seq_num desc) AS last_op_order
       FROM trades
   ) derived_table1
   WHERE derived_table1.last_op_order = 1;
   

2. 实现增量更新逻辑
   核心思路是：每次仅处理trades表中新增/更新的记录，重新计算这些记录对应flow_id+order_id分组的最新值，再同步到order_latest表中。需要依赖一个能识别增量的字段（比如trades表的timestamp，或专门新增的updated_at字段来标记数据修改时间）。

   示例逻辑（假设用updated_at追踪增量）：

   • 第一步：提取trades中上次刷新后新增/更新的数据到临时表

     CREATE TEMP TABLE temp_trades_delta AS
     SELECT * FROM trades
     WHERE updated_at > '上次刷新的时间戳'; -- 可通过专门的表存储上次刷新时间
     

   • 第二步：合并增量数据与原表中对应分组的旧数据，重新计算最新记录

     CREATE TEMP TABLE temp_latest_delta AS
     SELECT *
     FROM (
         SELECT t.*, pg_catalog.row_number()
                 OVER(PARTITION BY t.flow_id, t.order_id ORDER BY t.timestamp desc, t.msg_seq_num desc) AS last_op_order
         FROM (
             SELECT * FROM temp_trades_delta
             UNION ALL
             SELECT tl.* FROM order_latest tl
             JOIN temp_trades_delta td ON tl.flow_id = td.flow_id AND tl.order_id = td.order_id
         ) t
     ) derived
     WHERE derived.last_op_order = 1;
     

   • 第三步：删除原表中涉及增量的分组记录

     DELETE FROM order_latest
     USING temp_trades_delta td
     WHERE order_latest.flow_id = td.flow_id AND order_latest.order_id = td.order_id;
     

   • 第四步：插入重新计算后的最新记录

     INSERT INTO order_latest
     SELECT * EXCLUDE last_op_order FROM temp_latest_delta;
     

3. 封装与调度
   把上述逻辑封装成Redshift存储过程，然后通过Redshift内置调度器或外部工具定时执行，实现自动增量刷新。

注意事项 #

• 必须确保trades表有可靠的增量识别字段，否则无法准确筛选新增/更新数据。
• 如果trades存在删除操作，需要额外处理：比如通过Redshift的STL_DELETE系统表识别被删除的flow_id+order_id，再从order_latest中同步删除。
• 操作过程中尽量用临时表预处理数据，减少对order_latest表的锁持有时间，避免影响查询性能。

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