ClickHouse中的SummingMergeTree和AggregatingMergeTree

SummingMergeTree 与 AggregatingMergeTree：轻松实现增量聚合

1. 为什么需要聚合引擎——海量数据计算的问题

让我们回到在线赌场的场景。每天，玩家们会下数百万次注。仪表盘所有者需要查看：每个用户每天下了多少注，总金额是多少。

在传统数据库（PostgreSQL、MySQL）中，你会这样写：

SELECT user_id, date, COUNT(*), SUM(amount)
FROM bets
GROUP BY user_id, date

在一个有1亿行的表上，这样的查询会花费……嗯，你懂的——很长时间。非常长。因为数据库必须读取所有行，进行排序或哈希，然后计算聚合。

ClickHouse 当然更快，但也不是魔法。数据越多，GROUP BY 耗时越长。如果有很多报表且需要“即时”响应——性能就成了问题。

思路： 如果我们预先计算并存储聚合结果呢？这样查询“user_id=123 昨天下了多少注”就只是从一行中 SELECT，而不是全量聚合？

为此，ClickHouse 提供了两种特殊的表引擎：SummingMergeTree 和 AggregatingMergeTree。它们会在后台数据部分合并时为你完成繁重的工作。

现实类比： 想象你在商店里记录销售。每笔销售就是一张收据。如果老板要“今天卖了多少钱”的报表，你可以每次都翻遍所有收据。或者你可以准备一个笔记本，在一天结束时记下总数：“今天150笔销售，共5000卢布。”SummingMergeTree 就像自动记录这个笔记本。

2. SummingMergeTree——自动求和器

工作原理

SummingMergeTree 是一种引擎，在数据部分合并（后台）时，它会根据相同的排序键（ORDER BY）对数值列进行求和。

求和规则：

• 所有数值列（类型：UInt*、Int*、Float*、Decimal*）会自动求和。
• 其他列（字符串、日期、数组）取自第一个遇到的行——这一点很重要，可能不是你期望的结果。
• 如果某列不是数值类型，但你想要以某种方式聚合它——SummingMergeTree 不适用，你需要 AggregatingMergeTree。

为什么叫“Summing”：因为具有相同键的多行会被合并为一行，其中的数字是原始行中数字的总和。

CREATE TABLE——详细解析

-- 创建按用户统计的每日统计表
CREATE TABLE daily_stats
(
    date         Date,                -- 统计日期
    user_id      UInt64,              -- 玩家ID
    bets_count   UInt64,              -- 每日下注次数（将被求和）
    total_amount Decimal(18,2)       -- 下注总金额（将被求和）
)
ENGINE = SummingMergeTree()           -- 自动求和引擎
ORDER BY (date, user_id)              -- 分组键：按日期和用户

重点：

• ENGINE = SummingMergeTree()——你可以在括号中指定要求和的列：SummingMergeTree(bets_count, total_amount)。如果不指定，则所有数值列都会被求和（ORDER BY 中的列除外，它们的值决定唯一性）。

• ORDER BY (date, user_id)——这些列决定哪些行会被合并为一行。也就是说，所有具有相同 date 和相同 user_id 的行在合并时会合并为一行，其中 bets_count 和 total_amount 是总和。

如果 ORDER BY 太宽泛会怎样？ 如果你把 bets_count 也包含进去，那么每个不同的下注次数都会保留为单独一行。由于键不同，不会发生求和。陷阱 #1（稍后会讲到）。

如何插入数据

插入原始事件（每行代表一次下注）：

-- 为2025-06-01的用户123插入三次下注
INSERT INTO daily_stats VALUES 
    ('2025-06-01', 123, 1, 100.00),   -- 一次下注100卢布
    ('2025-06-01', 123, 1, 250.00),   -- 第二次下注250卢布
    ('2025-06-01', 456, 1, 50.00);    -- 另一个用户

-- 你可以插入非聚合数据——引擎会处理

后台合并后（可能需要几秒到几小时），date='2025-06-01' 且 user_id=123 的行会合并为一行：('2025-06-01', 123, 2, 350.00)。

无需 GROUP BY 的读取——魔法及其局限

思路是，合并完成后，你可以无需聚合直接读取数据：

-- 如果合并已完成，此查询返回每个用户每天一行
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';

但有一个问题。 在合并之间，数据可能分布在具有重复键的不同部分。因此，实践中，你仍然需要使用 SUM 来写：

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
WHERE date = '2025-06-01'
GROUP BY date, user_id;

为什么这样可行？ 因为即使数据尚未合并，SUM 也会正确累加。而如果已经合并，每组只有一行，SUM 直接返回该值。查询仍然读取数据，但现在数据量更少（聚合后的行而非原始行）。

类比： SummingMergeTree 就像一个助手，预先将相同的收据粘在一起。但你仍然会问“显示每天的总计”。如果收据已经粘好，总计与一行中的数字一致。如果没有，你仍然能得到正确的总计。关键是，你读取的不是数百万张收据，而是数千个汇总。

3. 问题：合并之间需要在 SELECT 中使用 SUM

这是一个经常被误解的关键点。

天真的方法（错误）：

-- 初学者认为合并后数据已经聚合，于是写：
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- 结果得到同一个用户的多个行（如果合并尚未发生）

正确的方法（安全）：

SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
GROUP BY date, user_id;

为什么？

1. 你总是得到正确的结果——无论合并前后。
2. 数据量仍然比原始下注表小。
3. ClickHouse 能很好地优化这类查询。

什么时候可以省略 SUM？ 只有当你绝对确定所需数据已经合并时。例如，在强制执行 OPTIMIZE TABLE daily_stats 之后（但这是一个昂贵的操作，不要为小事执行）。

4. AggregatingMergeTree——当简单求和不够时

SummingMergeTree 只能对数字求和。但如果你需要：

• 统计唯一用户（而非求和）？
• 找出最大值或最小值？
• 计算平均值？
• 使用近似算法如 uniq 来统计唯一值？

为此，有 AggregatingMergeTree。它存储的不是简单的值，而是聚合函数的状态——一种特殊的中间数据，允许你稍后得到最终结果。

类比： SummingMergeTree 只存储最终的和。而 AggregatingMergeTree 不仅存储和，还存储计数器（以便后续计算平均值），或唯一值的哈希表（以便后续告诉你有多少个）。这就像“我有总数”和“我有一个笔记本，里面记录了所有数据，但以压缩形式”之间的区别。

使用 AggregateFunction 创建表

-- 创建用于聚合仪表盘统计的表
CREATE TABLE dashboard_hourly
(
    event_hour     DateTime,                               -- 事件发生的小时
    sport_type     String,                                 -- 运动类型（足球、篮球……）
    total_bets     AggregateFunction(sum, UInt64),         -- 下注次数总和
    total_amount   AggregateFunction(sum, Decimal(18,2)), -- 金额总和
    unique_users   AggregateFunction(uniq, UInt64),        -- 唯一玩家（近似）
    avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- 平均下注金额
    max_bet        AggregateFunction(max, Decimal(18,2))   -- 最大下注金额
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);

解析不熟悉的部分：

• AggregateFunction(sum, UInt64)——一种列类型，存储聚合函数 sum 对 UInt64 类型数据的状态。它不是一个数字，而是 ClickHouse 的内部结构。
• 为什么不直接用 UInt64？因为对于某些函数（uniq、avg），你需要存储比最终结果更多的数据。avg 同时存储总和和计数。uniq 存储一个哈希表。
• 当合并两个具有相同 ORDER BY（相同小时和运动类型）的行时——聚合函数的状态会被合并。对于 sum，就是简单地将中间总计相加。对于 uniq，就是合并两个唯一值的哈希表。

通过 INSERT SELECT 使用 State 函数插入数据

你不能直接将普通值插入 AggregateFunction 列。需要使用特殊的 *State 函数，从原始值创建状态。

-- 从原始下注表插入聚合数据
INSERT INTO dashboard_hourly
SELECT
    toStartOfHour(event_time) AS event_hour,               -- 将时间截断到小时
    sport_type,
    sumState(bets_count) AS total_bets,                    -- sum 的状态
    sumState(amount) AS total_amount,                      -- 金额总和的状态
    uniqState(user_id) AS unique_users,                    -- 唯一值状态
    avgState(amount) AS avg_bet_amount,                    -- 平均值状态
    maxState(amount) AS max_bet                            -- 最大值状态
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;

这里发生了什么：

• toStartOfHour(event_time)——ClickHouse 函数，将时间截断到小时：2025-06-01 12:34:56 → 2025-06-01 12:00:00。
• sumState(amount)——代替 SUM(amount)，你写 sumState(amount)。结果是聚合函数的状态，类型为 AggregateFunction(sum, ...)。
• 插入查询中必须使用 GROUP BY！因为你正在将原始表中的数据聚合到组（小时+运动类型）中，然后将每个组作为一行插入到 dashboard_hourly。

通过 Merge 函数读取

要读取数据，使用 *Merge 函数：

SELECT
    event_hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,                    -- 从状态到数字
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,               -- 唯一用户数
    avgMerge(avg_bet_amount) AS avg_bet_amount,
    maxMerge(max_bet) AS max_bet
FROM dashboard_hourly
WHERE event_hour >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;   -- 仍然需要分组（如果数据尚未合并）

为什么又要 GROUP BY？ 与 SummingMergeTree 同理：在合并之间，可能存在多个具有相同 ORDER BY 的行。GROUP BY 配合 *Merge 在任何状态下都能给出正确结果。

5. 与物化视图结合的使用模式

使用 AggregatingMergeTree 最强大的方式是与物化视图结合。你将原始数据插入普通表，视图自动聚合数据并存储到聚合表中。

类比： 就像设置一条传送带：原始收据进入一个箱子，自动分拣机每分钟收集每日总计并放入另一个箱子。分析师只看第二个箱子——快速且无需即时 GROUP BY。

完整示例：运营商仪表盘的小时统计

步骤 1：原始表——事件（下注）将插入此处

CREATE TABLE raw_bets
(
    event_time DateTime,
    sport_type String,
    user_id UInt64,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY event_time;

步骤 2：聚合表——准备好的统计信息将存储在此处

CREATE TABLE bets_hourly_agg
(
    hour DateTime,
    sport_type String,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    avg_bet AggregateFunction(avg, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_type);

步骤 3：物化视图——两者之间的桥梁

CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
    toStartOfHour(event_time) AS hour,
    sport_type,
    sumState(1) AS total_bets,                    -- 每行代表一次下注
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;

现在会发生什么？

1. 你通过常规 INSERT 将行插入 raw_bets。
2. ClickHouse 自动（几乎实时）将它们通过物化视图处理。
3. 视图仅聚合插入批次中的数据，并将结果插入 bets_hourly_agg。
4. 在 bets_hourly_agg 中，可能暂时积累多个具有相同 (hour, sport_type) 的行——但它们会在后台合并期间合并。

仪表盘读取：

SELECT
    hour,
    sport_type,
    sumMerge(total_bets) AS total_bets,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,
    avgMerge(avg_bet) AS avg_bet
FROM bets_hourly_agg
WHERE hour >= today() - 7
GROUP BY hour, sport_type;

此查询将只读取聚合数据，其空间占用比原始下注数据小数千倍。

6. 真实示例：体育赛事仪表盘

想象你是一名博彩运营商。在仪表盘上，你需要显示：

• 每场比赛（足球、欧冠、皇马 vs 拜仁）
• 过去5分钟内下了多少注
• 所有下注的总金额
• 唯一玩家数量
• 平均下注金额

原始数据：每秒5000次下注。每次都存储所有数据并从头聚合是疯狂的。

解决方案：

-- 按比赛和5分钟间隔存储聚合数据
CREATE TABLE match_stats_5min
(
    match_id String,
    interval_5min DateTime,
    total_bets AggregateFunction(sum, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64),
    max_bet AggregateFunction(max, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (match_id, interval_5min);

-- 物化视图
CREATE MATERIALIZED VIEW match_stats_mv TO match_stats_5min AS
SELECT
    match_id,
    toStartOfFiveMinute(event_time) AS interval_5min,
    sumState(1) AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,
    maxState(amount) AS max_bet
FROM raw_bets
GROUP BY match_id, interval_5min;

现在仪表盘查询 match_stats_5min——响应时间从秒级降至毫秒级。

7. 何时不应使用 SummingMergeTree 和 AggregatingMergeTree

SummingMergeTree 适用场景：

• 你只需要对数值列求和。
• 你接受在合并之间使用 GROUP BY 配合 SUM。
• 分组键的基数不是特别高（例如，不是十亿个唯一用户——虽然也可以，只是数据量更大）。

SummingMergeTree 不适用场景：

• 你需要统计唯一用户（uniq、count(DISTINCT)）——只有 AggregatingMergeTree 可以。
• 你需要其他聚合：avg、min、max——同样只有 AggregatingMergeTree。
• 你期望数据始终处于已聚合状态——事实并非如此。
• 你的数据会被更新（而不仅仅是插入）——这些引擎不支持更新语义。

AggregatingMergeTree 适用场景：

• 你需要不同类型的聚合（求和、唯一值、平均值、最大值）。
• 你愿意编写带 *State 的 INSERT 和带 *Merge 的 SELECT。
• 你使用物化视图进行自动聚合。
• 原始数据量巨大，而聚合数据量小几个数量级。

AggregatingMergeTree 不适用场景：

• 你无法向团队解释什么是 AggregateFunction 以及如何使用它。学习曲线较高。
• 你需要精确的唯一值，而不是近似值（uniq 是概率结构，误差约2%）。对于精确值，使用 groupBitmap 或在其他系统中计数。
• 数据量很小（数百万行）——常规 GROUP BY 更简单。
• 你频繁更改聚合模式（添加新指标）——重建物化视图很麻烦。

8. 与常规 MergeTree + GROUP BY 的对比

特性	MergeTree + GROUP BY	SummingMergeTree	AggregatingMergeTree
插入速度	最快	快	中等（由于状态）
读取速度（大范围）	慢（读取所有数据）	快（读取聚合数据）	快
读取速度（点查询）	中等	快	快
存储空间	最大	最小（聚合数据）	稍多（状态）
代码复杂度	低	低（只需建表）	高（*State, *Merge）
聚合灵活性	任意	仅求和	任意（通过 AggregateFunction）

9. 常见陷阱

陷阱 #1：ORDER BY 包含的字段不足

-- 错误：只有 date，没有 user_id
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;

-- 合并时，一天内的所有行会合并为一行
-- 你失去了用户级别的细节

正确做法： 在 ORDER BY 中包含所有你想要聚合的字段。

陷阱 #2：在 SELECT 中忘记 GROUP BY

-- 错误：没有 GROUP BY，即使数据尚未合并
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';

-- 如果有两行具有相同 date 但不同 user_id——你会得到错误
-- ClickHouse 不知道要显示哪个 user_id

正确做法： 始终按与 ORDER BY 相同的字段进行分组。

陷阱 #3：非随机的 uniq

ClickHouse 中的 uniq 是一个概率函数。误差约2-3%。如果你需要精确的唯一值，使用 uniqExact 或 groupBitmap。

陷阱 #4：更新旧数据

SummingMergeTree 和 AggregatingMergeTree 不喜欢更新。如果你需要更正昨天的下注，更简单的方法是插入一条相反符号的新行（通过 CollapsingMergeTree）。

10. 下一步

现在你已经掌握了聚合引擎，接下来的主题：

• 如何为你的任务选择合适的引擎——所有 *MergeTree 引擎的对比。
• 物化视图详解——如何调试，如何更新模式。
• 调优后台合并——使聚合更快折叠。

总结： SummingMergeTree 和 AggregatingMergeTree 是为那些不希望仪表盘在 TB 级数据上卡顿的人准备的。它们需要前期多一些理解，但在实际工作负载下会带来数倍的回报。主要规则：读取时始终使用 GROUP BY 和聚合函数——这样无论合并前后你都是安全的。

---

← 上一篇: ReplacingMergeTree：如何在 ClickHouse 中轻松去重
→ 下一篇: CollapsingMergeTree：如何在ClickHouse中无需UPDATE更新聚合数据

— Editorial Team