有一天你工作工作,在當社畜的時候,發現你現在需要大量的「數值加總」、「分組彙總統計」,例如每日活躍使用者數量、每小時流量統計、即時計數器 (Counter) 等,ClickHouse 提供了一個極致高效的資料彙總利器 —— SummingMergeTree。
SummingMergeTree 是 ClickHouse MergeTree 系列的引擎變種,具備在資料合併 (Merge) 階段,自動將具有相同 Primary Key
的數值欄位進行加總 (SUM),幫助你快速構建高效能的預彙總表 (Pre-Aggregated Table)。
CREATE TABLE daily_metrics
(
date Date,
page String,
views UInt32,
clicks UInt32
) ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (date, page);
INSERT INTO daily_metrics VALUES ('2025-08-01', 'Home', 100, 10);
INSERT INTO daily_metrics VALUES ('2025-08-01', 'Home', 200, 25);
INSERT INTO daily_metrics VALUES ('2025-08-01', 'Contact', 50, 5);
查詢時仍會看到所有資料:
SELECT * FROM daily_metrics WHERE date = '2025-08-01';
date | page | views | clicks |
---|---|---|---|
2025-08-01 | Home | 100 | 10 |
2025-08-01 | Home | 200 | 25 |
2025-08-01 | Contact | 50 | 5 |
進行背景 Merge(或強制 Optimize)後:
OPTIMIZE TABLE daily_metrics FINAL;
再查詢結果:
date | page | views | clicks |
---|---|---|---|
2025-08-01 | Home | 300 | 35 |
2025-08-01 | Contact | 50 | 5 |
場景 | 說明 |
---|---|
即時計數器 (Counter) | 用於 API Call 次數、商品瀏覽次數等累加統計場景。 |
批次數據預彙總 (Batch Aggregation) | 將大數據表做成 Pre-Aggregated Table,避免即時計算壓力。 |
流量/活動數據彙總 (Metrics Aggregation) | 日誌、網站流量統計、用戶行為統計(例如 PV/UV 指標)。 |
與 Materialized View 搭配 | 將 Raw Data 資料流寫入 Materialized View,實時聚合彙總結果至 SummingMergeTree 表。 |
SummingMergeTree 的去重與彙總邏輯是以 Primary Key 欄位為基準,並對數值欄位進行加總。
若 Primary Key
欄位設計錯誤(例如太細緻或包含非必要欄位),將導致彙總效果失效,無法正確合併統計數據。
CREATE TABLE raw_events
(
event_time DateTime,
page String,
views UInt32,
clicks UInt32
) ENGINE = MergeTree
ORDER BY (event_time, page);
CREATE MATERIALIZED VIEW mv_daily_metrics
TO daily_metrics
AS SELECT
toDate(event_time) AS date,
page,
sum(views) AS views,
sum(clicks) AS clicks
FROM raw_events
GROUP BY date, page;
這樣每次寫入 raw_events
時,ClickHouse 會即時計算彙總結果並寫入 daily_metrics (SummingMergeTree)
。
特性 | SummingMergeTree | AggregatingMergeTree |
---|---|---|
支援的聚合函數 | 只支援 SUM(數值欄位) | 支援所有 AggregateFunction (SUM, AVG, COUNT, MIN, MAX) |
聚合邏輯 | 背景 Merge 時加總 | 需配合 AggregateFunction 資料型別與聚合函數進行計算 |
設計複雜度 | 簡單 (適用於計數器、單純加總) | 較複雜,適用於指標統計、分位數、去重統計等 |
寫入性能 | 較高(無需特殊型別) | 較低(需轉換為 AggregateFunction 型別資料寫入) |
ORDER BY
需謹慎設計,只包含用來 GROUP BY
的欄位。SummingMergeTree
進行高效查詢。SummingMergeTree 提供了一種簡單卻強大的數據彙總方式,非常適合用於統計計數、流量指標分析與預彙總表場景。透過合理設計 Primary Key 與 Materialized View 的搭配,能讓你的查詢效能成倍提升。