ClickHouse 聚合表：快之前，先把指标粒度定死

ClickHouse 聚合表快之前先把指标粒度定死一、聚合表能提速也能把口径错误固化ClickHouse 很适合做明细查询和聚合分析。为了提升看板性能很多团队会建立日粒度、小时粒度或多维聚合表。查询变快了但如果指标粒度和维度设计不清楚错误口径也会被固化到聚合表里。聚合表设计前要回答三个问题指标按什么粒度聚合哪些维度允许下钻哪些指标可加。比如用户数是去重指标不能简单按天相加得到周活。订单金额可以相加但转化率不可以直接相加。为什么可加性是聚合表设计的第一准则而不是性能性能优化可以通过增加节点、调整分区键来做但不可加指标被错误地 SUM 了是数据正确性问题无法通过加机器解决。一个经典翻车案例某团队把每日的用户 UV 直接 SUM 得到周 UV结果周报显示周活 150 万实际周活只有 80 万包含跨天重复用户。这个错误数字在管理层会议上被引用了 3 个月直到财务部门独立统计时才发现差距。聚合表一旦上线并被引用错误的纠正成本远超创建时的设计成本。二、按指标可加性选择聚合策略指标可以分为可加、半可加和不可加。可加指标如 GMV、订单数半可加指标如库存不可加指标如去重用户数、转化率。不同类型对应不同聚合表设计。flowchart TD A[指标清单] -- B{可加性} B --|可加| C[直接 sum 聚合] B --|半可加| D[按指定时间点快照] B --|不可加| E[保留状态或重算] C -- F[聚合表] D -- F E -- F F -- G[BI 查询]不可加指标不要为了快硬聚合。该保留 bitmap、uniqState 或回查明细时就不要偷懒。三、建表时把聚合状态和查询方式配套设计下面示例使用AggregatingMergeTree保存去重用户状态。查询时再 merge。CREATE TABLE agg_daily_channel ( dt Date, channel LowCardinality(String), pay_amount SimpleAggregateFunction(sum, Float64), order_count SimpleAggregateFunction(sum, UInt64), user_state AggregateFunction(uniqCombined64, UInt64) ) ENGINE AggregatingMergeTree PARTITION BY toYYYYMM(dt) ORDER BY (dt, channel);这个表把金额和订单数作为可加指标把用户数保存为聚合状态。后续查询用户数时需要使用uniqCombined64Merge(user_state)不能直接 sum。四、聚合表要有回补和校验机制聚合表不是一次建好就结束。上游补数、订单状态变化、维度修正都会影响结果。必须设计回补机制并能按日期重刷聚合数据。还要做明细对账。每天抽样比较聚合表和明细表的结果尤其是核心指标。聚合表查询快但不能因为快就跳过校验。看板越依赖它越要证明它可信。为什么明细对账不能是一个月抽一次聚合表的错误有两种一次性计算错误建表时 SQL 写错了和累积漂移错误上游补数了、订单状态变了、分区重跑后部分数据缺失。一次性错误可以通过建表时的全量对账发现但累积漂移是渐进的——今天 0.1% 的误差30 天后变成了 3%到第 60 天才被发现。建议至少每日抽样对账核心指标GMV、订单数、UV一旦误差超过 0.5% 就自动告警。这不是狼来了而是帮你发现上游补数、维度变更或数据损坏的唯一手段。最后维度基数要控制。把所有维度都塞进聚合表会导致数据膨胀。高基数字段可以留给明细查询或单独场景不要让通用聚合表背所有需求。查询层也要限制用法。聚合表通常只支持固定粒度和固定维度组合如果 BI 允许用户任意拖拽字段就可能生成不符合口径的查询。可以在语义层声明哪些指标允许按哪些维度查询超出范围时提示回到明细表或申请新模型。聚合表上线前要做压测。看板峰值刷新、多人同时下钻、缓存失效后的冷查询都可能暴露性能问题。压测结果要反向调整分区、排序键和物化视图刷新策略。删除和修正同样要设计。订单撤销、维度归属变更、历史补数都会让旧聚合结果失效。可以按分区重算也可以通过版本字段隔离新旧结果。无论选哪种都要保证 BI 查询不会同时读到两个口径。五、总结 踩坑提醒不可加指标用 AggregatingMergeTree 但查询时忘了Mergeuser_state AggregateFunction(uniqCombined64, UInt64)保存的是中间聚合状态不是可读数字。如果你直接SELECT user_state会得到一个二进制 blob看起来像乱码。必须用uniqCombined64Merge(user_state)来合并状态并得到最终的去重结果。新人容易犯的错误是建表对了查询错了看板显示的数字永远为 0。更新频率和分区策略不一致会导致对账时数据对不上你的聚合表按toYYYYMM(dt)做月度分区但上游明细表是 T1 更新的。如果某个月底分区内部分天的数据被补录了你重跑了整个月分区但下游 BI 缓存还是旧的就会出现同一张表同一个查询两次不同结果。建议聚合表的分区和上游明细表保持一致粒度。维度基数膨胀会让聚合表比明细表还大如果一个聚合表有 8 个维度每个维度有 10 个取值笛卡尔积是 10^8 1 亿种组合——远超原始明细数据的行数。聚合表不是把所有维度都扔进去就完了必须评估交叉组合基数。建议只把看板上实际用作 TopN 筛选和下钻的维度放入聚合表其余按需查明细。ClickHouse 聚合表设计要先确定指标粒度和可加性。可加指标可以直接聚合不可加指标要保存状态或回查重算。聚合表必须配套回补、对账和维度基数控制。性能优化的前提是口径稳定否则只是把错误结果更快地展示出来。