用物化视图做 ClickHouse 交易报表 ::

背景#

交易明细一天两三亿行，报表要按不同时区出日报、周报、月报。每次都从明细现算，读的是整天甚至整月的明细；明细表换成 ReplacingMergeTree 之后还要带 FINAL（查询路径那篇）。常见的做法是在写入时就把明细增量聚合进一张小得多的报表表，查询只读报表，官方最佳实践的说法是写入时增量计算之后，查询面对的数据小得多（Use materialized views）。

Aiven 上现有集群的聚合表按写入时间 create_time 的窗口增量累加，不按业务键去重。这篇写的是改过的报表链路：明细表 → 物化视图 → 报表表，桶按业务时间切。它在 clickhouse-lab 的实验 24、25 里跑过（25.3.14.14，三副本），还没部署到现有集群。报表会因为哪些重复多算、怎么对账和重算，放在报表的对账和重算那篇。

物化视图是插入时的触发器#

ClickHouse 的增量物化视图，文档原话是「just a trigger that runs a query on blocks of data as they’re inserted into a table」（Incremental materialized view）。源表每插入一个块，就拿这个块跑一遍视图的 SELECT，结果写进目标表。

它只看得到这一次新插入的块，看不到源表里已有的数据，也看不到之后发生的合并、mutation 和删除。好处是代价和写入量成正比，不用回头扫明细；代价是明细后来被折叠、被修改，报表不会跟着变。这一点决定了报表必须能对账、能重算。

三张表怎么建#

层	引擎	键	为什么这样建
明细	`ReplicatedReplacingMergeTree(create_time)`	和现表一致：按结算日分区，`ORDER BY (settle_ms, id, rev)`	现有集群上见过的三种重复都是除 `create_time` 外整行相同，排序键相同，合并或 `FINAL` 时折成一行
报表	`ReplicatedAggregatingMergeTree`	UTC 的 30 分钟桶加维度，`PARTITION BY toYYYYMMDD(bucket)`	按天分区，和明细的日分区一一对应，修一天只换报表的一个分区
物化视图	`CREATE MATERIALIZED VIEW … TO 报表表`	—	显式写目标表：重算时要往报表表换分区；报表表本身可以单独 `ALTER`，不用连带重建视图

实验 25 里报表表和物化视图是这样建的，events 是明细表，表名换成了这个系列里统一的名字，引擎参数按现有集群的 Replicated 库省略：

CREATE TABLE rpt
(
    bucket   DateTime('UTC'),
    tenant_id UInt32,
    rows     SimpleAggregateFunction(sum, UInt64),
    amount   SimpleAggregateFunction(sum, Decimal(38, 8)),
    users    AggregateFunction(uniqExact, UInt64)
)
ENGINE = ReplicatedAggregatingMergeTree
PARTITION BY toYYYYMMDD(bucket)
ORDER BY (bucket, tenant_id);

CREATE MATERIALIZED VIEW rpt_mv TO rpt AS
SELECT toStartOfInterval(toDateTime(intDiv(settle_ms, 1000), 'UTC'), INTERVAL 30 MINUTE) AS bucket,
       tenant_id,
       count()                AS rows,
       sum(amount)            AS amount,
       uniqExactState(user_id) AS users
FROM events
GROUP BY bucket, tenant_id;

实验 25 的每条 INSERT 都带着 deduplicate_blocks_in_dependent_materialized_views = 1。25.3 上这个设置默认是 0，这时源表的块级去重拦下了一次重投，物化视图照样会再算一次（实验 24）。它要满足的几个条件，写在报表的对账和重算。

AggregatingMergeTree 在合并时把排序键相同的行合成一行，存的是聚合函数的中间状态（AggregatingMergeTree）。能直接相加的指标用 SimpleAggregateFunction(sum, …)，读的时候再 sum 一次；去重用户数这种不能相加的，写进去用 uniqExactState，读出来用 uniqExactMerge。合并什么时候发生没有保证，所以读报表时一律带 GROUP BY 再聚合一遍，不能假设一个桶只有一行。

去重计数用 uniqExact，不用近似的 uniq：uniq 在 25.3 上实测误差两个方向都有，对账口径里没法修正回去（数字对不上的排查顺序）。

桶按结算时间切，按 UTC 切#

桶的时间轴用业务时间 settle_ms，不用写入时间 create_time。按 create_time 切有两个问题。第一，同一笔交易的几份重复，create_time 可能不一样（producer 重试那批约三分之二是同一秒写进来的，其余的晚几秒），明细折叠时留下哪一份，它就算进哪个窗口；ReplacingMergeTree(create_time) 留的是版本最大的那份，版本相同时留最后写入的，都是后到的那份，两份 create_time 不同时这一行会算进更晚的窗口（去重方案那篇的「留下哪一份」）。第二，人工补发的行 create_time 晚好几天，重算一个窗口要扫好几天的明细分区。按 settle_ms 切，重复的几份落在同一个桶里，明细的一天正好对应报表的一个分区。口径从 create_time 换成 settle_ms 之前，要先和报表的使用方确认。

桶按 UTC 切，时区留到查询时再给。前提是明细和报表按同一个时区切天：明细的分区键 toYYYYMMDD(toDateTime(settle_ms / 1000)) 用的是服务端时区，lab 的服务端是 UTC，报表的桶也按 UTC 算，修一天才正好只碰报表的一个分区。每套部署的服务端时区都要先查 SELECT timezone() 确认。

报表按天分区，假设保留三年，大约一千一百个分区，略超过分区键文档建议的「about a thousand」（Custom partitioning key）。改成按月分区，分区数降一个数量级，但修一天就要整月重算、整月换进去。这是个取舍，一千一百个分区的实际影响 lab 没测过。

在查询里按时区上卷#

日报、周报、月报都从 30 分钟桶现算，时区写在查询里：

SELECT toDate(bucket, 'Asia/Shanghai') AS d,
       sum(amount),
       uniqExactMerge(users)
FROM rpt
WHERE bucket >= {from} AND bucket < {to}
GROUP BY d
ORDER BY d;

实验 25 造了三天、每天 30 万行的明细，按几个时区上卷成日报，再和「直接按这个时区从明细 FINAL 算的日报」逐天比：

时区	UTC 偏移	结果
`Asia/Shanghai`	+8:00	逐天一致，去重用户数也一致
`Asia/Kolkata`	+5:30	逐天一致
`America/New_York`	−5:00 / −4:00	逐天一致；夏令时开始那天只有 23 小时、46 个桶，也对得上
`Asia/Kathmandu`	+5:45	有对不上的天

整点和半点偏移的时区，日界都落在 30 分钟桶的边界上。加德满都的日界是 UTC 18:15，落在 18:00 到 18:30 那个桶中间，30 分钟的桶切不开。要支持 :45 偏移的时区，桶得是 15 分钟，报表表的行数也跟着翻倍。

uniqExact 这类不能相加的指标同样能上卷：桶里存的是状态，按天 uniqExactMerge 一次，得到的是这一天精确的去重数；要是把各桶的去重数直接相加，跨桶出现的用户会被重复计入。

读报表和读明细差多少#

同一张上海时区的日报（只算金额合计，三天全读），在 lab 上分别从报表和明细 FINAL 查：

查的表	`read_rows`	耗时	内存
报表	3275	2 ms	4.04 MiB
明细 `FINAL`	1175832	18 ms	24.62 MiB

报表一天的行数等于桶数乘当天出现的维度组合数（lab 上一天 48 个桶、20 个租户），不跟着明细的行数涨。lab 上三天的组合是 2880 个，读到 3275 行，多出来的应该是还没合并到一起的行；明细 FINAL 读的行数比明细本身（约 90 万行）还多，原因应该是互相重叠的区间要归并，边界上的 granule 读了不止一次。这两句都是推断。耗时是这台机器上的数，不外推。

这张报表表省掉的是每次查询的读代价，换来的是写入时多做一次聚合，以及报表和明细之间的一致性要靠对账来保证，哪些重复会让报表多算、怎么发现、怎么修，写在报表的对账和重算。

小结#

物化视图是插入时的触发器，只看每次新插入的块，看不到之后的合并和修改。
报表表用 AggregatingMergeTree，可加的指标用 SimpleAggregateFunction(sum, …)，去重计数存 uniqExact 的状态，读的时候一律带 GROUP BY 再聚合。
桶按业务时间 settle_ms 切、按 UTC 切，报表按天分区，和明细的日分区一一对应，修一天只换报表的一个分区。明细和报表必须按同一个时区切天。
时区放到查询里上卷。lab 上整点、半点时区和夏令时那天都对得上，:45 偏移的时区要 15 分钟桶。
同一张日报，查报表读三千多行，查明细 FINAL 读一百多万行。

参考资料#

Incremental materialized view：物化视图是插入时的触发器，TO 子句
Use materialized views：写入时增量计算
AggregatingMergeTree：-State / -Merge、SimpleAggregateFunction
Custom partitioning key：分区数量的建议
clickhouse-lab 实验 25：建表、上卷和读代价的脚本，输出在 results/25-report-drift-and-rebuild.log；实验 24 是物化视图跟着源表去重的边界

用物化视图做 ClickHouse 交易报表

目录

背景#