ClickHouse基于AggregatingMergeTree构建多指标聚合物化视图咨询

核心错误原因 #

你当前写法不符合AggregatingMergeTree的使用规则：该引擎不会自动识别普通数值类型的聚合结果进行合并，必须使用对应聚合函数的*State()变种存储聚合中间状态，引擎合并时会自动对State类型的字段做状态合并，保证结果正确。

正确实现步骤 #

1. 创建符合规范的物化视图 #

你需要的四个指标对应State函数分别是：

• 求和：sumState()
• 去重计数：uniqExactState()（精准去重，若允许极小误差可换uniqState()性能更高）
• 最小值：minState()
• 最大值：maxState()

正确建表语句如下：

CREATE MATERIALIZED VIEW test1.MV1
ENGINE = AggregatingMergeTree() 
ORDER BY seriesId
POPULATE
AS 
SELECT  
    seriesId,
    sumState(amount) AS amt_state,
    uniqExactState(amount) AS distinct_amt_cnt_state,
    minState(amount) AS min_amt_state,
    maxState(amount) AS max_amt_state
FROM test1.table1 t 
GROUP BY seriesId;

2. 正确查询聚合结果 #

存储的State字段不能直接读取，需要用对应*Merge()函数包裹，且查询时必须带上GROUP BY分组键：

SELECT
    seriesId,
    sumMerge(amt_state) AS total_amt,
    uniqExactMerge(distinct_amt_cnt_state) AS distinct_amt_cnt,
    minMerge(min_amt_state) AS min_amt,
    maxMerge(max_amt_state) AS max_amt
FROM test1.MV1
GROUP BY seriesId;

如果不想每次查询都写Merge逻辑，可以额外创建一个普通视图封装查询逻辑，直接调用该视图即可拿到最终结果：

CREATE VIEW test1.MV1_final AS
SELECT
    seriesId,
    sumMerge(amt_state) AS total_amt,
    uniqExactMerge(distinct_amt_cnt_state) AS distinct_amt_cnt,
    minMerge(min_amt_state) AS min_amt,
    maxMerge(max_amt_state) AS max_amt
FROM test1.MV1
GROUP BY seriesId;

额外说明 #

• 无需等待后台合并完成即可得到正确结果：*Merge()函数在查询时会自动合并同一seriesId下所有分片/分区的聚合状态，哪怕后台还没触发AggregatingMergeTree的合并动作，结果也100%准确。
• 不建议高频使用FINAL关键字查询：40亿级数据量下FINAL会强制触发实时合并，查询性能损耗极高，正常使用*Merge()函数即可满足需求。
• 新写入源表的数据会自动同步到物化视图做增量聚合，无需额外操作。

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