ClickHouse物化视图聚合出现幽灵行问题求助

问题背景 #

主表Liquidity（Distributed引擎）执行特定过滤条件查询时，sum(nb_aggregated)结果为1；但对应的物化视图目标表shard_state_Liquidity_facet（ReplicatedAggregatingMergeTree引擎）用相同条件查询，得到的nb_aggregated结果为2，聚合逻辑出现偏差，以下是具体排查方向：

1. 验证物化视图查询的聚合函数使用正确性 #

ReplicatedAggregatingMergeTree存储的是聚合状态数据，查询时必须用sumMerge()函数解析聚合状态，而非直接读取字段值。确认你的物化视图查询SQL是否符合规范：

• 错误写法（直接读取状态字段会得到二进制解析后的错误数值）：

select nb_aggregated from shard_state_Liquidity_facet where [你的过滤条件];

• 正确写法：

select sumMerge(nb_aggregated) as nb_aggregated from shard_state_Liquidity_facet where [你的过滤条件];

2. 检查GROUP BY字段与过滤字段的匹配度 #

物化视图的GROUP BY子句包含所有过滤字段，但需排查字段的实际值一致性：

• 字符串类型字段（如Currency、Scenario）是否存在隐藏字符、大小写差异（比如'AUD'和'AUD '），导致主表过滤时匹配单行，但物化视图中因字符串不一致被错误聚合；
• 数值类型字段是否存在溢出或隐式转换问题（比如commit字段为Int64，是否有写入时的类型不匹配）。

3. 验证物化视图数据源与主表分片一致性 #

物化视图从本地分片表shard_Liquidity读取数据，而主表是Distributed引擎关联所有分片：

• 执行SELECT sum(nb_aggregated) FROM shard_Liquidity WHERE [你的过滤条件];，查看本地分片的原始数据总和，确认是否与主表结果一致；
• 执行SELECT _shard_num, sum(nb_aggregated) FROM Liquidity WHERE [过滤条件] GROUP BY _shard_num;，检查Distributed引擎是否正确路由到目标分片，是否存在多分片返回数据的情况。

4. 排查物化视图的数据同步与合并状态 #

• 强制同步物化视图：执行SYSTEM SYNC MATERIALIZED VIEW mv_Liquidity_facet;后重新查询；
• 检查分区合并状态：执行SELECT * FROM system.parts WHERE table = 'shard_state_Liquidity_facet' AND active = 1 AND partition = '2022-10-17';，查看是否存在未合并的片段；
• 强制合并分区：执行OPTIMIZE TABLE shard_state_Liquidity_facet PARTITION '2022-10-17' FINAL;后重新验证结果。

5. 检查数据写入时的重复或异常 #

• 查看本地分片的原始数据：执行SELECT * FROM shard_Liquidity WHERE [你的过滤条件];，确认是否存在多行数据，以及每行的nb_aggregated值是否符合预期；
• 排查写入日志：执行SELECT * FROM system.query_log WHERE query LIKE '%mv_Liquidity_facet%' AND type = 'Write' ORDER BY event_time DESC LIMIT 10;，查看是否有写入失败、重复写入的异常记录。

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