CrateDB：如何让从原表创建的新表自动同步新增数据？

针对你的需求，完全可以避免定期执行INSERT的方式，推荐使用CrateDB的**物化视图（Materialized View）**来实现自动同步，以下是具体方案：

1. 用物化视图替代手动创建的new_table #

物化视图是CrateDB专为预计算场景设计的对象，支持自动刷新机制，能同步源表的新增/变更数据。你可以直接将原来的CREATE TABLE AS语句替换为创建物化视图的语句，并配置刷新策略：

CREATE MATERIALIZED VIEW "schema"."new_table"
REFRESH INTERVAL 1 MINUTE -- 设置自动刷新间隔，可根据需求调整（如5分钟、1小时）
AS
SELECT
  state,
  time,
  time - LAG(time, -1, time) OVER (ORDER BY time DESC) AS duration
FROM "schema"."original_table"
ORDER BY time DESC;

核心说明： #

• 物化视图会按照你设置的REFRESH INTERVAL自动执行全量计算，同步原表的最新数据，无需手动执行INSERT语句。
• 由于你的计算依赖LAG窗口函数（需要基于全表的时间排序关系），全量刷新能保证duration字段的计算始终准确——如果用手动增量插入，若原表存在乱序写入的情况，新增数据可能会破坏已有数据的前后时间关系，导致duration计算错误。

2. 优化：针对大表的增量刷新（分区表场景） #

如果你的original_table是按time字段分区的表（比如按天/小时分区），可以通过分区物化视图实现增量刷新，只计算新增分区的数据，大幅提升性能：

CREATE MATERIALIZED VIEW "schema"."new_table"
PARTITIONED BY (time) -- 与原表分区规则保持一致
REFRESH INTERVAL 1 MINUTE
AS
SELECT
  state,
  time,
  -- 按分区内的时间排序计算duration，避免全表扫描
  time - LAG(time, -1, time) OVER (PARTITION BY DATE_TRUNC('day', time) ORDER BY time DESC) AS duration
FROM "schema"."original_table"
ORDER BY time DESC;

注意事项： #

• 分区物化视图的分区规则必须与原表匹配，否则无法实现增量刷新。
• 这种方式下，刷新操作只会处理原表中新增的分区，不会重复计算已有分区的数据，适合数据量较大的场景。

3. 关于原有手动方案的问题 #

你当前使用的INSERT语句存在两个潜在问题：

• 笔误：原创建表语句中选择的是state字段，但插入语句中写的是process，需注意字段一致性。
• 计算准确性：LAG函数依赖全局的时间排序，增量插入时无法重新计算已有数据的duration，如果原表有数据更新或乱序写入，会导致duration字段出现错误。

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