ClickHouse学习笔记（四）：物化视图与聚合状态实战

订单明细越积越多，但报表每天都在问同一个问题：“各城市今天支付了多少笔、多少钱、多少人？”

如果每次都从全部明细重新聚合，查询会重复做很多工作。能不能在数据写入时就先整理一部分，查询时再合并这些结果？

这一篇用增量物化视图 + 聚合状态表完成一个小实验，重点理解它处理的是哪些数据，以及为什么不能把每批次的统计结果随意相加。

系列导航：

  1. 列式存储与 OLAP
  2. Docker 与 SQL 入门实战
  3. MergeTree、分区与索引
  4. 物化视图与聚合状态
  5. 查询优化与常见问题

沿用第二篇的 ClickHouse 25.8 环境。本篇创建独立的 payments_mv_demo、order_daily_metrics 和视图，不继续往八条支付记录中追加数据。所有建表和插入语句按顺序执行一次。

一、普通视图与增量物化视图

普通视图主要保存查询定义，读取视图时执行对应查询。增量物化视图则在源表写入新数据块时执行转换，将结果写入目标表。

1
2
3
4
5
6
7
普通视图：
查询视图 → 执行查询定义 → 读取明细、计算结果

增量物化视图：
新数据写入源表 → 执行视图中的查询 → 写入目标表
                                          ↓
                                报表读取目标表

它不是定时把整个源表重新扫描一遍。增量视图通常处理这次新插入的数据块。 机制说明见官方增量物化视图文档。

本文使用显式的 TO target_table，把“如何加工”和“结果存在哪里”分开表达。

二、为什么要保存聚合状态？

2.1 金额可以加，人数不一定可以加

考虑两批支付记录：

1
2
第一批：用户 201 支付 100 元，用户 202 支付 200 元
第二批：用户 201 又支付 50 元

分别统计之后：

指标 第一批 第二批 跨批次的正确结果
支付笔数 2 1 3
支付金额 300 50 350
去重支付人数 2 1 2

人数不能直接算成 2 + 1 = 3，因为同一个人出现在两批数据中。

如果保存的是能够合并的去重状态，就可以在合并时处理跨批次重复用户。ClickHouse 用 AggregateFunction 类型表达这类状态，见官方类型说明。

2.2 State 与 Merge 怎么配合？

写入时生成状态 查询时合并状态 用途
countState() countMerge(...) 合并计数
sumState(amount) sumMerge(...) 合并金额
uniqExactState(user_id) uniqExactMerge(...) 精确合并去重用户

-State 返回的是内部聚合状态，-Merge 合并相应状态并返回最终值。这些后缀是聚合函数组合器，见官方组合器文档。

状态也有成本。uniqExact 的状态需要保留精确去重信息，用户越多，内存和存储开销越大。大规模场景可以评估近似算法，但要先确定业务是否允许误差。

2.3 平均值为什么也要小心？

上述两批的平均支付金额是 150 元和 50 元。直接平均会得到 100 元，而正确结果是：

1
(100 + 200 + 50) / 3 = 116.666...

因为两批的记录数不同。可以保存总额和笔数再相除，也可以使用 avgState / avgMerge 保存和合并平均值状态。

一个已经算出来的数值，未必保留了再次合并需要的信息。 这是理解聚合状态最关键的一步。

三、创建源表、目标表与视图

3.1 源表保存支付事实

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE clickhouse_learning.payments_mv_demo
(
    order_id UInt64,
    user_id UInt64,
    city String,
    amount Decimal(12, 2),
    paid_at DateTime('Asia/Shanghai')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(paid_at)
ORDER BY (city, paid_at, order_id);

这一张表里一行仍然表示一次独立的订单成功支付，不是订单状态的多个版本。

3.2 目标表保存每天、每个城市的聚合状态

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE clickhouse_learning.order_daily_metrics
(
    day Date,
    city String,
    payment_count_state AggregateFunction(count),
    amount_state AggregateFunction(sum, Decimal(12, 2)),
    user_state AggregateFunction(uniqExact, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, city);

这张表的粒度是“每天、每个城市”。因此，排序键包含 day 和 city，与后面的分组维度一致，防止不同日期或城市的状态被当作一组。

AggregateFunction(sum, Decimal(12, 2)) 中的类型是聚合函数的输入类型，要与 sumState(amount) 对应，不能随意把目标状态声明成另一种类型。

3.3 创建增量物化视图

1
2
3
4
5
6
7
8
9
10
11
CREATE MATERIALIZED VIEW clickhouse_learning.order_daily_metrics_mv
TO clickhouse_learning.order_daily_metrics
AS
SELECT
    toDate(paid_at) AS day,
    city,
    countState() AS payment_count_state,
    sumState(amount) AS amount_state,
    uniqExactState(user_id) AS user_state
FROM clickhouse_learning.payments_mv_demo
GROUP BY day, city;

显式别名与目标表列名对应。AggregatingMergeTree 如何保存和合并状态，可以参考官方引擎示例。

创建顺序是：空源表 → 空目标表 → 视图 → 插入数据。这样本实验不涉及历史回填。

四、分两次插入，验证跨批次合并

4.1 插入第一批

1
2
3
INSERT INTO clickhouse_learning.payments_mv_demo VALUES
    (2001, 201, '上海', 100.00, '2026-10-01 09:00:00'),
    (2002, 202, '上海', 200.00, '2026-10-01 10:00:00');

查询目标表：

1
2
3
4
5
6
7
8
9
SELECT
    day,
    city,
    countMerge(payment_count_state) AS payment_count,
    sumMerge(amount_state) AS total_amount,
    uniqExactMerge(user_state) AS paying_users
FROM clickhouse_learning.order_daily_metrics
GROUP BY day, city
ORDER BY day, city;

预期是 2026-10-01、上海、2 笔、300.00 元、2 人。

4.2 第二批出现同一个用户

1
2
INSERT INTO clickhouse_learning.payments_mv_demo VALUES
    (2003, 201, '上海', 50.00, '2026-10-01 11:00:00');

重新执行上面的查询，预期变成：

日期 城市 支付笔数 支付金额 去重支付人数
2026-10-01 上海 3 350.00 2

用户 201 又买了一次，但仍然只有两个不同用户。结果按固定输入计算，用它检查自己的实验。

4.3 为什么查询仍要 GROUP BY 和 Merge？

后台合并不保证已经将同一个维度的全部状态整理成一行。因此，查询时仍需要按维度分组，并使用匹配的 -Merge 函数。

直接 SELECT * 可能看到多个中间状态，输出也不一定是人能直接读懂的数字。不要把目标表当前的物理行数当作支付笔数。

聚合结果的正确性应该由查询语义保证，不应该依赖“后台应该合并完了”。

五、已经有历史数据怎么办？

如果先插入一年的明细，后来才创建视图，普通 CREATE MATERIALIZED VIEW ... TO ... 不会自动把已有数据全部补入目标表。

为了观察这一点，可以做一个完全独立的小实验：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE clickhouse_learning.history_payments_demo
AS clickhouse_learning.payments_mv_demo;

INSERT INTO clickhouse_learning.history_payments_demo VALUES
    (3001, 301, '北京', 80.00, '2026-10-01 09:00:00');

CREATE TABLE clickhouse_learning.history_daily_demo
AS clickhouse_learning.order_daily_metrics;

CREATE MATERIALIZED VIEW clickhouse_learning.history_daily_mv
TO clickhouse_learning.history_daily_demo
AS
SELECT
    toDate(paid_at) AS day,
    city,
    countState() AS payment_count_state,
    sumState(amount) AS amount_state,
    uniqExactState(user_id) AS user_state
FROM clickhouse_learning.history_payments_demo
GROUP BY day, city;

SELECT count() AS target_rows
FROM clickhouse_learning.history_daily_demo;

创建表时的 AS other_table 复制结构，不复制记录。目标表此时应为空，因为 3001 在视图创建前就已经写入。

这个实验没有其他写入方，下面的历史回填只执行一次：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
INSERT INTO clickhouse_learning.history_daily_demo
SELECT
    toDate(paid_at) AS day,
    city,
    countState() AS payment_count_state,
    sumState(amount) AS amount_state,
    uniqExactState(user_id) AS user_state
FROM clickhouse_learning.history_payments_demo
GROUP BY day, city;

SELECT
    day,
    city,
    countMerge(payment_count_state) AS payment_count,
    sumMerge(amount_state) AS total_amount,
    uniqExactMerge(user_state) AS paying_users
FROM clickhouse_learning.history_daily_demo
GROUP BY day, city
ORDER BY day, city;

预期是北京、1 笔、80.00 元、1 人。第二次回填会再次累加笔数和金额，因此它不是可随意重复执行的修复命令。

真实环境还需要处理回填与实时写入的交界：可以暂停写入后完成回填再恢复，也可以设计明确的摄入边界，让历史路径与实时路径互不重叠。仅按业务事件时间切分时，还要考虑迟到事件，不能默认旧日期的数据永远不会再来。官方回填指南提供了更完整的方法。

六、三种“看起来会自动发生”的误解

6.1 源表更新，汇总表会自动更正吗？

本篇的增量视图不是持续比较整张源表的变化。源表的 mutation、删除、分区操作或后台合并，不会自动变成对目标表的对应撤销和重新计算。

因此，退款、错误支付和历史修正需要明确的业务方案，例如单独记录调整事实，或重算受影响的汇总范围。官方物化视图指南说明了这种触发边界。

6.2 源表用了 ReplacingMergeTree，就能避免重复金额吗？

不能直接这么推导。

1
2
3
4
5
6
7
重复记录先写入源表
      ↓
增量视图已经把它计入金额
      ↓
源表后来后台去重
      ↓
目标表不会自动减回这笔金额

写入去重、实体版本替换和指标聚合是三种不同机制。重试是否被识别为同一个写入块，也取决于引擎与去重设置，不能将它理解为任意业务事件 ID 的自动幂等。

6.3 视图中 JOIN 了用户表，用户改城市会自动重算吗？

常规增量视图由源表的新插入触发。右侧维度表变化不会单独触发历史明细的重新聚合，JOIN 使用的是触发时读取到的维度数据。

要先明确统计口径：“支付发生时的城市”还是“用户现在的城市”。前者可以随事实一起保存，后者可能需要查询时关联或其他刷新方案，见官方 JOIN 注意事项。

七、什么时候值得预聚合？

场景 可以考虑的方式
高频查询固定的日级指标 增量物化视图与汇总表
临时分析不断增加新维度 保留明细直接查询
需要定期重算包含更新的数据集 评估可刷新物化视图或批处理
指标定义尚未稳定 先明确口径，避免过早固化汇总

预聚合把一部分计算成本移到了写入阶段，也增加了状态存储和维护工作。维度组合很多、精确去重状态很大时，目标表未必像想象中那样小。

学习时可以再做一个练习：将第二批用户改成 203，预期金额仍然是 350 元，但支付人数应该变为 3。使用新的实验表复现，不要向当前三条记录上直接重复插入。

下一篇用执行计划、查询日志和一个较大的合成数据集，练习判断查询慢在哪里。