ClickHouse如何基于ReplacingMergeTree构建SummingMergeTree MV避免重复统计

核心逻辑前提 #

ReplacingMergeTree的去重仅在后台异步合并时触发，未合并的重复版本数据会被正常读取，而常规写入时触发的物化视图会默认将所有写入的版本数据纳入统计，这是重复统计的根本原因。以下是可落地的实现方案：

具体实现步骤 #

1. 源表结构校验 #

源ReplacingMergeTree的排序键（ORDER BY）必须和业务唯一键完全对齐，同时保留版本字段（数值型，值越大代表数据版本越新）作为去重依据，示例结构如下：

CREATE TABLE source_replace_table
(
    unique_key1 String,
    unique_key2 Int64,
    stat_value Int64 COMMENT '需要求和的指标字段',
    ver UInt64 COMMENT '数据版本号，写入时递增保证新版本覆盖旧版本'
)
ENGINE = ReplacingMergeTree(ver)
ORDER BY (unique_key1, unique_key2);

2. 构建SummingMergeTree目标存储表 #

目标表排序键和统计维度完全对齐，字段采用AggregateFunction类型存储聚合状态，避免重复写入重复累加：

CREATE TABLE sum_target_table
(
    unique_key1 String,
    unique_key2 Int64,
    total_stat AggregateFunction(sum, Int64)
)
ENGINE = SummingMergeTree
ORDER BY (unique_key1, unique_key2);

此处不能使用普通数值类型存储求和结果，否则同一份数据多次写入时会被重复累加

3. 编写物化视图逻辑 #

通过argMaxState保留唯一键对应最新版本的指标值，再做求和聚合，确保即使重复数据多次写入也只会取最新版本参与统计：

CREATE MATERIALIZED VIEW replace_to_sum_mv
TO sum_target_table
AS
SELECT
    unique_key1,
    unique_key2,
    sumState(argMax(stat_value, ver)) AS total_stat
FROM source_replace_table
GROUP BY unique_key1, unique_key2;

4. 正确的查询方式 #

查询统计结果时使用sumMerge函数触发SummingMergeTree的预聚合逻辑，得到准确结果：

SELECT
    unique_key1,
    unique_key2,
    sumMerge(total_stat) AS final_sum
FROM sum_target_table
GROUP BY unique_key1, unique_key2;

不可遗漏的实现要点 #

• 禁止在物化视图的查询逻辑中对源表加FINAL：会导致每次写入都触发源表分区合并，性能随数据量增长暴跌
• 存量数据回溯：如果源表已存在存量数据，需要手动执行一次写入补全存量统计结果：

  INSERT INTO sum_target_table
  SELECT
      unique_key1,
      unique_key2,
      sumState(argMax(stat_value, ver)) AS total_stat
  FROM source_replace_table
  GROUP BY unique_key1, unique_key2;
  

• 处理删除场景：如果业务存在数据删除需求，需要新增sign标记字段，删除时写入sign=-1的同版本同唯一键数据，求和时乘以sign即可
• 分片集群适配：如果是分布式表，需要将物化视图建在本地表上，再通过分布式表查询聚合结果，避免跨分片数据统计错误

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